MSSQL - syscomments

Sometimes in SQL I am looking for a procedure that references a certain text string or table or column.

The best script that I have come up when looking for procedural string references is a simple select against the syscomments and sysobjects tables.

Syntax
Select a.name from sysobjects a
join syscomments b
on
a.id = b.id
and a.xtype = 'P' 
where b.text like '%mytextstring%'

Examlple
Select a.name from sysobjects a
join syscomments b
on
a.id = b.id
and a.xtype = 'P'
where b.text  like '%xp_sendmail%'

The example will look for any procedure within the current database that has the text string of xp_sendmail in it.  Once it returns the object names then I can review the procedural code for those procedures that returned.

I find this very useful when I am trying to find procedures that use "xp_sendmail" or other items like referencing a certain value or hard coded items from old code.  I also find this very useful when I need to write a new procedure for some reason or another and there may already be a similar procedure that I can copy part of the code from.  Especially if you are new to an organization this is very useful as a lot of times you don't always know what all of the procedures do, this is a handy way to look for specific key words and values to see if a procedure is similar in nature to what you are trying to accomplish.

sp_help tablename

Using the following syntax will provide details on a table.

Syntax:  sp_help 'tablename'

Example: sp_help 'authors'


This system procedure is most useful in obtaining details of columns within a table.  Their names and datatypes.

MSSQL - Use database

Query analyzer.
The first step in writing any query is ensuring you are in the right database.  If you are connected to the correct server and know which database you want to connect to use the following statement to ensure you are in the correct database.

Syntax:
Use databasename
Go

Example
Use pubs
Go

Execute the statement by hitting F5 or the execute button in the query analyzer window. After executing the statement you should see the database name in the explorer window change to the database you keyed. 

Give it a try.