Database Schema Search


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.

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: