February 16, 2010 at 6:08 am
Hi,
For example, we have a database with 500 tables, 3000 stored procedures, 120 triggers...
We need to find if some of the stored procedures updates the PK fields of tables, how this can be found ?
Thanks.
Victor S.
February 16, 2010 at 11:38 am
Ok, thanks for the script, it's only a start but i need something a little bit else.
Let's say for example that i have a table named Orders with fields OrderId that is the primary key and much more fields that aren't now so important to write them down, i need to find what are the procedures (if any) that updates (...by mistake) the OrderId field himself. As you know, primary keys should not be changed with other value inside...
Thanks a lot.
Victor
February 18, 2010 at 7:45 am
I was reading through the SS 2008 System View poster this morning (I know - I should get out more) when I caught sight of the table 'sys.identity_columns', and had a thought.
I tried modifying the code I posted earlier to join sys.sql_modules to this table, like this:
WITH PKeys(PKeyName, PKeyTable)
AS
(
SELECT name, OBJECT_NAME(object_id) FROM sys.identity_columns
)
select OBJECT_NAME(sm.object_id), Pkeyname, sm.definition
from sys.sql_modules sm
INNER JOIN PKeys ON sm.definition LIKE '%''' + PKeys.Pkeyname + '''%'
AND sm.definition like '%''' + PKeys.PkeyTable + '''%'
It does appear to work, but understandably it goes quiet for a very long time while it runs.
So I resorted to a cursor, so at least I could see some sort of progress:
DECLARE @TableName VarChar(max), @IDName VarChar(max), @SQLString VarChar(max)
DECLARE CCUR CURSOR FOR
select object_name(object_id), name
from sys.identity_columns
OPEN CCUR
FETCH NEXT FROM CCUR INTO @TableName, @IDName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQLString = 'select distinct [Routine] = object_name(object_id), [Table Name] = ' + '''' + @TableName + '''' + ', [ID Name] = ' + '''' + @IDName + '''' +
' from sys.sql_modules where definition like ' + '''%' + @TableName + '%''' + ' and definition like ' + '''%' + @IDName + '%'''
--print @SQLString
EXEC (@SQLString)
FETCH NEXT FROM CCUR INTO @TableName, @IDName
END
CLOSE CCUR
DEALLOCATE CCUR
This could still do with further modification, as it lists Views which of course won't be of interest in this case.
Of course it just lists all routines that mention the key fields and not just those that update them, but that is well beyond the ability of my little grey cells.
It was an interesting exercise and I'd like to see someone improve on it, as I'm less than happy with it. But it is another starting point.
BrainDonor
February 18, 2010 at 8:11 am
This is a very problematic script to write. I think that at best you can write a SQL statement that will bring you closer. For example:
Select object_name(object_id)
From sys.sql_modules
Where definition like ‘%update%WriteTableNameHere%set%WriteColumnNameHere’
This will show you all stored procedures that have an update statement and after the key word update, the code referenced the table and it’s primary key. This of course doesn’t have to be because it updated the table’s primary key, because of the wild card, but you have to use the wild cards. It also won’t show you any update that is done through a view that reference this table. Since this is a SQL Server 2008 forum, I guess that this is what you are using. In that case if you have an enterprise edition, you’ll might consider using audit, to check if there are any updates on the primary key. Few important advantages of audit are:
1) It recognize all kind of updates even if the update is done with a view and not directly on the table.
2) If the update statement is done by SSMS and not by the stored procedures and triggers, you’ll still be able to catch it.
Adi
P.S - Start coming to the SQL Server user group. I havent seen you there for a long time:-)
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 18, 2010 at 8:28 am
Adi Cohn-120898 (2/18/2010)
This is a very problematic script to write. I think that at best you can write a SQL statement that will bring you closer. For example:
Select object_name(object_id)
From sys.sql_modules
Where definition like ‘%update%WriteTableNameHere%set%WriteColumnNameHere’
I never realised that you could use several wildcard searches in one string - much neater and it narrows down the candidates substantially.
BrainDonor
February 19, 2010 at 2:33 pm
Victor Shahar (2/16/2010)
As you know, primary keys should not be changed with other value inside...
There is nothing inherently wrong with updating a key value. Maybe it's a bug for your purposes but in other circumstances it might be the right thing to do.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply