Scripted delete rows

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.

"rcamarda" <rcamarda@cablespeed.com> wrote in message
news:1114613428.538202.213970@f14g2000cwb.googlegroups.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(objec
t_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

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