Search for objects in a database

USE [master]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
Name : pSearch
Created By : Alberto De Rossi
Date Creation : 2008-03-17
Description : Search all objects or by type in a database with the option of order it by date creation. 
 It shows at the end a counter of the objects that match the search criteria
 It's stored in the master database so it can be available for any user database
Usage: ->Search an object no matter the type, with name starting with cli. No need to use '', ordered by name
     pSearch cli

 ->Search objects no matter the type, with name has the string cli, ordered by name
 pSearch '%cli'

 ->Search objects no matter the type, with name has the string cli, ordered by date creation
 sp_search '%cli', null, 1

 ->Search a stored procedure, with name starting with cli. No need to use '', ordered by name
     pSearch cli, p

 ->Search a stored procedure, with name starting with cli. No need to use '', ordered by date creation
     pSearch cli, p, 1

 ->The Object Types could be anyone of these: 
 C = CHECK constraint 
 D = Default or DEFAULT constraint 
 F = FOREIGN KEY constraint 
 L = Log 
 FN = Scalar function 
 IF = Inlined table-function 
 P = Stored procedure 
 PK = PRIMARY KEY constraint (type is K) 
 RF = Replication filter stored procedure 
 S = System table 
 TF = Table function 
 TR = Trigger
 U = User table
 UQ = UNIQUE constraint (type is K)
 V = View
 X = Extended stored procedure
**/ 
ALTER PROCEDURE [dbo].[sp_Search] 
@ObjectName varchar(50), 
@Type varchar(2) = NULL, 
@SortByDate bit = NULL 
AS 

--Variable for dynamic Query
DECLARE @Query varchar(200) 

--Adds '%' at the end to the name we want to search
SELECT @ObjectName = @ObjectName + '%' 

--Constructs the query dynamically based on the parameters
SET @Query = 'SELECT name AS [Object Name], type AS [Object Type], crdate [Date Creation]' 
SET @Query = @Query + ' FROM sysobjects WHERE name LIKE ''' + @ObjectName + '''' 
 
--If we specify the type, it is added to the where condition
IF @Type IS NOT NULL 
 SET @Query = @Query + ' AND type = ''' + UPPER(@Type) + '''' 
 
--If we specify de SortByDate flag, it is added to the query. If not is ordered by object name
IF @SortByDate = 1 
 SET @Query = @Query + ' ORDER BY crdate DESC' 
ELSE 
 SET @Query = @Query + ' ORDER BY name' 

--Compute clause to count the lines showed 
SET @Query = @Query + ' COMPUTE COUNT(name)' 
 
EXEC (@Query) 
--print @Query