Find occurence of any value in database!

,

Simply pass the proc a value and it will search the entire database reporting every occurence of the word in tables, views, stored procedures, functions, etc...

Best of all, if you create a key stroke shortcut to execute the proc, you can highlight any text in your editor and search for it instantly. And because the proc's only parameter is of type SQL_VARIANT, there's no need to

surround the input parameter with single quotes! Hightlight, click, and find!

This proc does not query contnet of tables - I have another proce for that. Email me if interested.

Please feel free to email me with any questions, thoughts, or comments. And search SQLServerCentral for my other helpful scripts.

CREATE PROC [dbo].[spUtil_Occur] 
(
    @SearchString SQL_VARIANT
)
As
/****************************************************************************
*Procedure Name: spUtil_Occurences
*Author:        Pat Reddy
*Purpose:    This procedure is useful for finding field and/or table references.
                It will search the entire database. Best of all, if you create a key stroke shortcut
                to execute the proc, you can highlight any text in your editor and search for it instantly.
                And because the proc's only parameter is of type SQL_VARIANT, there's no need to 
                surround the input parameter with single quotes!
                
                Feel free to contact me with any questions at all:

                Pat Reddy - Reddy Software Solutions at:        reddys@charter.net
	*By the way, I cannot take full credit for this script as I found it somewhere
	 years ago and merely modified it to my liking.
******************************************************************************/
SET NOCOUNT ON

DECLARE @SQL VARCHAR(1500)
SELECT @SQL = 'SELECT 
 SUBSTRING(SO.name, 1, 40 ) as Object,
 COUNT(*) as Occurences, ' +
 'CASE ' +
 ' WHEN SO.xtype = ''D'' THEN ''Default'' ' +
 ' WHEN SO.xtype = ''F'' THEN ''Foreign Key'' ' +
 ' WHEN SO.xtype = ''P'' THEN ''Stored Procedure'' ' +
 ' WHEN SO.xtype = ''PK'' THEN ''Primary Key'' ' +
 ' WHEN SO.xtype = ''S'' THEN ''System Table'' ' +
 ' WHEN SO.xtype = ''TR'' THEN ''Trigger'' ' +
 ' WHEN SO.xtype = ''V'' THEN ''View'' ' +
 'END AS TYPE ' +
 
 'FROM dbo.syscomments as SC
 JOIN dbo.sysobjects as SO ON SC.id = SO.id ' +
 'WHERE PATINDEX(''%' + CAST(@SearchString AS VARCHAR(100)) + '%'', SC.text ) > 0 ' + 
 'GROUP BY SO.name, SO.xtype ' +
 'UNION ' +
 'SELECT 
 SUBSTRING(SO.name, 1, 40 ) as Object,
 1 as Occurances,
 ''User Table'' as TYPE
 FROM sysobjects as SO
 INNER JOIN syscolumns as SC on SC.id = SO.id
 WHERE SC.name LIKE ''' + CAST(@SearchString AS VARCHAR(100)) + ''' AND SO.xtype =' + '''U'''
 
EXECUTE( @SQL )

RETURN

Rate

3.78 (9)

Share

Share

Rate

3.78 (9)