Search for objects in a database

,

I use this short named procedure regularly to find different objects on different database. It's very simple and very practical. Of curse you need to have an Idea of whats on the database. Creating it on the master databse makes it available for searchs on any database in the server. It constructs dynamycally a query depending on the prameters u use. Feel free to make any changes.

Bye

Alberto De Rossi

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

Rate

2.75 (4)

Share

Share

Rate

2.75 (4)