4

I've built a stored procedure (which we can call sproc_deps) that uses sys.sql_expression_dependencies and sys.dm_sql_referenced_entities. I want it to list out all of the tables and columns used by a stored procedure of the user's choice. This stored procedure will have its name passed as an argument to sproc_deps.

The problem is that I'm getting columns that the stored procedure doesn't actually use when I combine sys.sql_expression_dependencies and sys.dm_sql_referenced_entities. In order to get the information I want, I've JOINed a few other things on as well:

  • sys.objects(for object IDs, and type_desc)
  • sys.tables(to match against tables contained in sys.sql_expression_dependencies)
  • sys.views (because I'm interested in both views and tables)
  • sys.columns (to pull columns for each table or view involved)

Here is the actual JOIN:

sys.sql_expression_dependencies AS sed  
INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id  
LEFT OUTER JOIN sys.tables t on sed.referenced_entity_name = t.name
LEFT OUTER JOIN sys.views v on sed.referenced_entity_name = v.name
LEFT OUTER JOIN sys.columns c on (c.object_id = t.object_id OR c.object_id = v.object_id)
INNER JOIN sys.dm_sql_referenced_entities (N'dbo.DummySprocName', 'OBJECT') s
ON s.referenced_entity_name = sed.referenced_entity_name

Using just sys.sql_expression_dependencies nets a small list of tables that I can't really decipher, and using sys.dm_sql_referenced_entities yields a partial list of tables and columns used by the procedure.

Is it possible for sproc_deps to return the correct list of tables and columns used by a procedure using just T-SQL? If so, how?

6
  • 1
    Perhaps not terribly helpful if you "can't really decipher" what you've got, but... the gold standard for questions is to provide a minimal reproducible example. Can you demonstrate a repeatable situation where an expected dependency doesn't show up in your query? That by itself might answer your question! And you're always welcome in chat for unstructured troubleshooting. Btw, good first question & welcome to the network! :) Oct 10, 2019 at 20:41
  • Your sample code is missing the actual join predicates for the last inner join, can you include the full text of your query, your expected output and the output you're getting?
    – HandyD
    Oct 11, 2019 at 6:11
  • @HandyD I've been looking at one particular procedure that I know uses 4 tables and 146 columns ( I went through and typed them out). Using sys.sql_expression_dependencies and sys.dm_sql_referenced_entities, I get 96 columns and 4 tables. Using the code I posted, I get 159 columns and 4 tables. I want something that returns the correct amount of tables and columns; in this case it will disregard the 13 columns that aren't actually in use. Oct 11, 2019 at 13:12
  • @PeterVandivier Would it count as reproducible in the case of the procedure below using its own name as an argument? You can see it uses a view and a table - v9_Sproc_DocInfo and z9_BaseTables_Columns. On top of that, I specify the columns used from each. Is a table used only in a SELECT something I shouldn't treat as a dependency? Oct 11, 2019 at 16:51

2 Answers 2

0

I would come at this from the opposite direction.

    select c.table_name, c.column_name, sp.name 
    from INFORMATION_SCHEMA.columns c
    inner join sys.procedures sp on object_definition(sp.object_id) like '%' + c.TABLE_NAME + '%' 
    and object_definition(sp.object_id) like '%' + c.column_name + '%'
3
  • Let's say table A and table B both have a column EmpName. If table A is the only one using that column in my procedure, but I'm also using table B somewhere else in this same procedure, how can I know that EmpName comes from table A? Oct 11, 2019 at 14:31
  • @Antidiscrete you'd have to also read the alias from the sp_text. Not impossible, but it would make it vastly more complex. In my experience, columns with identical names tend to be foreign keys anyways, so if it's needed on one table, it is probably needed on both.
    – James
    Oct 11, 2019 at 14:41
  • Good point. I didn't get to choose the column names in many cases, but there are columns with the same name between two or more tables that have no physical relation. Oct 11, 2019 at 16:31
0

Here is my full code as requested (still working on reproducible example for missing dependencies):

CREATE PROCEDURE [dbo].[usp_v9_SprocDocInfo_FullDependency_SingleSproc]
@SprocName NVARCHAR(150) = ''

AS
BEGIN

DECLARE @ObjName NVARCHAR(128) = NULL
DECLARE @rowCount INT = 0
DECLARE @HasNulls BIT = 0
DECLARE @DepExists BIT = 0

--temp table to hold output
--match this against view!!!
CREATE TABLE #TempData
(
FullName NVARCHAR(300) not null,
ShortName NVARCHAR(128) not null,
TableName NVARCHAR(128),
ObjectName NVARCHAR(128),
column_name NVARCHAR(128),
[definition] NVARCHAR(MAX),
LastUpdated DATETIME,
[Type] NVARCHAR(60),
[object_id] INT,
SprocNo BIGINT
)

--temp table to hold pure dependencies
CREATE TABLE #Sproc_FullTableCols
(
RefEntity NVARCHAR(256),
TableName NVARCHAR(256),
ColName NVARCHAR(256),
TypeDesc NVARCHAR(256)
)

--first, grab known dependency data for this particular sproc from the correct view
INSERT INTO #TempData
SELECT FullName,
ShortName,
TableName,
ObjectName,
column_name,
[definition],
LastUpdated,
[Type],
[OBJECT_ID],
SprocNo
FROM v9_Sproc_DocInfo
WHERE ShortName = @SprocName

--next grab any data not covered in the previous query
--this will be tables/views and ALL columns for objects found in sproc
INSERT INTO #Sproc_FullTableCols
--https://www.sqlrx.com/using-sys-sql_expression_dependencies-as-a-single-source-to-find-referenced-and-referencing-objects/
SELECT DISTINCT OBJECT_NAME(referencing_id) AS referencing_entity_name, 
    CASE WHEN t.name is null then V.name when V.name is null THEN t.name ELSE NULL END,  
    c.name,
    case when t.name is not null then t.type_desc 
        when v.name is not null then v.type_desc 
        else o.type_desc end AS referencing_desciption
FROM sys.sql_expression_dependencies AS sed  
INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id  
LEFT OUTER JOIN sys.tables t on sed.referenced_entity_name = t.name
LEFT OUTER JOIN sys.views v on sed.referenced_entity_name = v.name
LEFT OUTER JOIN sys.columns c on (c.object_id = t.object_id OR c.object_id = v.object_id)
INNER JOIN sys.dm_sql_referenced_entities (N'dbo.' + @SprocName, 'OBJECT') s
ON s.referenced_entity_name = sed.referenced_entity_name
WHERE referencing_id = OBJECT_ID(N'dbo.' + @SprocName)

--clean up
DELETE FROM #Sproc_FullTableCols
WHERE (TableName IS NULL OR ColName IS NULL)-- OR MinorRef IS NULL)

WHILE @HasNulls = 0
BEGIN

    --pull the first row of junk data from the v9 results
    SET @ObjName = (SELECT TOP 1 ObjectName FROM #TempData WHERE (TableName IS NULL AND column_name IS NULL))

    SET @DepExists = CASE WHEN @ObjName IN (SELECT TableName FROM #TempData) THEN 1 ELSE 0 END

    --see if pull was successful; if so update flag accordingly
    IF (@ObjName IS NOT NULL) SET @HasNulls = 1
    ELSE BREAK

    IF @HasNulls = 1 AND @DepExists = 0
    BEGIN

        INSERT INTO #TempData
        SELECT DISTINCT (N'dbo.' + @SprocName) AS FullName,
            @SprocName AS ShortName,
            @ObjName AS TableName,
            '-' AS ObjectName,
            z.ColumnName AS column_name,
            z.Description AS [definition],
            GETDATE() AS LastUpdated,
            '*' + (N'' + c.TypeDesc) AS [Type],
            OBJECT_ID(N'dbo.' + @SprocName) AS [object_id],
            0 AS SprocNo
        FROM z9_BaseTables_Columns z
        INNER JOIN #Sproc_FullTableCols c 
            ON (z.[Table] = c.TableName collate Latin1_General_CI_AI
            AND z.ColumnName = c.ColName collate Latin1_General_CI_AI)
        WHERE z.[Table] = @ObjName collate Latin1_General_CI_AI

    END

    --clean out row used for input
    DELETE TOP (1) FROM #TempData
    WHERE ObjectName = @ObjName
    AND TableName IS NULL
    AND column_name IS NULL

    SET @HasNulls = 0
    SET @ObjName = ''

END

--finally, print results then discard the temp table
SELECT * FROM #TempData

order by [Type] asc, FullName asc, ObjectName asc, TableName asc, column_name asc, LastUpdated asc

DROP TABLE #TempData

DROP TABLE #Sproc_FullTableCols

END

This will create a procedure that grabs known dependency data for a procedure from a view (v9_Sproc_DocInfo) that uses only sys.sql_expression_dependencies and sys.dm_sql_referenced_entities. Then I pull the full list of columns for any table used by the procedure, filter out anything already contained in v9_Sproc_DocInfo, and combine this data with the view data as output.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.