How can I get the list of tables in the stored procedure?

Asked 26/4, 2013 at 5:43 Answered 23/4 at 6:50

There are lot of tables and sp in the db. I find the tables name which are used in the specific sp (stored procedure).

sp_depends %sp_name% not give the desire result. I am also used INFORMATION_SCHEMA.TABLES,INFORMATION_SCHEMA.ROUTINES tables.

But the result is not full fill my requirment.

Tennietenniel answered 26/4, 2013 at 5:43 Comment(1)

Hi @Tennietenniel , Did any of these answers solve your problem? Could you tick one please? – Drennan 3/7, 2019 at 12:25

The two highest voted answers use a lot of deprecated tables that should be avoided.
Here's a much cleaner way to do it.

Get all the tables on which a stored procedure depends:

SELECT DISTINCT p.name AS proc_name, t.name AS table_name
FROM sys.sql_dependencies d 
INNER JOIN sys.procedures p ON p.object_id = d.object_id
INNER JOIN sys.tables     t ON t.object_id = d.referenced_major_id
ORDER BY proc_name, table_name

Works with MS SQL SERVER 2005+

List of Changes:

sysdepends should be replaced with sys.sql_dependencies
- The new table uses object_id instead of id
- The new table uses referenced_major_id instead of depid
Using sysobjects should be replaced with more focused system catalog views
- As marc_s pointed out, instead use sys.tables and sys.procedures
- Note: This prevents having to check where o.xtype = 'p' (etc.)
Also, there is really no need for a CTE which uses ROW_NUMBER() just in order to make sure we only have one of each record set returned. That's what DISTINCT is there for!
- In fact, SQL is smart enough to use DISTINCT behind the scenes.
- I submit into evidence: Exhibit A. The following queries have the same Execution Plan!
```
-- Complex
WITH MyPeople AS (
  SELECT id, name,
  ROW_NUMBER() OVER(PARTITION BY id, name ORDER BY id, name) AS row
  FROM People)
SELECT id, name
FROM MyPeople
WHERE row = 1

-- Better
SELECT DISTINCT id, name
FROM People
```

Koblas answered 16/5, 2014 at 14:47 Comment(2)

Not work for me, better stackoverflow.com/a/63573451 – Palaver 2/6, 2021 at 9:7

@Palaver It worked just fine for me (Azure SQL Database). Maybe you are on an older version of SQL Server? – Lysis 5/4, 2023 at 11:7

Try more elegant way (but, it's solution works only in MS SQL 2008 or higher) -

SELECT DISTINCT 
      [object_name] = SCHEMA_NAME(o.[schema_id]) + '.' + o.name
    , o.type_desc
FROM sys.dm_sql_referenced_entities ('dbo.usp_test1', 'OBJECT') d
JOIN sys.objects o ON d.referenced_id = o.[object_id]
WHERE o.[type] IN ('U', 'V')

Oregano answered 26/4, 2013 at 5:56 Comment(1)

Use sys.sql_expression_dependencies stackoverflow.com/a/63573451 – Palaver 2/6, 2021 at 9:8

;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

Fransiscafransisco answered 26/4, 2013 at 5:48 Comment(4)

As of SQL Server 2005 it it recommended to use the more focused system catalog views - like sys.tables and sys.procedures instead of the "general purpose" sys.objects (and sysobjects is deprecated anyway) – Bennir 26/4, 2013 at 6:54

@marc_s, agreed! I added an answer that uses the updated tables. – Koblas 16/5, 2014 at 15:21

What is the reason of using ROW_NUMBER() here? Isn't it easier to do just DISTINCT? – Irruption 9/10, 2017 at 23:22

Looks like other dependent objects are also included here. – Irruption 9/10, 2017 at 23:45

Here is the sql code for this

To get list of tables used in a 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

Reverse - To find Stored Procedure Related to Table in Database – Search in All Stored Procedure

There two ways to this

----Option 1
SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%tablename%'

----Option 2
SELECT DISTINCT o.name, o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE '%tablename%'

PS: sp_help and sp_depends does not always return accurate results.

Reference:

Pestalozzi answered 26/4, 2013 at 5:50 Comment(1)

Use sys.sql_expression_dependencies - view stackoverflow.com/a/63573451 – Palaver 2/6, 2021 at 9:9

KyleMit's answer is using a table that will be deprecated soon as well. It is recommended to use sys.sql_experssion_dependencies instead of sys.sql_dependencies, e.g.,

SELECT DISTINCT p.name AS proc_name, t.name AS table_name
FROM sys.sql_expression_dependencies d
INNER JOIN sys.procedures p ON p.object_id = d.referencing_id
INNER JOIN sys.tables t     ON t.object_id = d.referenced_id
ORDER BY proc_name, table_name

This should work with SQL Server 2008+.

I did not have a high enough reputation to comment directly on the referenced answer.

Fetich answered 12/6, 2018 at 15:29 Comment(1)

Above KyleMit's answer is not giving proper results, but this answer query is giving correct results. – Bara 2/11, 2023 at 9:52

Looks like there is no complete answer for OP question. Most of the answers above don't have either schema name or include other objects (e.g. functions) used in stored procedures.

Full list of tables/views used in stored procedures with schema name and object id

SELECT  DISTINCT
        procObj.[object_id]     AS [ProcObjectId],
        procSchema.[name]       AS [ProcSchema],
        procObj.[Name]          AS [ProcName], 
        tableObj.[object_id]    AS [TableObjectId],
        tableSchema.[name]      AS [TableSchema],
        tableObj.[Name]         AS [TableName]
FROM sys.sql_dependencies AS dep
INNER JOIN sys.objects AS procObj
ON procObj.[object_id] = dep.[object_id]
INNER JOIN sys.schemas AS procSchema 
ON procSchema.[schema_id] = procObj.[schema_id]
INNER JOIN sys.objects AS tableObj
ON tableObj.[object_id] = dep.[referenced_major_id]
INNER JOIN sys.schemas AS tableSchema 
ON tableSchema.[schema_id] = tableObj.[schema_id]
WHERE   procObj.[type] = 'P' 
    -- using this filter we can control dependent object types
    -- e.g. tableObj.[type] IN ('U') - returns tables only
    AND tableObj.[type] IN ('V', 'U')

Note that there is a filter on dependent object types which can be changed (depends on what you want in output results). Full list of type abbreviations is here.

Irruption answered 10/10, 2017 at 0:20 Comment(0)

SELECT 
NAME 
FROM SYSOBJECTS 
WHERE ID IN (   SELECT SD.DEPID 
                FROM SYSOBJECTS SO, 
                SYSDEPENDS SD
                WHERE SO.NAME = 'SP_NAME' 
                AND SD.ID = SO.ID
            )

Silverstein answered 15/4, 2016 at 12:50 Comment(0)

But please note that the sysdepends will not give the table names if they are used in dynamic sql. What I suggest is to search reversely ie: create a loop search the tables in the syscomments. The below stored procedure may help

CREATE PROCEDURE dbo.sp_getObjects
(
    @ObjName VARCHAR(255)
)
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @Idkeyst INTEGER
    DECLARE @Idkeyed INTEGER
    DECLARE @tblName VARCHAR(255)
    DECLARE @Objects VARCHAR(MAX)
    IF NOT EXISTS(SELECT 1 FROM sys.objects where NAME = @ObjName AND type in ('P', 'FN','TR'))
    BEGIN
        PRINT 'NO Text Available for the Parameter'
        RETURN(0)
    END
    CREATE TABLE #ProcStr
    (
        Idkey       INT IDENTITY(1,1),
        ScriptStr   VARCHAR(MAX)
    )
    CREATE TABLE #Depends
    (
        Idkey           INT IDENTITY(1,1),
        Depends         VARCHAR(255)
    )
    CREATE TABLE #Objects
    (
        Idkey           INT IDENTITY(1,1),
        ObjectName      VARCHAR(255)
    )
    INSERT INTO #ProcStr
    (ScriptStr)
    EXEC sp_helptext @ObjName
    DELETE #ProcStr WHERE LTRIM(ScriptStr) LIKE '--%'
    DELETE #ProcStr WHERE LTRIM(REPLACE(ScriptStr,CHAR(9),'')) LIKE '--%'

    SET @Idkeyst = 0
    SET @Idkeyed = 0
    WHILE 1=1
    BEGIN
        SELECT @Idkeyst = MIN(idKey) FROM #ProcStr WHERE ScriptStr like '%/*%' and Idkey > @Idkeyst
        IF @Idkeyst IS NULL
            BREAK
        SELECT @Idkeyed = MIN(idKey) FROM #ProcStr WHERE ScriptStr like '%*/%' and Idkey >= @Idkeyst
        DELETE #ProcStr WHERE Idkey >= @Idkeyst and Idkey <=@Idkeyed
    END
    DELETE #ProcStr WHERE ISNULL(LTRIM(REPLACE(ScriptStr,CHAR(9),'')),'')=''    
    INSERT INTO #Depends
    (Depends)
    SELECT DISTINCT t.name 
    FROM sys.sql_dependencies d 
    INNER JOIN sys.procedures p ON p.object_id = d.object_id
    INNER JOIN sys.tables     t ON t.object_id = d.referenced_major_id
    where p.name = @ObjName

    INSERT INTO #Objects
    SELECT name from sys.objects o WHERE Type = 'U' AND NOT EXISTS
    (SELECT 1 FROM #Depends WHERE Depends = o.name)

    SET @Objects = ''
    SELECT @Objects = CASE WHEN ISNULL(@Objects,'') = '' THEN '' ELSE @Objects+', ' END+ Depends
    FROM #Depends

    UPDATE #ProcStr
    SET ScriptStr = LTRIM(RTRIM(ScriptStr))
    UPDATE #ProcStr
    SET ScriptStr = REPLACE(ScriptStr,CHAR(9),'')
    UPDATE #ProcStr
    SET ScriptStr = REPLACE(ScriptStr,CHAR(13),'')
    UPDATE #ProcStr
    SET ScriptStr = REPLACE(ScriptStr,CHAR(10),'')
    SET @tblName = ''
    SET @Idkeyst = 0
    WHILE 1=1
    BEGIN
        SELECT @Idkeyst = MIN(idKey) FROM #Objects WHERE Idkey > @Idkeyst
        IF @Idkeyst IS NULL
            BREAK
        SELECT @tblName = ObjectName FROM #Objects WHERE Idkey = @Idkeyst
        IF Exists (SELECT 1 FROM #ProcStr WHERE (ScriptStr LIKE '% '+@tblName+' %' 
                        OR ScriptStr LIKE '%.'+@tblName+' %' OR ScriptStr LIKE @tblName+' %' OR ScriptStr LIKE @tblName
                        --OR ScriptStr LIKE '%'+@tblName 
                        OR ScriptStr LIKE '% '+@tblName+'''%' OR ScriptStr LIKE @tblName+'''%'))
        BEGIN
            SET @Objects = CASE WHEN ISNULL(@Objects,'')<>'' THEN @Objects+', '+@tblName ELSE @tblName END
        END
    END

    IF ISNULL(@Objects,'') = ''
    BEGIN
        PRINT 'NO Tables are reffered in the stored procedures'
        RETURN(0)
    END
    PRINT @Objects
    SET NOCOUNT OFF
END

Isaisaac answered 25/11, 2015 at 14:21 Comment(1)

Don't understand why this was down voted? Is it not mandatory to give a comment while you are down voting a post? For my understanding, we could not rely on sysdepends. – Isaisaac 7/8, 2017 at 16:27

Here is an example to find list of tables used in a procedure

;WITH procs
AS
(
SELECT o1.name AS proc_name,
o2.name AS table_name,
ROW_NUMBER() OVER(PARTITION BY o1.name,o2.name ORDER BY o1.name,o2.name) AS row
FROM sysdepends d
INNER JOIN sysobjects o1
ON o1.id=d.id
INNER JOIN sysobjects o2
ON o2.id=d.depid
WHERE o1.xtype = 'P'
--AND o2.name = 'tabname1' OR o2.name = 'tblname2'
)
SELECT proc_name, table_name
FROM procs
WHERE row = 1
ORDER BY proc_name, table_name

Also, this query returns all the table names of all dependent tables in a Stored procedure.

SELECT DISTINCT o.id, o.name as 'Procedure_Name' , oo.name as 'Table_Name'
FROM sysdepends d, sysobjects o, sysobjects oo
WHERE o.id=d.id 
and oo.id=d.depid and depnumber=1
ORDER BY o.name,oo.name

Lao answered 26/4, 2013 at 5:48 Comment(0)

This code recursively looks at every Stored Procedure in your Stored Procedure and gives you the full list of all tables used.



    declare @sp_name varchar(100)
    declare @curSpName varchar(100)
    declare @curObjName varchar(255)
    declare @curXType varchar(1)
    
    create table #tmpTables
    (
    proc_name varchar(255),
    table_name varchar(255)
    )
    
    set @sp_name = 'STORED_PROCEDURE_NAME'
    
    ;WITH stored_procedures AS (
    SELECT 
    o.name AS proc_name, oo.name AS table_name, oo.type AS xType,
    ROW_NUMBER() OVER(partition by o.name,oo.name, oo.Type ORDER BY o.name,oo.name, oo.Type) 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'
    --and oo.type = 'U' --Tables
    and o.name = @sp_name)
    SELECT proc_name, table_name, xType, 'N' Processed into ##tmpSP
    FROM stored_procedures 
    WHERE row = 1
    --ORDER BY proc_name,table_name
    
    
    While (Select count(*) from ##tmpSP where Processed = 'N')  0
    Begin
    
        Select top  1 @curSpName = proc_name, @curObjName = table_name, @curXType = xType
        from ##tmpSP
        where Processed = 'N'
    
    
        if @curXType = 'U'
        Begin
    
            insert into #tmpTables
            values (@curSpName, @curObjName)
    
        End
    
        if @curXType = 'P'
        Begin
    
            ;WITH stored_procedures AS (
            SELECT 
            o.name AS proc_name, oo.name AS table_name, oo.type AS xType,
            ROW_NUMBER() OVER(partition by o.name,oo.name, oo.Type ORDER BY o.name,oo.name, oo.Type) 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'
            and oo.type = 'U' --Tables
            and o.name = @curObjName)
            insert into #tmpTables
            SELECT @curSpName, table_name 
            FROM stored_procedures 
            WHERE row = 1
            ORDER BY proc_name,table_name
    
            ;WITH stored_procedures AS (
            SELECT 
            o.name AS proc_name, oo.name AS table_name, oo.type AS xType,
            ROW_NUMBER() OVER(partition by o.name,oo.name, oo.Type ORDER BY o.name,oo.name, oo.Type) 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'
            and oo.type = 'P' --SP's
            and o.name = @curObjName)
            insert into ##tmpSP
            SELECT proc_name, table_name, xType, 'N'  
            FROM stored_procedures 
            WHERE row = 1
            and proc_name not in 
            (
                Select proc_name
                from ##tmpSP
            )
            ORDER BY proc_name,table_name
    
        End
    
        if @curXType = 'v'
        Begin
    
            ;WITH stored_procedures AS (
            SELECT 
            o.name AS proc_name, oo.name AS table_name, oo.type AS xType,
            ROW_NUMBER() OVER(partition by o.name,oo.name, oo.Type ORDER BY o.name,oo.name, oo.Type) 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 = 'v'
            and oo.type = 'U' --Tables
            and o.name = @curObjName)
            insert into #tmpTables
            SELECT proc_name, table_name 
            FROM stored_procedures 
            WHERE row = 1
            ORDER BY proc_name,table_name
    
        End
    
        update ##tmpSP
        set Processed = 'Y'
        where table_name = @curObjName
    
    
    End
    
    
    Select distinct table_name
    from #tmpTables
    
    drop table #tmpTables
    drop table ##tmpSP

Oatmeal answered 30/3, 2016 at 15:25 Comment(0)

SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%Tablename%'

Loxodromic answered 4/8, 2021 at 14:26 Comment(0)

KyleMit's solution lists all the tables that are referenced by stored procedures, that are contained in the same database. What if you need to list all tables referenced, by stored procedures, including those that belong to other databases? For example you may have a staging database, used for reporting purposes, that accesses a proprietary database for complex reporting. You need to know which tables from the proprietary database are being referenced.

NB If you want to see all the referenced tables comment out the Where clause.

SELECT s.name, p.name, 
  db  = COALESCE(d.referenced_database_name, DB_NAME()),
  obj = COALESCE(d.referenced_schema_name, s.name) + N'.' + 
  d.referenced_entity_name
FROM sys.sql_expression_dependencies AS d
INNER JOIN sys.procedures AS p ON p.[object_id] = d.referencing_id
INNER JOIN sys.schemas AS s ON p.[schema_id] = s.[schema_id]
Where COALESCE(d.referenced_database_name, DB_NAME()) = 'proprietary database'
Group By s.name, p.name, 
d.referenced_database_name, d.referenced_schema_name, s.name, 
d.referenced_entity_name
ORDER BY obj;

References:- Stack Exchange How can I get the list of tables in all stored procedures

Dacy answered 23/4 at 6:50 Comment(0)

Hot tags

Godot Unity Godot Help Programming Godot 4.X GUI GDScript 3D 2D Physics CSharp Godot 3.X VR XR Projects C++

Get all the tables on which a stored procedure depends:

List of Changes:

To get list of tables used in a stored procedure

Reverse - To find Stored Procedure Related to Table in Database – Search in All Stored Procedure

Recommended topics

Hot tags