All tables and columns referenced by sql objects

I'm trying to find all the tables and columns referenced by the sql objects (stored procs, UDFs, views, etc.) in a particular MSSQL2005 database.

I can join the sys.objects, sys.sql_dependencies and sys.columns tables using object_id, referenced_major_id, referenced_minor_id, and column_id but I find that the more complex procs contain references to tables (sub selects, for example) that do not seem to appear in sys.sql_dependencies.

I can always do string searches on the definition column in sys.sql_modules, but that won't reliably get me column+table combos.

Anyone have any ideas?

Thanks!

I actually wrote a blog on this exact topic recently. Check it out:

http://blogs.claritycon.com/blogs/the_englishman/default.aspx

HTH

|||

Well... close but... Your solution involves string searches on the proc definition which may or may not (probably not) distinguish between tab1.tab1Key (PK) and tab2.tab1Key (FK).

Incidentally, check out http://msdn2.microsoft.com/en-us/library/ms187997.aspx for the SQL2005 system views that correspond to the SQL 2000 tables.

Thanks!

|||

No, for that you would have to use the following in an exists:

SELECT * FROM sysforeignkeys f join sysobjects obj on obj.id = f.fkeyid

Incidentally, I was told by a SQL server database engine worker that the INFORMATION_SCHEMA schema views are better to use. Check out the following:

SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE

SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

That should do it, in combination with my stored porcedure. Let me know if you need more information.

|||

This is unfortunately not so easy to do. You should rely on your source code control to maintain the dependencies since it can be easily broken on the database side by recreating objects in wrong order or dropping some objects etc. There are certain dependencies like foreign key constraints, schema bound objects which are easy to obtain and maintained accurately by the engine. But there are other by name dependencies which are harder to track. For an example of how complex this scenario can be you can take a look at my blog post below which covers the direct dependencies on a column:

http://blogs.msdn.com/sqltips/archive/2005/07/05/435882.aspx