January 7, 2023 at 6:14 am
Hi all,
Truly not a developer here. Just a reporting analyst on a team with a bunch of untrained users of SQL. We are report writers, not developers or DBAs. We have a ton of stored procedures that use each others' output tables, and the relationships are becoming difficult to manage via manual documentation.
I'm looking for any way to systematically output, for any stored procedure, a list of the physical tables that are read (whether via from or join), and that are written (whether via create, truncate, insert, update or select into) by the procedure.
I'm open to using any method, but with the limitation that we do not have access to any other programming language or environment into which we can import our procedure text. All I've got is whatever SQL Server can do.
Any suggestions?
Thanks in advance!
January 7, 2023 at 11:32 am
Suggest you start by looking at sys.sql_dependencies, sys.sql_expression_dependencies and sys.sysdepends.
😎
Here is an example of code for static t-sql code analysis (the first example I found in my repository)
USE TEEST;
GO
SET NOCOUNT ON;
GO
SELECT
SM.object_id AS O_ID
,OBJECT_NAME(SM.object_id) AS O_NAME
,LEN(SM.definition) - (LEN(REPLACE(SM.definition,NCHAR(10),N''))) AS O_NUMBER_OF_LINES
,LEN(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SM.definition,NCHAR(32),N''),NCHAR(9),N''),NCHAR(45),N''),NCHAR(10),N''),NCHAR(13),N'')) AS O_NUMBER_OF_CHARS
,(SELECT COUNT(*) FROM sys.sql_expression_dependencies SD
WHERE SD.referencing_id = SM.object_id
AND OBJECT_NAME(SD.referencing_id) NOT LIKE N'DATAVIEW_%'
AND SD.referenced_database_name IS NULL) AS O_INTERNAL_DEPENDENCIES
,(SELECT COUNT(*) FROM sys.sql_expression_dependencies SD
WHERE SD.referencing_id = SM.object_id
AND SD.referenced_database_name IS NOT NULL) AS O_EXTERNAL_DEPENDENCIES
,(SELECT COUNT(*) FROM sys.sql_expression_dependencies SD
WHERE SD.referenced_id = SM.object_id) AS O_DEPENDENTS
,OBJECTPROPERTY(SM.object_id,'IsInlineFunction') AS O_IsInlineFunction
,OBJECTPROPERTY(SM.object_id,'IsProcedure') AS O_IsProcedure
,OBJECTPROPERTY(SM.object_id,'IsScalarFunction') AS O_IsScalarFunction
,ISNULL(OBJECTPROPERTY(SM.object_id,'IsSchemaBound'),0) AS O_IsSchemaBound
,OBJECTPROPERTY(SM.object_id,'IsTableFunction') AS O_IsTableFunction
,OBJECTPROPERTY(SM.object_id,'IsTrigger') AS O_IsTrigger
,OBJECTPROPERTY(SM.object_id,'IsView') AS O_IsView
,LEN(SM.definition) - (LEN(REPLACE(SM.definition,NCHAR(45),N''))) AS O_NUMBER_OF_HYPHEN
,LEN(SM.definition) - (LEN(REPLACE(SM.definition,NCHAR(42),N''))) AS O_NUMBER_OF_ASTERISK
,LEN(SM.definition) - (LEN(REPLACE(SM.definition,NCHAR(59),N''))) AS O_NUMBER_OF_SEMICOLON
,LEN(SM.definition) - (LEN(REPLACE(SM.definition,NCHAR(58),N''))) AS O_NUMBER_OF_COLON
,CEILING((LEN(SM.definition) - (LEN(REPLACE(SM.definition,N'JOIN',N'')))) * (1.0 / (LEN(N'JOIN') + 0.0))) AS O_NUMBER_OF_JOIN
,CEILING((LEN(SM.definition) - (LEN(REPLACE(SM.definition,N'DISTINCT',N'')))) * (1.0 / (LEN(N'DISTINCT') + 0.0))) AS O_NUMBER_OF_DISTINCT
,CEILING((LEN(SM.definition) - (LEN(REPLACE(SM.definition,N'NOLOCK',N'')))) * (1.0 / (LEN(N'NOLOCK') + 0.0))) AS O_NUMBER_OF_NOLOCK
,CEILING((LEN(SM.definition) - (LEN(REPLACE(SM.definition,N'READ UNCOMMITTED',N'')))) * (1.0 / (LEN(N'READ UNCOMMITTED') + 0.0))) AS O_NUMBER_OF_ISOLATION_READ_UNCOMMITTED
,CEILING((LEN(SM.definition) - (LEN(REPLACE(SM.definition,N'CURSOR',N'')))) * (1.0 / LEN(N'CURSOR'))) AS O_NUMBER_OF_CURSOR
,CEILING((LEN(SM.definition) - (LEN(REPLACE(SM.definition,N'WHILE',N'')))) * (1.0 / LEN(N'WHILE'))) AS O_NUMBER_OF_WHILE
,CEILING((LEN(SM.definition) - (LEN(REPLACE(SM.definition,N'TABLE',N'')))) * (1.0 / LEN(N'TABLE'))) AS O_NUMBER_OF_TABLE
,CEILING((LEN(SM.definition) - (LEN(REPLACE(SM.definition,N'@@IDENTITY',N'')))) * (1.0 / LEN(N'@@IDENTITY'))) AS O_NUMBER_OF_@@IDENTITY
,CEILING((LEN(SM.definition) - (LEN(REPLACE(SM.definition,N'SCOPE_IDENTITY',N'')))) * (1.0 / LEN(N'SCOPE_IDENTITY'))) AS O_NUMBER_OF_SCOPE_IDENTITY
,CEILING((LEN(SM.definition) - (LEN(REPLACE(SM.definition,N'READUNCOMMITTED',N'')))) * (1.0 / LEN(N'READUNCOMMITTED'))) AS O_NUMBER_OF_READUNCOMMITED
,CEILING((LEN(SM.definition) - (LEN(REPLACE(SM.definition,N'WAITFOR',N'')))) * (1.0 / LEN(N'WAITFOR'))) AS O_NUMBER_OF_WAITFOR
,CEILING((LEN(SM.definition) - (LEN(REPLACE(SM.definition,N'ROWCOUNT',N'')))) * (1.0 / LEN(N'ROWCOUNT'))) AS O_NUMBER_OF_ROWCOUNT
,CEILING((LEN(SM.definition) - (LEN(REPLACE(SM.definition,N'EXEC ',N'')))) * (1.0 / LEN(N'EXEC') + 1)) AS O_NUMBER_OF_EXEC
,CEILING((LEN(SM.definition) - (LEN(REPLACE(SM.definition,N'sp_executesql',N'')))) * (1.0 / LEN(N'sp_executesql'))) AS O_NUMBER_OF_sp_executesql
,CEILING((LEN(SM.definition) - (LEN(REPLACE(SM.definition,N'FASTFIRSTROW',N'')))) * (1.0 / LEN(N'FASTFIRSTROW'))) AS O_NUMBER_OF_FASTFIRSTROW
,CEILING((LEN(SM.definition) - (LEN(REPLACE(SM.definition,N'COMPUTE',N'')))) * (1.0 / LEN(N'COMPUTE'))) AS O_NUMBER_OF_COMPUTE
,CEILING((LEN(SM.definition) - (LEN(REPLACE(SM.definition,N'*=',N'')))) * (1.0 / LEN(N'*='))) +
CEILING((LEN(SM.definition) - (LEN(REPLACE(SM.definition,N'=*',N'')))) * (1.0 / LEN(N'=*'))) AS O_NUMBER_OF_OLD_JOIN
,CEILING((LEN(SM.definition) - (LEN(REPLACE(SM.definition,N'text',N'')))) * (1.0 / LEN(N'text'))) AS O_NUMBER_OF_text
,CEILING((LEN(SM.definition) - (LEN(REPLACE(SM.definition,N'ntext',N'')))) * (1.0 / LEN(N'ntext'))) AS O_NUMBER_OF_ntext
,CEILING((LEN(SM.definition) - (LEN(REPLACE(SM.definition,N'image',N'')))) * (1.0 / LEN(N'image'))) AS O_NUMBER_OF_image
,CEILING((LEN(SM.definition) - (LEN(REPLACE(SM.definition,N'.sys',N'')))) * (1.0 / LEN(N'.sys'))) AS O_NUMBER_OF_sys
,CEILING((LEN(SM.definition) - (LEN(REPLACE(SM.definition,N'OPENQUERY',N'')))) * (1.0 / LEN(N'OPENQUERY'))) AS O_NUMBER_OF_OPENQUERY
,CEILING((LEN(SM.definition) - (LEN(REPLACE(SM.definition,N'OPENROWSET',N'')))) * (1.0 / LEN(N'OPENROWSET'))) AS O_NUMBER_OF_OPENROWSET
,CEILING((LEN(SM.definition) - (LEN(REPLACE(SM.definition,N'CONVERT',N'')))) * (1.0 / LEN(N'CONVERT'))) AS O_NUMBER_OF_CONVERT
,CEILING((LEN(SM.definition) - (LEN(REPLACE(SM.definition,N'CAST',N'')))) * (1.0 / LEN(N'CAST'))) AS O_NUMBER_OF_CONVERT
,CEILING((LEN(SM.definition) - (LEN(REPLACE(SM.definition,N'OUTPUT',N'')))) * (1.0 / LEN(N'OUTPUT'))) AS O_NUMBER_OF_OUTPUT
,CEILING((LEN(SM.definition) - (LEN(REPLACE(SM.definition,N'READPAST',N'')))) * (1.0 / LEN(N'READPAST'))) AS O_NUMBER_OF_READPAST
,CEILING((LEN(SM.definition) - (LEN(REPLACE(SM.definition,N'UPDATETEXT',N'')))) * (1.0 / LEN(N'UPDATETEXT'))) AS O_NUMBER_OF_UPDATETEXT
,CEILING((LEN(SM.definition) - (LEN(REPLACE(SM.definition,N'WITH',N'')))) * (1.0 / LEN(N'WITH'))) AS O_NUMBER_OF_WITH
--INTO UTIL.dbo.TBL_CODE_METRICS
FROM sys.all_sql_modules SM
WHERE SM.object_id > 0
AND OBJECT_NAME(SM.object_id) NOT LIKE N'DATAVIEW_%'
ORDER BY O_NAME
;
January 7, 2023 at 2:46 pm
January 7, 2023 at 3:25 pm
EXEC sp_depends 'storedprocedurename'
This is a deprecated procedure.
😎
"This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities instead."
January 8, 2023 at 1:22 am
Jonathan AC Roberts wrote:EXEC sp_depends 'storedprocedurename'
This is a deprecated procedure. 😎 "This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities instead."
Thanks, I didn't notice it had been deprecated. For the recommended methods you can use sys.dm_sql_referenced_entities to find the dependencies of a stored procedure. To do this, you would simply pass the name of the stored procedure as the first argument to the function, like this:
SELECT referencing_schema_name, referencing_entity_name, referencing_id
FROM sys.dm_sql_referenced_entities('dbo.my_stored_procedure', 'OBJECT')
This query would return a row for each object in the current database that references the my_stored_procedure stored procedure in the dbo schema. The referencing_schema_name and referencing_entity_name columns would contain the name of the schema and the name of the object, respectively, and the referencing_id column would contain the object identifier for the object.
You can also use sys.dm_sql_referenced_entities to find the dependencies of other types of objects, such as views, tables, and functions. Simply pass the name of the object as the first argument to the function, and specify the type of object you are interested in as the second argument. For example:
SELECT referencing_schema_name, referencing_entity_name, referencing_id
FROM sys.dm_sql_referenced_entities('dbo.my_table', 'OBJECT')
January 8, 2023 at 3:22 pm
This was removed by the editor as SPAM
January 12, 2023 at 9:05 pm
Thank you all for your replies! I was able to build something using sys.dm_sql_referenced_entities to cycle through all the stored procedures and pull the referenced entities.
The only missing piece I think I found is that when the stored procedure truncates a table, this does not show up in the output of sys.dm_sql_referenced_entities. The table is there, but you can't tell in what way the procedure is referencing it.
It flags a referenced table as is_updated = 1 if the procedure inserts into the table. I'm surprised the same is not true for truncation.
Any ideas on how to get that last piece?
Thanks again!
January 12, 2023 at 9:53 pm
don't you you can find it elsewhere if it does not show on those dm views.
another thing you will not catch with those is tables used within dynamic sql. this may or not be an issue in your case.
January 12, 2023 at 10:16 pm
Yeah, we're not big on dynamic SQL. Maybe the idea is that you usually won't be truncating a table unless you're going to do something else with it.
Thanks anyway!
January 12, 2023 at 10:18 pm
Maybe just scan the procedure text for "~TRUNCATE TABLE~" (where ~ is not A-Z0-9$_@). Then parse out the next word from the text to get the table name.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply