Execute the following SQL Server T-SQL script in SSMS Query Editor to create a keyword search procedure for stored procedures, functions and views:

USE AdventureWorks2008;

GO

-- T-SQL create stored procedure - sproc

-- SQL search programming objects for keyword - database metadata

CREATE PROCEDURE sprocSearchProgrammingObjects(

                @KeyWord CHAR(50))

AS

  BEGIN

    DECLARE  @SQLCommand NVARCHAR(MAX)

    SELECT @SQLCommand = 'SELECT ObjectName=o.Name,' +

                ' o.Type, CreateDate=o.crdate ' + CHAR(13) +

                'FROM sys.syscomments c' + CHAR(13) +

                'JOIN sys.sysobjects o' + CHAR(13) +

                'ON c.ID = o.ID' + CHAR(13) +

                'WHERE c.text LIKE ''%' + rtrim(@KeyWord) +

                '%''' + CHAR(13) + 'ORDER BY Type, ObjectName'

    PRINT @SQLCommand

    DECLARE  @Result  TABLE(

                            ObjectName VARCHAR(50),

                            [Type]     CHAR(10),

                            CreateDate DATETIME

                            )

    INSERT @Result

    EXEC( @SQLCommand)

    SELECT   *

    FROM     @Result

    ORDER BY [Type]

    RETURN

  END

GO

-- Execute stored procedure - test stored procedure

EXEC sprocSearchProgrammingObjects   'Vendor'

/*

ObjectName                    Type        CreateDate

ufnGetContactInformation      TF          2009-08-19 04:29:19.443

dVendor                       TR          2009-08-19 04:29:19.050

vVendorWithAddresses          V           2009-08-19 04:29:19.420

vVendorWithContacts           V           2009-08-19 04:29:19.407

*/

EXEC sprocSearchProgrammingObjects    'SalesOrderHeader'

/*

ObjectName                          Type        CreateDate

iduSalesOrderDetail                 TR          2009-08-19 04:29:19.030

uSalesOrderHeader                   TR          2009-08-19 04:29:19.043

vSalesPersonSalesByFiscalYears      V           2009-08-19 04:29:19.350

*/