Tags

,


SQL Puzzle | How to get List of Stored Procedures along with the table names for all DBs on a SERVER

Write a script to get List of Stored Procedures along with the table names for all DBs on a SERVER

Rules/Restrictions

  • The challenge is to do it with T-SQL statements only.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

SOLUTION – 1

--

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

SELECT @sql += N' UNION ALL
SELECT 
  [database]  = ''' + REPLACE(name, '''', '''''') + ''',
  [stored procedure/function] = QUOTENAME(s.name) + ''.'' + QUOTENAME(o.name)
                COLLATE Latin1_General_CI_AI, 
  [table]     = QUOTENAME(d.referenced_schema_name) + ''.'' 
              + QUOTENAME(d.referenced_entity_name)
                COLLATE Latin1_General_CI_AI,
  [column]    = QUOTENAME(d.referenced_minor_name)
                COLLATE Latin1_General_CI_AI
FROM ' + QUOTENAME(name) + '.sys.schemas AS s
INNER JOIN ' + QUOTENAME(name) + '.sys.all_objects AS o
ON s.[schema_id] = o.[schema_id]
CROSS APPLY ' + QUOTENAME(name) 
+ '.sys.dm_sql_referenced_entities'
+ '(QUOTENAME(s.name) + ''.'' + QUOTENAME(o.name), N''OBJECT'') AS d
WHERE d.referenced_minor_id > 0 
AND o.[Type] IN (''P'',''TF'',''FN'') and o.[Is_MS_Shipped] = 0 AND d.referenced_entity_name <> ' + '''MigrateExecutionLog''
AND o.name <> '+ '''MigrateExecutionLog''
'
FROM sys.databases 
  WHERE 
  database_id > 4 
  AND [state] = 0

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

EXEC (@sql)

--

[From Microsoft – https://social.msdn.microsoft.com/Forums/sqlserver/en-US/1e8605eb-59e9-480e-9695-3e4ccb56ec8f/list-tables-and-column-names-a-stored-procedure-is-querying?forum=transactsql%5D

Add a comment if you have a solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com