What are the table names which are
linked to the current table ? This question strikes every developer or a
database administrator at-least once in a day. And if a task is given
to find all the dependent tables for each of the table that resides in
the database then it becomes even more tedious and humongous.
Below is one such script which can do the above work in second, so enjoy the script and find the dependencies among the table.
DECLARE @mastertable VARCHAR(100)
DECLARE @TableCompleteName VARCHAR(100)
DECLARE @tablesname VARCHAR(1000)
CREATE TABLE #temptable
(
tablecompletename VARCHAR(100),
tablename VARCHAR(1000)
)
DECLARE tmp_cur CURSOR static
FOR SELECT s.name + ‘.’ + o.name,
o.name
FROM sys.objects o
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE type = ‘U’
ORDER BY s.name,
o.name
OPEN tmp_cur
–FETCH
FETCH FIRST FROM tmp_cur INTO @TableCompleteName, @mastertable
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @tablesname = COALESCE(@tablesname + ‘,’, ”) + s.name + ‘.’
+ OBJECT_NAME(FKEYID)
FROM SYSFOREIGNKEYS
INNER JOIN sys.objects o ON o.object_id = SYSFOREIGNKEYS.fkeyid
INNER JOIN sys.schemas s ON s.schema_id = o.schema_id
WHERE OBJECT_NAME(RKEYID) = @mastertable
INSERT INTO #temptable
(
tablecompletename,
tablename
)
SELECT @TableCompleteName,
COALESCE(@tablesname, ”)
SELECT @tablesname = NULL
FETCH NEXT FROM tmp_c
ur INTO @TableCompleteName, @mastertable
END
SELECT tablecompletename AS TableName, tablename AS DependentTables
FROM #temptable
DROP TABLE #temptable
CLOSE tmp_cur
DEALLOCATE tmp_cur
Below is one such script which can do the above work in second, so enjoy the script and find the dependencies among the table.
DECLARE @mastertable VARCHAR(100)
DECLARE @TableCompleteName VARCHAR(100)
DECLARE @tablesname VARCHAR(1000)
CREATE TABLE #temptable
(
tablecompletename VARCHAR(100),
tablename VARCHAR(1000)
)
DECLARE tmp_cur CURSOR static
FOR SELECT s.name + ‘.’ + o.name,
o.name
FROM sys.objects o
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE type = ‘U’
ORDER BY s.name,
o.name
OPEN tmp_cur
–FETCH
FETCH FIRST FROM tmp_cur INTO @TableCompleteName, @mastertable
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @tablesname = COALESCE(@tablesname + ‘,’, ”) + s.name + ‘.’
+ OBJECT_NAME(FKEYID)
FROM SYSFOREIGNKEYS
INNER JOIN sys.objects o ON o.object_id = SYSFOREIGNKEYS.fkeyid
INNER JOIN sys.schemas s ON s.schema_id = o.schema_id
WHERE OBJECT_NAME(RKEYID) = @mastertable
INSERT INTO #temptable
(
tablecompletename,
tablename
)
SELECT @TableCompleteName,
COALESCE(@tablesname, ”)
SELECT @tablesname = NULL
FETCH NEXT FROM tmp_c
ur INTO @TableCompleteName, @mastertable
END
SELECT tablecompletename AS TableName, tablename AS DependentTables
FROM #temptable
DROP TABLE #temptable
CLOSE tmp_cur
DEALLOCATE tmp_cur