Wednesday, June 22, 2011

Table Dependencies

As you may know, we can't really rely upon sp_depends to list object dependencies, because of a problem with deferred name resolution.  When you script out objects in a database, if any objects referenced by an object are created after the object that references it, then sp_depends won't be completely accurate.


This is just a quick piece I put together to return table dependencies.  Note, the table is returned exactly as it is referenced in the procedure or view. So, if you've got unqualified object references, you'll see 'TableName', rather than 'SchemaName.TableName'.


SELECT 
COALESCE(referenced_server_name + ',','') + COALESCE(referenced_database_name + ',','')
+ COALESCE(referenced_schema_name + ',','') + referenced_entity_name
+ COALESCE(',' + COL_NAME(referenced_id, referenced_minor_id), '') AS [Table],
OBJECT_NAME(referencing_id) + COALESCE(',' + COL_NAME(referencing_id, referencing_minor_id),'')
AS [Dependency], 
o.type_desc AS ObjectType
FROM sys.sql_expression_dependencies AS sed
INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id
UNION ALL
SELECT OBJECT_NAME(referencing_id) AS [Dependency],
OBJECT_NAME(referencing_id) + ',' + COL_NAME(referencing_id, referencing_minor_id) AS [Table],
o.type_desc AS ObjectType
FROM sys.sql_expression_dependencies AS sed
INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id
WHERE referencing_minor_id <> 0
ORDER BY [Table];


I've also just read that sp_depends will be removed in a future version of SQL, so it probably wouldn't hurt to take a look at sys.sql_expression_dependencies: 
http://msdn.microsoft.com/en-us/library/ms189487.aspx