Scripted delete rows

[rcamarda]

Hi,
I need to delete rows from my user tables dependant upon there non
existence from another table:

delete student
where student_id not in (select student_id from tblStudent)

The reasons is convoluted, simplest explanation is that our operational
system allows the change of business keys. This wreaks havoc in the
data warehouse.
So, I'm look for help on how I can delete rows from tables that have a
column STUDENT_ID. I'd like the script to search for the tables, then
perform the delete.
I don't know where information about user tables are stored, nor how to
loop through the results to do the delete.

Any Ideas are appreciated.

[Simon Hayes]

"rcamarda" <rc******@cablespeed.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Hi,
I need to delete rows from my user tables dependant upon there non
existence from another table:

delete student
where student_id not in (select student_id from tblStudent)

The reasons is convoluted, simplest explanation is that our operational
system allows the change of business keys. This wreaks havoc in the
data warehouse.
So, I'm look for help on how I can delete rows from tables that have a
column STUDENT_ID. I'd like the script to search for the tables, then
perform the delete.
I don't know where information about user tables are stored, nor how to
loop through the results to do the delete.

Any Ideas are appreciated.

You can use a query like this to generate a script, then review it before
executing it:

select 'delete from ' +
TABLE_SCHEMA + '.' + TABLE_NAME +
' where not exists (select student_id from dbo.tblStudent ts where ' +
TABLE_SCHEMA + '.' + TABLE_NAME +
'.student_id = ts.student_id)'
from
INFORMATION_SCHEMA.COLUMNS
where
COLUMN_NAME = 'student_id' and
objectproperty(object_id(TABLE_SCHEMA + '.' + TABLE_NAME), 'IsTable') = 1

See the INFORMATION_SCHEMA views in Books Online, as well as syscolumns,
sysobjects, and "Meta Data Functions".

Simon

[rcamarda]

Simon,
Works like a champ and I learned something new!
Thanks
Rob