Rahman Agoro, the frontline SQL DBA

Just another WordPress.com weblog

Searching for objects in a database views stored procedures functions etc etc

Posted by rahmanagoro on August 26, 2010


I am often faced with the need to search for objects on a database, for example i want to ensure that there are no other objects referencing the objects, or perhaps I need to change the schema of a table, but want to make sure that I capture and edit all other objects that rely on the object i.e a table.

In one example, I noticed an application was using custom key generation for generating a unique ID, this was causing a deadlock on the database, and I pinned down the problem to a exclusive lock being held on the table during another update, whilst a new key was being generated.

select @myID = max([ID])  + 1 from Company.dbo.tbluser

In order to complete regression testing, I need to ensure that all objects making reference to the table were correctly identified and the code changed.

EXEC sp_MSforeachdb 
'if ''?''  ''master'' and ''?''  ''tempdb''and ''?''  ''model''and ''?''  ''msdb''and ''?''  ''distribution''
begin
select ''Finding the use of tbluser on database '' ?
Use ?

Select 
DISTINCT OBJECT_NAME(A.id), B.type
from syscomments A
join sysobjects B
on A.id = B.id
where A.text like ''%tbluser%''
or text like ''% INSERT tbluser %''
order by OBJECT_NAME(A.id), B.type
end
'

In the example below, I was looking for any object across the database that use the COLLATE or collate hint, as the database server was case sensitive, I have to try both lower and upper case.

EXEC sp_MSforeachdb 
'if ''?''  ''master'' and ''?''  ''tempdb''and ''?''  ''model''and ''?''  ''msdb''and ''?''  ''distribution''
begin
select ''Finding the use of collate on DB '' ?
Use ?

Select 
DISTINCT OBJECT_NAME(A.id), B.type
from syscomments A
join sysobjects B
on A.id = B.id
where A.text like ''%collate%''
or text like ''%%COLLATE%''
order by OBJECT_NAME(A.id), B.type
end
'
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: