our company is planning to administer the report server database and the requirement is to get the list of the tables used in stored-procedures in the productions server database.Is there any query/function to retrieve this information ?
-
If your procedures use dynamic code like select @Tname = 'MyTable' + datename(year, getdate()); exec('select * from ' + @Tname) it's not possible to determine if MyTable2018 is used or not– sepupicJan 8, 2018 at 10:41
-
1But you can try to use sys.sql_expression_dependencies and sys.sql_modules.definition like ...– sepupicJan 8, 2018 at 10:43
1 Answer
If you use dynamic SQL to construct table names (or accept them as arguments), or use nested views, it becomes very difficult to automate this. However if you stick to traditional T-SQL and proper references you can get there with this:
DECLARE @procid int = OBJECT_ID(N'dbo.procedurename');
;WITH src AS
(
SELECT name =
COALESCE(QUOTENAME(d.referenced_server_name) + N'.', N'')
+ COALESCE(QUOTENAME(d.referenced_database_name) + N'.', N'')
+ QUOTENAME(d.referenced_schema_name) + N'.'
+ QUOTENAME(d.referenced_entity_name)
FROM sys.sql_expression_dependencies AS d
WHERE d.referencing_id = @procid
)
SELECT name FROM src GROUP BY name;
And if you just want a catalog of all references, you can do this:
;WITH src AS
(
SELECT [procedure] = QUOTENAME(s.name) + N'.' + QUOTENAME(o.name),
ref =
COALESCE(QUOTENAME(d.referenced_server_name) + N'.', N'')
+ COALESCE(QUOTENAME(d.referenced_database_name) + N'.', N'')
+ QUOTENAME(d.referenced_schema_name) + N'.'
+ QUOTENAME(d.referenced_entity_name)
FROM sys.sql_expression_dependencies AS d
INNER JOIN sys.objects AS o
ON d.referencing_id = o.[object_id]
INNER JOIN sys.schemas AS s
ON o.[schema_id] = s.[schema_id]
WHERE o.[type] = N'P'
)
SELECT [procedure],ref
FROM src
GROUP BY [procedure],ref;