h1

How to find all places where object (Table/view/Text etc..) is used

02/16/2009

Recently I have one change in a table schema. To make this change I need to be sure that it should not raise an error. To make suare that I need to get all the places where I used this table in whole DB and my DB is too big.

So I triued for that and come with the solution, that I need to share with all of you.

I wrote one query which will give me all the objects in which i have used the TEXT that i want to search.

Query for SQL 2005:

SELECT DISTINCT o.name
FROM sysobjects o
INNER JOIN syscomments c ON c.Id = o.Id
WHERE category = 0 AND c.text like ‘%’ + ‘<TableName>’ + ‘%’
ORDER BY o.name

So it save my life. I found all the objects where I used that table and fix if needed. Let me know if this can help you !

Advertisements

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

%d bloggers like this: