Simple T-SQL question

  • Thread starter Thread starter Kikoz
  • Start date Start date
K

Kikoz

Hi all.

For some reason, my company has urgent need to rename one core table in one
db. Db has lots (lots!) of sprocs, views, triggers and UDFs. I'm not
familiar in details with the structure of master db, so I'm not sure if I
can simply create some procedure that would look for specific type of
objects in particular db on the server, check if object's code contains any
references to that freaking table and replace the name of it in object's
sql. Is it possible at all? Samples, articles?

Thanks for any kind of help.
Kikoz.
 
Hi,

I believe you could get list of the dependencies for you table when you
right click on a table in a Enterprise Manager of SQL Server and select menu
item that calls something like Show Dependencies
 
Try sp_depends:
EXEC dbo.sp_depends [dbo.Users]

Also, the INFORMATION_SCHEMA views are pretty helpful for this kind of
thing. The following scripts would check for SP's, Constraints, and
Views that access the table Users:

select * from information_schema.routines
where specific_name not like 'sp_%'
and routine_definition like '%Users%'

select * from information_schema.table_constraints where table_name =
'Users'

select * from information_schema.view_table_usage where table_name =
'Users'

I don't think these are all inclusive, but may get you started.
- Jon
http://weblogs.asp.net/jgalloway
 
Back
Top