There are several ways to list dependency here is one simple way to do this.
;WITH a AS
(
SELECT
0 AS lvl, t.OBJECT_ID AS tblID
FROM [sys].tables t
WHERE t.is_ms_shipped = 0
AND t.OBJECT_ID NOT IN (SELECT f.referenced_object_id
FROM [sys].foreign_keys f)
UNION ALL
SELECT
a.lvl + 1 AS lvl, f.referenced_object_id AS tblId
FROM a
INNER JOIN [sys].foreign_keys f ON a.tblId = f.parent_object_id
AND a.tblID != f.referenced_object_id
)
SELECT
OBJECT_SCHEMA_NAME(tblID) [schema_name], OBJECT_NAME(tblId) [table_name], a.lvl
FROM a
GROUP BY tblId, a.lvl
ORDER BY MAX(lvl), 1;
sp_MSdependencies
There are several undocumented systems stored procedures and extended stored procedures in SQL Server 2005 which can be of enormous use.
EXEC sp_MSdependencies N'Sales.Customer' --Parameters --@objname nvarchar --@objtype int --@flags int --@objlist nvarchar --@intrans int
name: name or null (all objects of type)
type: type number (see below) or null
if both are null, get all objects in the database
a flag is a bitmask of the following values:
0x10000 = return multiple parent/child rows per object
0x20000 = descending return order
0x40000 = return children instead of parents
0x80000 = Include input object in output result set
0x100000 = return only firstlevel (immediate) parents/children
0x200000 = return only DRI dependencies
power(2, object type number(s)) to return in results set:
0 (1 – 0x0001) – UDF
1 (2 – 0x0002) – system tables or MS-internal objects
2 (4 – 0x0004) – view
3 (8 – 0x0008) – user table
4 (16 – 0x0010) – procedure
5 (32 – 0x0020) – log
6 (64 – 0x0040) – default
7 (128 – 0x0080) – rule
8 (256 – 0x0100) – trigger
12 (1024 – 0x0400) – uddt
shortcuts:
29 (0x011c) – trig, view, user table, procedure
448 (0x00c1) – rule, default, datatype
4606 (0x11fd) – all but systables/objects
4607 (0x11ff) – all
Other References:
