CODEWITHSUNDEEP
sqlsql-serversql-server-2008stored-procedures
Chow
Chow

Reputation: 103

List all columns referenced in all procedures of all databases

Is there a way that I can get all the columns and tables referenced in all the stored procedures in all the databases in an instance? The output should be:

Database   Procedure   Table   Column 
--------   ---------   -----   ------

Upvotes: 2

Views: 3451

Answers (3)

user275683
user275683

Reputation:

Here is yet another way to do this. This is very DIRTY but I like it. Why? Because I came up with it. Anyway it is using Dynamic SQL inside Dynamic SQL to insert dependency information into temp table that can be queried.

This can be modified into a SP that you can run from time to time to update dependencies information, also temp table can be changes to real table if you want to store it.

IF OBJECT_ID('tempdb.dbo.#SPDependencyDetails') IS NOT NULL
DROP TABLE #SPDependencyDetails

CREATE TABLE #SPDependencyDetails
(
 Or_Object_Database NVARCHAR(128)
,Or_Object_Name NVARCHAR(128)
,Ref_Database_Name NVARCHAR(128)
,Ref_Schema_Name NVARCHAR(128)
,Ref_Object_Name NVARCHAR(128)
,Ref_Column_Name NVARCHAR(128)
,Is_Selected BIT
,Is_Updated BIT
,Is_Select_All BIT
,Is_All_Columns_Found BIT
)

DECLARE @database_name VARCHAR(100)

DECLARE database_cursor CURSOR
FOR
SELECT name
    FROM sys.databases
    WHERE database_id > 4

OPEN database_cursor

FETCH NEXT FROM database_cursor
INTO @database_name

WHILE @@FETCH_STATUS = 0 --Outer Loop begin
BEGIN
    DECLARE  @WholeLotofSQL NVARCHAR(MAX) =       '
    DECLARE @object_name VARCHAR(150)
    ,@sqlstatement NVARCHAR(2500)

    DECLARE object_cursor CURSOR --Inner cursor, iterates list of objects that match type
    FOR
        SELECT name
            FROM '+@database_name+'.sys.objects AS o
            WHERE o.type = ''P'' --Change Object type to find dependencies of Functions, Views and etc.
            ORDER BY 1    

    OPEN object_cursor
    FETCH NEXT FROM object_cursor INTO @object_name

    WHILE @@FETCH_STATUS = 0  --Inner Loop Begin
        BEGIN
            SET @sqlstatement = ''USE '+@database_name+';

                                INSERT INTO #SPDependencyDetails
                                SELECT DB_NAME() AS Or_Object_Database
                                        ,'''''' + @object_name + '''''' AS Or_Object_Name
                                        ,CASE WHEN referenced_database_name IS NULL THEN DB_NAME()
                                                ELSE referenced_database_name
                                        END AS Ref_Database_Name
                                        ,referenced_schema_name AS Ref_Schema_Name
                                        ,referenced_entity_name AS Ref_Object_Name
                                        ,referenced_minor_name AS Ref_Column_Name
                                        ,is_selected
                                        ,is_updated
                                        ,is_select_all
                                        ,is_all_columns_found
                                    FROM sys.dm_sql_referenced_entities(''''dbo.'' + @object_name + '''''', ''''OBJECT'''');''

            EXEC sys.sp_executesql @sqlstatement

            FETCH NEXT FROM object_cursor INTO @object_name
        END      
    CLOSE object_cursor
    DEALLOCATE object_cursor'

    EXEC sys.sp_executesql @WholeLotofSQL

    FETCH NEXT FROM database_cursor INTO @database_name
END

CLOSE database_cursor;
DEALLOCATE database_cursor;

SELECT Or_Object_Database as 'Database'
,Or_Object_Name as 'Procedure'
,Ref_Object_Name as 'Table'
,Ref_Column_Name as 'Column
FROM #SPDependencyDetails

Upvotes: 0

bgs
bgs

Reputation: 3223

To list all SP Name have contain the specified column name:

SELECT OBJECT_NAME(M.object_id), M.* 
FROM sys.sql_modules M 
JOIN sys.procedures P 
ON M.object_id = P.object_id 
WHERE M.definition LIKE '%ColumnName%'

Upvotes: 1

Aaron Bertrand
Aaron Bertrand

Reputation: 280644

This will get the list you're after, however it won't help you if you have such column references embedded in dynamic SQL (and may not find references that rely on deferred name resolution). SQL Server doesn't parse the text of the stored procedure to come up with the DMV output.

Try now with COLLATE clauses to deal with cases where you have databases on the same server with different collations.

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'UNION ALL
SELECT 
  [database]  = ''' + REPLACE(name, '''', '''''') + ''',
  [procedure] = QUOTENAME(s.name) + ''.'' + QUOTENAME(p.name)
                COLLATE Latin1_General_CI_AI, 
  [table]     = QUOTENAME(referenced_schema_name) + ''.'' 
              + QUOTENAME(referenced_entity_name)
                COLLATE Latin1_General_CI_AI,
  [column]    = QUOTENAME(referenced_minor_name)
                COLLATE Latin1_General_CI_AI
FROM ' + QUOTENAME(name) + '.sys.schemas AS s
INNER JOIN ' + QUOTENAME(name) + '.sys.procedures AS p
ON s.[schema_id] = p.[schema_id]
CROSS APPLY ' + QUOTENAME(name) 
+ '.sys.dm_sql_referenced_entities'
+ '(QUOTENAME(s.name) + ''.'' + QUOTENAME(p.name), N''OBJECT'') AS d
WHERE d.referenced_minor_id > 0'
FROM sys.databases 
  WHERE database_id > 4 
  AND [state] = 0;

SET @sql = STUFF(@sql,1,11,'');

EXEC sp_executesql @sql;

Also the CROSS APPLY syntax won't work if you have databases that are in 80 compatibility mode. Just make sure you don't execute the code in such a database and it should work fine (even if some of the target databases are in 80).

Upvotes: 4

Related Questions