列出所有数据库的所有过程中引用的所有列 [英] 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 
--------   ---------   -----   ------

推荐答案

这将获得您想要的列表，但是如果您在动态 SQL 中嵌入了此类列引用(并且可能找不到引用)，它将无济于事依赖于延迟名称解析).SQL Server 不会解析存储过程的文本以得出 DMV 输出.

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.

现在尝试使用 COLLATE 子句来处理您在同一服务器上拥有不同排序规则的数据库的情况.

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;

如果您的数据库处于 80 兼容模式，CROSS APPLY 语法也将不起作用.只要确保您不在这样的数据库中执行代码并且它应该可以正常工作(即使某些目标数据库在 80 中).

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).

这篇关于列出所有数据库的所有过程中引用的所有列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！