Developing Matt

My Technical Journal

What’s the umbrella for? (part 2)

leave a comment »

Earlier I wrote a post (What’s the umbrella for?) It seems like I’ve written a couple of posts regarding searching in the database for objects with text in them.  Here is the latest way I’ve done it.  This method seems to return more results.  I should script it all out and make it fancy but I needed to get to the end result instead of wasting company time on the process.

So in order to search all objects in a database for the word FADRIZZLE I first get a list of the databases with

Select name from sys.databases order by name

I paste these names in to Excel, which I think has two L’s but I’m not sure.

Then I use this at the top of my spreadsheet

create table #ttDbList (DatabaseName varchar(50), ObjectName varchar(100))

and add the formula against my database name list

="insert into #ttDbList select ‘" & A2 & "’ as db, o.name  from " & A2 & ".sys.sql_modules m  inner join " & A2 & "..sysobjects o on m.object_id=o.id where [definition] like ‘%FADRIZZLE%’"

If it is a 2000 database I use the following formula instead

="Use " & A2 & "; insert into #ttDbList  SELECT ‘" & A2 & "’ as ‘database’, ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE ‘%FADRIZZLE%’ "

Copy and paste the results to your query window.

Now you can run it and it will query all the databases on the server for your special word FADRIZZLE.  You’ll get errors for offline databases and it doesn’t discriminate against comments.

Advertisements

Written by matt

January 15, 2012 at 3:28 pm

Posted in T-Sql

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: