Below script helps you to create all database tables list along with their dependent tables name in comma separated format.
DECLARE @MainTable VARCHAR(100)
DECLARE @TableFullName VARCHAR(100)
DECLARE @TablesName VARCHAR(1000)
CREATE TABLE #TmpTbl
(TableCompName VARCHAR(100), TableName VARCHAR(1000))
DECLARE Tmp_Cursor 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_Cursor
--FETCH
FETCH FIRST FROM Tmp_Cursor INTO @TableFullName, @MainTable
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) = @MainTable
INSERT INTO #TmpTbl
(TableCompName, TableName)
SELECT @TableFullName, COALESCE(@TablesName, '')
SELECT @TablesName = NULL
FETCH NEXT FROM Tmp_Cursor INTO @TableFullName, @MainTable
END
SELECT TableCompName AS TableName, TableName AS DependentTables
FROM #TmpTbl
DROP TABLE #TmpTbl
CLOSE Tmp_Cursor
DEALLOCATE Tmp_Cursor
No comments:
Post a Comment