DECLARE @vcSearch VARCHAR(100)
DECLARE @vcObjectName VARCHAR(100)
DECLARE @vcObjectType VARCHAR(100)
SELECT
@vcSearch = ''
,@vcObjectName = ''
,@vcObjectType = ''
SELECT DISTINCT
so.Name,
CASE WHEN so.xtype = 'p' THEN 'PROCEDURE'
WHEN so.xtype = 'u' THEN 'TABLE'
WHEN so.xtype = 'tr' THEN 'TRIGGER'
WHEN so.xtype = 'fn' THEN 'FUNCTION'
WHEN so.xtype = 'v' THEN 'VIEW'
ELSE xtype
END as [Object Type],
sc.text
FROM sysobjects so
LEFT JOIN sysComments sc ON so.id = sc.id
WHERE
(sc.text LIKE '%' + @vcSearch + '%' OR @vcSearch='')
AND
(so.Name LIKE '%' + @vcObjectName + '%')
AND
(so.xType = @vcObjectType or @vcObjectType = '')
ORDER BY so.Name
Please note what Jeff said though!!!!!
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
SQL-4-Life