In SQL Server 2005 the T-SQL meta data function OBJECT_DEFINITION was introduced.  I like to use this command to search stored procedures for string values, such as table or column names.

The BoL page is located at http://msdn.microsoft.com/en-us/library/ms176090.aspx.

The following queries will return the definitions for VIEWS, UDFs and STORED PROCEDURES:

USE AdventureWorks
GO

SELECT    OBJECT_DEFINITION(OBJECT_ID(N'Purchasing.vVendor'))
UNION
SELECT    OBJECT_DEFINITION(OBJECT_ID(N'HumanResources.uspUpdateEmployeeHireInfo'))
UNION
SELECT    OBJECT_DEFINITION(OBJECT_ID(N'dbo.ufnGetContactInformation'));

USE master;
GO

SELECT    OBJECT_DEFINITION(OBJECT_ID(N'INFORMATION_SCHEMA.TABLE_CONSTRAINTS'))
UNION
SELECT    OBJECT_DEFINITION(OBJECT_ID(N'sys.sp_add_log_file_recover_suspect_db'));

The only reason I UNIONed the queries together was to create a more compact result set:

In theory you could paste the result set of one of the object definitions into a QUERY window and see how the object is created.  In practice, because the output does not recognize line feeds / carriage returns, you would need to add your own CRs to get around the in-line comments.

This function should be used with the understanding that just because an object definition returns NULL does not mean that the object may not exist.  The BoL indicate that you simply may not have permissions to read that object.  In his book Microsoft SQL Server 2008: T-SQL Fundamentals, T-SQL guru (I know, too oft used, but in this case it really applies) Itzik Ben-Gan shows us that an encrypted VIEW will always return a NULL value.  Lesson Learned: Do not use the OBJECT_DEFINITION meta function to test if an object EXISTS.

SQL Server MVP Dave Pinal shows us in his blog how to use the OBJECT_DEFINITON function to search through sys.procedures (stored procedures) for a certain text string.  I have found this very handy to search for references to tables and columns, for example, in production stored procedures.

USE AdventureWorks
GO
--Searching for Empoloyee table
SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%Employee%'

Stay curious; keep learning…

Dave