Last active
July 4, 2024 09:20
-
-
Save jedidja/c89d9c35eb000d18db43e883bce96eb1 to your computer and use it in GitHub Desktop.
Get a list of tables and views referenced by sproc in SQL Server
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
(Generated by Claude 3.5 Sonnet) | |
-- Notes: | |
-- It won't catch tables referenced in dynamic SQL. | |
-- It may not catch deeply nested dependencies (e.g., views referencing other views). | |
-- It doesn't distinguish between read and write operations. | |
CREATE OR ALTER PROCEDURE sp_GetTableNames | |
@ProcedureName NVARCHAR(128) | |
AS | |
BEGIN | |
SET NOCOUNT ON; | |
-- Table to store direct dependencies | |
CREATE TABLE #DirectDependencies ( | |
SchemaName NVARCHAR(128), | |
ObjectName NVARCHAR(128), | |
ObjectType CHAR(2) | |
) | |
-- Insert direct dependencies | |
INSERT INTO #DirectDependencies (SchemaName, ObjectName, ObjectType) | |
SELECT DISTINCT | |
OBJECT_SCHEMA_NAME(referenced_id) AS SchemaName, | |
OBJECT_NAME(referenced_id) AS ObjectName, | |
CASE | |
WHEN OBJECTPROPERTY(referenced_id, 'IsTable') = 1 THEN 'T' | |
WHEN OBJECTPROPERTY(referenced_id, 'IsView') = 1 THEN 'V' | |
END AS ObjectType | |
FROM sys.sql_expression_dependencies | |
WHERE referencing_id = OBJECT_ID(@ProcedureName) | |
AND referenced_entity_name IS NOT NULL | |
AND referenced_id IS NOT NULL | |
AND (OBJECTPROPERTY(referenced_id, 'IsTable') = 1 OR OBJECTPROPERTY(referenced_id, 'IsView') = 1) | |
-- Table to store view dependencies | |
CREATE TABLE #ViewDependencies ( | |
ViewSchema NVARCHAR(128), | |
ViewName NVARCHAR(128), | |
ReferencedSchema NVARCHAR(128), | |
ReferencedTable NVARCHAR(128) | |
) | |
-- Get dependencies for each view | |
INSERT INTO #ViewDependencies (ViewSchema, ViewName, ReferencedSchema, ReferencedTable) | |
SELECT | |
dd.SchemaName AS ViewSchema, | |
dd.ObjectName AS ViewName, | |
OBJECT_SCHEMA_NAME(sed.referenced_id) AS ReferencedSchema, | |
OBJECT_NAME(sed.referenced_id) AS ReferencedTable | |
FROM #DirectDependencies dd | |
INNER JOIN sys.objects o ON dd.SchemaName = SCHEMA_NAME(o.schema_id) AND dd.ObjectName = o.name AND o.type = 'V' | |
INNER JOIN sys.sql_expression_dependencies sed ON o.object_id = sed.referencing_id | |
WHERE OBJECTPROPERTY(sed.referenced_id, 'IsTable') = 1 | |
-- Display results | |
SELECT | |
dd.SchemaName + '.' + dd.ObjectName AS FullObjectName, | |
dd.ObjectType, | |
STUFF(( | |
SELECT ', ' + ReferencedSchema + '.' + ReferencedTable | |
FROM #ViewDependencies vd | |
WHERE vd.ViewSchema = dd.SchemaName AND vd.ViewName = dd.ObjectName | |
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS ReferencedTables | |
FROM #DirectDependencies dd | |
ORDER BY dd.ObjectType, dd.SchemaName, dd.ObjectName | |
-- Clean up | |
DROP TABLE #DirectDependencies | |
DROP TABLE #ViewDependencies | |
END |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment