Here’s Accidental DBA Tip. Many times you are looking for where an object such as a table or stored procedure are used. Sometimes you can use dependency DMVs or sp_depends. However, those are not always reliable because the object you may be looking for could have been deleted, or not created in the correct order so the linkage is tracked.
When you can’t find an object the normal way you can use the following query:
SELECT DISTINCT OBJECT_NAME(id)
FROM SYS.SYSCOMMENTS
WHERE text LIKE ‘%SomeDatabaseObjectName%’
Simply replace SomeDatabaseObjectName with the text you are searching. Since text is a field with the actual SQL used to generate an object it can find tables called in stored procedures, views, etc.
You can use sp_msforeachdb to execute this query across all your databases if needed.
I was reminded of this tip by the comment from Dilip in his response to the editorial, “I didn’t expect that”.
Dilip Writes:
In the context of SQL Stored Procedures, we try to minimize this damage by maintaining
an elaborate list of all modules using the stored procedures. Further, I have found some
of the causes as follows:
- Change of Column Names in Select Clause (say EmpNm to Employee_Name).
- Change of Input Parameters:
By just adding a new parameter, you were able to cut down too many comparisons in where clause and thus speeding up the execution of SP but all class methods were not modified to pass this additional parameter and nobody noticed it due to method overloading. - External programs such as Report writers which are outside the perview of class definitions are affected by change of Column Names and also by the parameter passing.
- Removing old SP and replacing it with Newly named SP.
- SP itself getting deprecated in later versions of SQL and only quick patches are applied in emergency.
- My worst fear is SPs which create/Update data.
The list of Blunders is endless. Frankly, many times, Systems people themselves are Non-Systematic.
Leave your comments or send email to btaylor@sswug.org.
Cheers,
Ben