Search

Tuesday, September 27, 2011

Get Tables List With Dependent Tables Name


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