March 18, 2005 at 9:30 am
Hello!
Does anybody know more or less automatic way of getting a list of stored procedures/functions/triggers that modify a given table? My ultimate goal is to have an Excel file that lists all tables in my database and for each table there would be a list of objects that select, insert, delete or update the table!
March 18, 2005 at 10:22 am
Gregory,
Try sp_depends
"Displays information about database object dependencies (for example, the views and procedures that depend on a table or view, and the tables and views that are depended on by the view or procedure). References to objects outside the current database are not reported."
Not sure about triggers
Yelena
Regards,Yelena Varsha
March 18, 2005 at 12:37 pm
Yelena,
I am aware of sp_depends stored procedure but first, unfortunately from my long time experience with SQL Server I used to not trust to this procedure since it didn't work accurately all the time (I didn't validate this sp recently, hopefully it works better now :-).
Secondly, what I really need is a cross references between DDL objects (tables) and corresponding DML ones (procedures, functions, triggers...). I would like to know all the places in the database where the specific table could be potentially modified. In this case kind of smart parser would be what I am currently looking for.
Thanks,
Gregory
March 18, 2005 at 12:54 pm
I think the only thing that approaches that, are the perl scripts written by Linchi Shea
Real World SQL Server Administration with Perl
Other than that, you will have to roll up your sleves and build your own
btw Is a very good book
* Noel
March 18, 2005 at 1:03 pm
Gregory,
No parser is perfect!
Here is what I would probably do:
First, read the following scripts from this site
http://www.sqlservercentral.com/scripts/contributions/1074.asp
| DependencyDetails |
http://www.sqlservercentral.com/scripts/contributions/692.asp
MetaData_TableDependenceOrder
And see if they can help you. If not, then:
1. Generate SQL Script for tables OR select all from sysobjects where type is a table but not a system table. Whatever your requirements are. Store the result in the Table1, one record per table name /owner
2. Generate SQL Script for all other objects. Do not forget that triggers belong to the tables, so script triggers separately. Store results in Table2, one record per object / owner
3. Do a search with a cursor or something.
Regards,Yelena Varsha
March 18, 2005 at 1:13 pm
Yelena,
Table Dependency is achievable from the current metadata tables. Triggers and Table dependency are easy also but OBJECT dependency when it is in regards to procedure and functions is difficult because you can have more than one record on syscomments for one object and the names can be divided therefore a memory or disk based reconstruction is imperative.
HTH
* Noel
March 18, 2005 at 4:15 pm
Noel,
I suppose sp_depends that Gregory does not like does use syscomments and I did not want to let Gregory re-create what SQL Server is already doing. I don't know what he will do about crosss-database references or about linked servers. I sometimes use separate databases for my views and tables that reference a third-party database in order not to touch a third-party database structure
Yelena
Regards,Yelena Varsha
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply