question

Bhupendra99 avatar image
Bhupendra99 asked

Search sp's upadating a particular table

I have a huge database with 100's of tables and stored procedures. Using SQL Server 2008, how can I get a list of stored procedures that are doing an update operation on a given table. Please note that I can't search for a string or dump entire Db contents in an text file as a table can also be updated by using an alias name as Update J from TableToUpdate J
search
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Im not sure there is an easy way to do this with TSQL. However Redgate have a tool which might help. SQL Dependency Tracker http://www.red-gate.com/products/sql-development/sql-dependency-tracker/
0 Likes 0 ·
It will give me the dependency list I want only those stored procedure which were updating a Table there are more than 2000 plus sp which are dependent on this table that's why I want only a list of sp's updating a table
0 Likes 0 ·
This site functions according to votes. For all helpful answers below, show this by clicking on the thumbs up next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer.
0 Likes 0 ·
emil87b avatar image
emil87b answered
This tool should do the job: http://sqlcodeguard.com/ ![alt text][1] [1]: /storage/temp/1699-untitled.jpg

untitled.jpg (138.9 KiB)
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I think I'll give it a spin. Thanks for the tip!
1 Like 1 ·
That looks really interesting. Do you have any experience of using it?
0 Likes 0 ·
using it is straight forward. it's usefull when you have a lot lines of code to help you find obvious mistakes or unused variables etc. the dependency builder is a nice thing to have as well as you see from my screen shot.
0 Likes 0 ·
marcingminski avatar image
marcingminski answered
how about SELECT o.name, m.[definition] FROM sys.sql_modules m INNER JOIN sys.objects o ON m.object_id = o.object_id WHERE m.[definition] LIKE '%UPDATE%' AND o.type_desc = 'SQL_STORED_PROCEDURE'
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Sorry just noted you cannot search for a string!
0 Likes 0 ·
getting error while executing the script given by emil87b Msg 537, Level 16, State 5, Line 15 Invalid length parameter passed to the LEFT or SUBSTRING function. can u help emil87b
0 Likes 0 ·
iainrobertson avatar image
iainrobertson answered
I agree with SQLShark - I don't think that there is an easy way to do this. If you are able to, the tools route looks like your best option. But, I like a puzzle :) This should get you somewhere close. It might not be comprehensive though - there are a lot of alternatives for laying out scripts. One that springs to mind immediately is table aliases. This script expects *TableName alias*. You might have *TableName as alias*, or even *TableName 'alias'* (heaven forbid). But it's tweakable to handle different circumstances. You'd just need to play about with the replace inputs. Note that I've laid this out in a step by step way to make it a bit simpler to understand. It would be entirely possible to rewrite this using fewer steps. Here's the script: Note: edited in response to comment from OP. ------------------- -- set up test data ------------------- create table dbo.MyUpdatedTable (Id int) go create procedure dbo.DirectUpdateTest as begin update dbo.MyUpdatedTable set Id = 1 end go create procedure dbo.AliasUpdateTest as begin update mut set Id = 1 from dbo.MyUpdatedTable mut end go create procedure dbo.AliasUpdateTestNoBlock as update mut set Id = 1 from dbo.MyUpdatedTable mut go -- find updates declare @TableName varchar(200) set @TableName = 'dbo.MyUpdatedTable' ------------------------------------- -- find direct updates - the easy bit ------------------------------------- -- edit: need to use vertical bars here too, to avoid returning MyUpdatedTable1, MyUpdatedTable2 etc -- eliminate whitespace characters and square brackets to avoid problems with alternative layouts -- using vertical bars allows us to break the input to words ; with cte as ( select object_id , SubstitutedDefinition = replace(replace(replace(replace(replace(sm.definition,' ','|'),char(13),'|'),char(10),'|'),'[',''),']','') from sys.sql_modules sm ) -- reduce multiple vertical bar instances to singles -- assume maximum of 5 consecutive bars to substitute , cte2 as ( select object_id , SingleBarredDefinition = replace(replace(replace(replace(SubstitutedDefinition,'|||||','|'),'||||','|'),'|||','|'),'||','|') from cte ) select SchemaName = s.Name , ProcedureName = p.Name from cte2 sm join sys.procedures p on sm.object_id = p.object_id join sys.schemas s on p.schema_id = s.schema_id where charindex('update|' + @TableName + '|', SingleBarredDefinition) 0 go ----------------------------------------- -- find aliased versions - a bit trickier ----------------------------------------- -- again, use substitution of whitespace characters -- we then look for the pattern || and use this to derive the alias -- edit added trailing vertical bar to allow for procedure defintions without begin / end block declare @TableName varchar(200) set @TableName = 'dbo.MyUpdatedTable' ; with cte as ( select object_id , SubstitutedDefinition = replace(replace(replace(replace(replace(sm.definition,' ','|'),char(13),'|'),char(10),'|'),'[',''),']','') from sys.sql_modules sm ) -- reduce multiple vertical bar instances to singles -- assume maximum of 5 consecutive bars to substitute , cte2 as ( select object_id , SingleBarredDefinition = replace(replace(replace(replace(SubstitutedDefinition,'|||||','|'),'||||','|'),'|||','|'),'||','|') + '|' from cte ) -- isolate the possible matches and calculate start position of alias , cte3 as ( select * , AliasStart = charindex('from|' + @TableName + '|',SingleBarredDefinition) + len(@TableName) + 6 from cte2 where charindex('from|' + @TableName + '|',SingleBarredDefinition) 0 ) -- calculate end position of alias , cte4 as ( select * , AliasEnd = charindex('|',SingleBarredDefinition,AliasStart) - 1 from cte3 ) -- extract the actual alias value , cte5 as ( select * , AliasName = substring(SingleBarredDefinition,AliasStart,AliasEnd - AliasStart + 1) from cte4 ) -- return the result select SchemaName = s.Name , ProcedureName = p.Name from cte5 c join sys.procedures p on c.object_id = p.object_id join sys.schemas s on p.schema_id = s.schema_id where charindex('update|' + AliasName + '|',SingleBarredDefinition) 0 ---------- -- tidy up ---------- drop procedure dbo.DirectUpdateTest drop procedure dbo.AliasUpdateTest drop procedure dbo.AliasUpdateTestNoBlock drop table dbo.MyUpdatedTable
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

In response to comment below: getting error while executing the script given by emil87b Msg 537, Level 16, State 5, Line 15 Invalid length parameter passed to the LEFT or SUBSTRING function. can u help emil87b I suspect that this may be a result of procedure definitions without a begin / end block, causing the substituted string to not terminate with a vertical bar. I've edited the script. I hope that emil87b doesn't mind :D
1 Like 1 ·
"Ohh... Yes of course... help yourself and edit my script" - said emil muttering quietly words of indignation. :)
1 Like 1 ·
looks hmm... intense... I will give a thumbs up for that :)
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.