Do you ever have to check all stored procedures in a database to see if they are interacting with a certain table. Do you have to search all tables, views, stored procedures to see if a certain column of a table is used. All database objects are stored in the system tables just as the tables user created, so we should be able to use a simple query to find it out. But, do you remember all the system tables and column names?
I just finished a project that requires us to change the length of an account number field, due to business change, we have to search all database objects to find references to this field and the following queries came in handy.
Search for Text in Stored Procedures
DECLARE @SEARCH_TERM VARCHAR(100)
SET @SEARCH_TERM = ‘%BANKCARD_999%’
SELECT
ROUTINE_NAME,
ROUTINE_DEFINITION
FROM
INFORMATION_SCHEMA.ROUTINES
WHERE
ROUTINE_DEFINITION LIKE @SEARCH_TERM
ORDER BY
ROUTINE_NAME
Search for Text in All Columns
DECLARE @SEARCH_TERM VARCHAR(100)
SET @SEARCH_TERM = ‘%BANKCARD_999%’
SELECT
TABLE_NAME,
COLUMN_NAME
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
COLUMN_NAME LIKE @SEARCH_TERM
ORDER BY
TABLE_NAME,
COLUMN_NAME
Search for Text in All Database Objects
DECLARE @SEARCH_TERM VARCHAR(100)
SET @SEARCH_TERM = ‘%BANKCARD_999%’
SELECT
o.NAME,
c.TEXT
FROM
sysobjects o
INNER JOIN
syscomments c
ON
o.id = c.id
WHERE
c.TEXT LIKE @SEARCH_TERM
o.TYPE IN (
‘P’, — Stored Procedure
‘V’, — View
‘FN’, — Function,
‘PK’ — Key
)
The queries will help you solve your problems, but there are a lot more than this. Most of the time, when you have to search for something in the whole database, they are not always called the same, so you will need to try these queries for several search terms, and filter out the right ones manually. This is very important and I’m going to tell you a lesson we learned with our project. We actually searched for all the terms that we know are used for the changing account number, and thought we have covered all the basis. However, we didn’t realize that there is another commonly used field called reference number, and it’s created as a combination of the changing account number and a special code, so the reference number needs to be searched and modified as well. That caused a 60-hour working week for everybody on my team.
For whatever reason you came here to read this blog, I hope you can learn from what I didn’t do and save 20 hours for your family or your hobbies.
