Technical Article

Procedure to Search any sql Object

,

So many times I do not remember the name of table or stored procedure I have created, and it becomes time consuming to search object by firing query on sysobjects or search using Enterprise Manager.
It is a simple Stored Prodeure where the Parameter is the Hint of the Object name and Object Type.
It will fetch all the Objects and its description from sysobjects.
Both parameters are optional, so search can be made on the required criteria.

use master go
CREATE PROC dbo.sp_ob
(
@name SYSNAME= NULL,
@type VARCHAR(5)= NULL
)
AS 
/*
Sample Procedure Call (procedure can be called from any database as it is created on Master database with stored procedure name begin with sp_)
EXEC sp_ob 'abc','u
*/BEGIN
DECLARE@nmSYSNAME

SET @nm = LTRIM(RTRIM(ISNULL(@name,'%')))

IF (CHARINDEX('%',@name)=0)
BEGIN
IF (LEFT(@nm,1)<>'%' AND @nm<>'%')
SELECT@nm= '%' + @nm

IF (RIGHT(@nm,1)<>'%' AND @nm<>'%')
SELECT@nm= @nm + '%'
END

SELECT*
FROMsysobjects
WHEREtype = isnull(@type,type)
ANDname LIKE @nm
ORDER BY NAME
END
GO

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating