sql server - How do I find all stored procedures that insert, update, or delete records? -
Text after "div class =" itemprop = "text">
To parse this source, all sproc names are selected to include a list of records that include, update or delete Without it possible? I need to create a TSKL utility script which can do this. Efficiency is not an issue because it will be run only a few times a year (cursor means the cursor is fine). Ideally this script will not include updates for temporary or local variable tables.
I tried to find the following query.
select so.name, so2.name, sd. Process and is_updated = 1 - - proc update table is is_updated from sysobjects so that the inner is included so.id = sd.object_id internal sss.sql_dependencies sd.referenced_major_id = so2.id on sysobjects SO2 where so.xtype = join 'P' Or at least, I think what it means but it produces false negatives.
All non-schema calls bound stored procedures:
varchar @template announcement (maximum) set @template = 'print' '{} OBJECT_NAME' 'EXEC sp_refreshsqlmodule' '{OBJECT_NAME}' 'DECLARE @sql AS varchar (max) SELECT @sql = ISNULL (@sql,' ') + REPLACE (@template, '{obigen}}', quotation (ROUTINE_SCHEMA) + '.' + QUOTENAME (ROUTINE_NAME) FRO M.Information / SCHEMA.ROUTINES where OBJECTPROPERTY (OBJECT_ID (QUOTENAME (ROUTINE_SCHEMA) + '.' + QUOTENAME (ROUTINE_NAME) ), N'SSchemaBound 'Null or ObjectProject (OBJECTED (QUOTENAME (ROUTINE_SCHEMA) +'. '+ QUOTENAM E (ROUTINE_NAME), N'IsSchemaBound ') = 0 Exec (@sql)