Tags
Get List of Stored Procedures along with the table & column names for all DBs on a SERVER, SQL Puzzle | How to get List of Stored Procedures along with the table & column names for all DBs on a SERVER
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)
--
|
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
Improving my SQL BI Skills 