Is it possible, without parsing source, to select a list of all sproc names that insert, update, or delete records? I need to create a TSQL utility script that will do this. Efficiency is not an issue because it will be run only a few times a year (Curse'rs I mean Cursors are ok). Ideally this script would not include updates to temp or local variable tables.
I tried the following query found on SO Question.
SELECT
so.name,
so2.name,
sd.is_updated
from sysobjects so
inner join sys.sql_dependencies sd on so.id = sd.object_id
inner join sysobjects so2 on sd.referenced_major_id = so2.id
where so.xtype = 'p' -- procedure
and
is_updated = 1 -- proc updates table, or at least, I think that's what this means
But it produces false negatives.
Call sp_refreshsqlmodule on all non-schema bound stored procedures:
DECLARE @template AS varchar(max)
SET @template = 'PRINT ''{OBJECT_NAME}''
EXEC sp_refreshsqlmodule ''{OBJECT_NAME}''
'
DECLARE @sql AS varchar(max)
SELECT @sql = ISNULL(@sql, '') + REPLACE(@template, '{OBJECT_NAME}',
QUOTENAME(ROUTINE_SCHEMA) + '.'
+ QUOTENAME(ROUTINE_NAME))
FROM INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) + '.'
+ QUOTENAME(ROUTINE_NAME)),
N'IsSchemaBound') IS NULL
OR OBJECTPROPERTY(OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) + '.'
+ QUOTENAME(ROUTINE_NAME)),
N'IsSchemaBound') = 0
EXEC (
@sql
)
That is the problem with sys.sql_dependencies. SQL cannot accurately track dependencies in stored procedures (there are sound reasons why it can't, but lets no go there now). That is without even considering dynamic SQL or CLR procedures.
Visual Studio Database Edition has some better capabilities, but it can track dependencies in scipts, not in a live database. It can though reverse engineer live databases into scripts and analyse the resulted scripts, to some better accuracy than sys.sql_dependencies can. It cannot handle dynamic SQL.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With