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

ALL STORED PROCS RETURNING NOTHING BUT NULLS IN OUTPUT PARAMETERS

I think my hosting company changed something on SQL Server, but I don't
know what. Suddenly, all OUTPUT parameters from ANY SPROC return NULL.
The funny thing is, the values are in the variables within the SPROCS
(see the PRINT results on the SPROC example below) The behavior is the
same in Query Analyzer as in ADO.Net.
Any help would be GREATLY appreciated.
Thanks,
Brad
Here is some detail:
Stored Proc Definition
=====================================
ALTER PROC DBO.USP_GET_POOL_INFO
@.POOLID INT
,@.PRIVATE CHAR(1) OUTPUT
,@.PLAYERLIMIT NVARCHAR(33) OUTPUT
,@.PLAYERCOUNT INT OUTPUT
AS
SELECT
@.PRIVATE = private
,@.PLAYERLIMIT = CASE WHEN p.player_limit = 0 OR p.player_limit IS
NULL THEN 'No Limit' ELSE CAST(p.player_limit AS NVARCHAR(33)) END
,@.PLAYERCOUNT = ISNULL(pc.player_count,'')
from
tPools p
LEFT OUTER JOIN
(
SELECT
POOLID,
COUNT(*) player_count
FROM
tProfilePool
GROUP BY
POOLID
) pc
ON
p.pool_id = pc.poolid
where
pool_id = @.POOLID
PRINT @.PRIVATE
PRINT @.PLAYERLIMIT
PRINT @.PLAYERCOUNT
========================================
====
Stored Proc Execution:
========================================
====
DECLARE @.PLAYERLIMIT NVARCHAR(33)
DECLARE @.PLAYERCOUNT INT
DECLARE @.PRIVATE CHAR(1)
EXEC DBO.USP_GET_POOL_INFO 2, @.PRIVATE, @.PLAYERLIMIT, @.PLAYERCOUNT
SELeCT @.PRIVATE, @.PLAYERLIMIT, @.PLAYERCOUNT
Result:
NULL NULL NULL
Messages Result (from the PRINT command):
Y
No Limit
1
========================================
Raw TSQL Approach:
========================================
====
DECLARE @.PRIVATE CHAR(1)
DECLARE @.PLAYERLIMIT VARCHAR(33)
DECLARE @.PLAYERCOUNT INT
SELECT
@.PRIVATE = private
,@.PLAYERLIMIT = CASE WHEN p.player_limit = 0 OR p.player_limit IS
NULL THEN 'No Limit' ELSE CAST(p.player_limit AS NVARCHAR(33)) END
,@.PLAYERCOUNT = ISNULL(pc.player_count,'')
from
tPools p
LEFT OUTER JOIN
(
SELECT
POOLID,
COUNT(*) player_count
FROM
tProfilePool
GROUP BY
POOLID
) pc
ON
p.pool_id = pc.poolid
where
pool_id = 2
SELECT
@.PRIVATE, @.PLAYERLIMIT, @.PLAYERCOUNT
========================================
Execution Result:
Y No Limit 1You need to specify OUTPUT in the EXEC
EXEC DBO.USP_GET_POOL_INFO 2, @.PRIVATE OUTPUT , @.PLAYERLIMIT OUTPUT ,
@.PLAYERCOUNT OUTPUT