Virus No 1
Here is your Solution
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
Source: http://www.sqlservercentral.com/scripts/Search/61508/
