When I was working on a project I got a requirement to know how certain columns/tables are populated, which sprocs are populating them. Then I googled for a while about it and got the link about information_schema.routines. information_schema.routines is a system view which returns all the sprocs defined in the database.
You can have a look at the below example to know the usage of it. The below query shows the list of sprocs whose definition contains the word "customers". In general terms I am using this query to know what all sprocs are operating on the table "customers"
use northwind
go
select routine_name,routine_definition from information_schema.routines where routine_definition like '%customers%'
This is very useful when there is a huge database with many number of tables and many sprocs.
It was useful to me as our project was very complex and huge.
Thursday, April 23, 2009
Subscribe to:
Post Comments (Atom)
0 comments:
Post a Comment