List all tables used in a SQL statement

Question

Currently i have to create a list of all tables & schemes used in several (terribly) long SQL statements. Of course i could go manually through the SQL statements and write down every table used in a JOIN, Subselect and so on.

But i'd be curious if there is any easier way to do it. maybe with a "simple" SQL statement, but i have no clue how to do it.

Why am i asking? I'd have to do this for about 50 SQL-Unload-Statements, where all of them use between 30 and 70 tables. I guess you can imagine this quite boring work

Thanks in advance for any input & hints!

Answer 1

Have a read here: How can i get the list of tables in the stored procedure

;WITH stored_procedures AS (
SELECT 
o.name AS proc_name, oo.name AS table_name,
ROW_NUMBER() OVER(partition by o.name,oo.name ORDER BY o.name,oo.name) AS row
FROM sysdepends d 
INNER JOIN sysobjects o ON o.id=d.id
INNER JOIN sysobjects oo ON oo.id=d.depid
WHERE o.xtype = 'P')
SELECT proc_name, table_name FROM stored_procedures
WHERE row = 1
ORDER BY proc_name,table_name

All credit to the OP of the code above in the thread above.

Answer 2

This may help. Using DMVs

IF EXISTS (
        SELECT *
        FROM sys.objects
        WHERE object_id = OBJECT_ID(N'[dbo].[sp_dependsobj]')
            AND type IN (
                N'P'
                ,N'PC'
                )
        )
    DROP PROCEDURE [dbo].[sp_dependsobj]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_dependsobj] (@Objname NVARCHAR(2000))
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @ObjID INTEGER
    DECLARE @ObjType VARCHAR(100)
    DECLARE @RowCount INTEGER = 0

    SELECT @ObjID = OBJECT_ID(@Objname)

    SELECT @ObjType = TYPE
    FROM sys.objects
    WHERE object_id = @ObjID

    DECLARE @RefObjects AS TABLE (
        [Object_name] SYSNAME
        ,[Type] VARCHAR(255)
        ,referenced_entity_name SYSNAME
        ,Column_name SYSNAME NULL
        ,referenced_entity_type VARCHAR(255)
        )
    DECLARE @FoundIn AS TABLE (
        [Object_name] SYSNAME
        ,[Found In] SYSNAME
        ,[Type] VARCHAR(255)
        )
    DECLARE @SQLStr VARCHAR(MAX)

    SET @SQLStr = '
    WITH CTE_Objects
    AS
    (
    SELECT o.NAME referencing_entity_name
          ,o.type_desc referencing_object_type
          ,sed.referenced_entity_name
          ,refobj.referenced_minor_name
          ,obj.type_desc
          ,obj.object_id
    FROM sys.sql_expression_dependencies sed
    INNER JOIN sys.objects o ON sed.referencing_id = o.[object_id]
    INNER JOIN sys.objects obj ON sed.referenced_entity_name = obj.name
    INNER JOIN sys.dm_sql_referenced_entities(''dbo.' + @Objname + ''', ''OBJECT'') refobj ON
    refobj.referenced_id = sed.referenced_id 
    WHERE o.NAME = ''' + @Objname + '''
    UNION
    SELECT ''' + @Objname + ''', (SELECT type_desc from sys.objects where name = ''' + @Objname + '''), referenced_entity_name, NULL, obj.type_desc
          ,obj.object_id
    FROM sys.dm_sql_referenced_entities (''dbo.' + '' + @Objname + 
        ''', ''OBJECT'') refObj INNER JOIN sys.objects obj
    ON refObj.referenced_entity_name = obj.name 
    )
    SELECT CTE.referencing_entity_name Object_name, CTE.referencing_object_type Type, CTE.referenced_entity_name, CTE.referenced_minor_name Column_name, CTE.type_desc referenced_entity_type
    FROM CTE_Objects CTE LEFT JOIN sys.columns cl ON
    cte.object_id =  cl.object_id AND cte.referenced_minor_name = cl.name
    Order by cte.referencing_entity_name, CASE WHEN cte.referencing_entity_name IS NULL THEN 0 ELSE cl.column_id END
    '

    INSERT INTO @RefObjects (
        [Object_name]
        ,[Type]
        ,referenced_entity_name
        ,Column_name
        ,referenced_entity_type
        )
    EXEC (@SQLStr)

    SET @RowCount = @@ROWCOUNT

    IF @RowCount > 0
    BEGIN
        SELECT *
        FROM @RefObjects
    END

    SET @SQLStr = '
    SELECT DISTINCT [Object Name] = o.NAME
          ,[Found In] = sp.NAME
          ,sp.type_desc Type
    FROM sys.objects o
    INNER JOIN sys.sql_expression_dependencies sd ON o.object_id = sd.referenced_id
    INNER JOIN sys.objects sp ON sd.referencing_id = sp.object_id
    WHERE o.NAME = ''' + @Objname + '''
    UNION
    SELECT ''' + @Objname + ''', referencing_entity_name, (SELECT type_desc from sys.objects where name = referencing_entity_name)
    FROM sys.dm_sql_referencing_entities (''dbo.' + '' + @Objname + ''', ''OBJECT'');  
    '

    INSERT INTO @FoundIn (
        [Object_name]
        ,[Found In]
        ,[Type]
        )
    EXEC (@SQLStr)

    SET @RowCount = @@ROWCOUNT

    IF @RowCount > 0
    BEGIN
        SELECT *
        FROM @FoundIn
    END

    IF NOT EXISTS (
            SELECT 1
            FROM @RefObjects
            )
        AND NOT EXISTS (
            SELECT 1
            FROM @FoundIn
            )
    BEGIN
        PRINT 'Object does not reference any object, and no objects reference it.'
    END

    SET NOCOUNT OFF
END
GO

Answer 3

The question is old, but here is a regex for those that want the tables used in SQL statement (From/join).

I created it specially for my case, but it may be adapt and useful for others:

(from|join)[ ]{1,}[^.\n\ ]+?(?=(.))[.].*?(?=( |\n))