SQL Server 2005 • Search Across a Single Database

Listing 1. dba_searchDB searches the named database (given as the parameter @DBName) for the supplied string (given as the parameter @StringToFind). Setting the Transaction Isolation level to "read uncommitted" ensures that this utility runs smoothly. You validate the supplied string to ensure it has the correct format for the search. You should create this utility in the master database.

CREATE PROC DBA_SEARCHDB
        @STRINGTOFIND NVARCHAR(1000)
        ,@DBNAME NVARCHAR(255)
AS

-- Work variable to hold dynamic SQL.
DECLARE @SQL NVARCHAR(4000)

-- Do not lock anything, and do not get 
-- held up by any locks. 
SET TRANSACTION ISOLATION LEVEL 
        READ UNCOMMITTED

--      
-- Ensure @STRINGTOFIND has at least one 
-- character wrapped by % symbols.
--

-- Ensure @STRINGTOFIND has at least 3 
-- characters
--  (one character and two '%' symbols).
IF LEN(@STRINGTOFIND) < 3
BEGIN
RAISERROR('There should be at least one character enclosed with percentage symbols.', 16, 1)
        RETURN
END

-- Ensure @STRINGTOFIND starts with a %.
IF SUBSTRING(@STRINGTOFIND, 1, 1) <> '%'
BEGIN
        RAISERROR('String should start with
        a percentage symbol.', 16, 1)
        RETURN
END

-- Ensure @STRINGTOFIND ends with a %.
IF SUBSTRING(@STRINGTOFIND, 
                LEN(@STRINGTOFIND), 1) <> '%'
BEGIN
        RAISERROR('String should end with a 
        percentage symbol.', 16, 1)
        RETURN
END

-- Build @SQL to look for @STRINGTOFIND 
-- inside any table definitions.
SET @SQL = ' SELECT  ' +  '''' + @DBNAME + '''' 
        + ' AS [DATABASE NAME]' + ','  +  @DBNAME 
        + '.DBO.SYSOBJECTS.NAME AS
                 [OBJECT NAME],  ' 
        + '[OBJECT TYPE] = ' + '''' + 'USER TABLE' 
        + '''' + ' FROM  ' +  @DBNAME 
        + '.DBO.SYSOBJECTS INNER JOIN '
        + @DBNAME + '.DBO.SYSCOLUMNS ON '  
        + @DBNAME + '.DBO.SYSOBJECTS.ID =  ' 
        + @DBNAME + '.DBO.SYSCOLUMNS.ID' 
        + ' WHERE  ( ' + @DBNAME 
        + '.DBO.SYSCOLUMNS.NAME LIKE ' 
        + '''' + @STRINGTOFIND + '''' + ')'
        + ' AND ' + @DBNAME 
        + '.DBO.SYSOBJECTS.XTYPE = '  
        + '''' + 'U' + ''''

-- Set UNION command
-- (want to combine the result).
SET @SQL = @SQL + '  UNION '  

-- Build @SQL to look for @STRINGTOFIND
--  inside any view, rule, default, trigger, 
-- check constraint, default constraint,
-- and stored procedure definitions.
SET @SQL = @SQL 
        + ' SELECT  ' +  '''' + @DBNAME + '''' 
        + ' AS [DATABASE NAME]' + ',' 
        +  @DBNAME 
        + '.DBO.SYSOBJECTS.NAME AS
                 [OBJECT NAME], ' 
        + '[OBJECT TYPE] = CASE ' 
        + @DBNAME 
        + '.DBO.SYSOBJECTS.XTYPE'  
        + ' WHEN ' + '''' + 'C' + '''' + ' THEN ' 
                + '''' + 'CHECK CONSTRAINT' + ''''
        + ' WHEN ' + '''' + 'D' + '''' + ' THEN ' + ''''
                + 'DEFAULT OR DEFAULT CONSTRAINT' 
                + ''''
        + ' WHEN ' + '''' + 'F' + '''' + ' THEN ' 
                + '''' + 'FOREIGN KEY CONSTRAINT' 
                + ''''
        + ' WHEN ' + '''' + 'L' + '''' + ' THEN ' 
                + '''' + 'LOG ' + ''''
        + ' WHEN ' + '''' + 'FN' + '''' + ' THEN ' 
                + '''' + 'SCALAR FUNCTION' + ''''
        + ' WHEN ' + '''' + 'IF' + '''' + ' THEN ' 
                + '''' + 'INLINED TABLE-FUNCTION'  + ''''
        + ' WHEN ' + '''' + 'P' + '''' + ' THEN ' 
                + '''' + 'STORED PROCEDURE' + ''''
        + ' WHEN ' + '''' + 'PK' + '''' + ' THEN ' 
                + '''' + 'PPRIMARY KEY CONSTRAINT' + '''' 
        + ' WHEN ' + '''' + 'RF' + '''' + ' THEN ' 
        + '''' + 'REPLICATION FILTER STORED PROC' 
                + ''''
        + ' WHEN ' + '''' + 'S' + '''' + ' THEN ' 
                + '''' + 'SYSTEM TABLE' + ''''
        + ' WHEN ' + '''' + 'TF' + '''' + ' THEN ' 
                + '''' + 'TABLE FUNCTION' + ''''
        + ' WHEN ' + '''' + 'TR' + '''' + ' THEN ' 
                + '''' + 'TRIGGER' + ''''
        + ' WHEN ' + '''' + 'U' + '''' + ' THEN ' 
                + '''' + 'USER TABLE' + ''''
        + ' WHEN ' + '''' + 'UQ' + '''' + ' THEN ' 
                + '''' + 'UNIQUE CONSTRAINT' + ''''
        + ' WHEN ' + '''' + 'V' + '''' + ' THEN ' 
                + '''' + 'VIEW ' + ''''
        + ' WHEN ' + '''' + 'X' + '''' + ' THEN ' 
                + '''' + 'EXTENDED STORED PROCEDURE' 
                        + ''''
        + ' ELSE ' + '''' + 'OTHER' + '''' 
        + ' END ' 
        + ' FROM  ' +  @DBNAME 
        + '.DBO.SYSOBJECTS INNER JOIN '
        + @DBNAME + '.DBO.SYSCOMMENTS ON '  
        + @DBNAME + '.DBO.SYSOBJECTS.ID =  ' 
        + @DBNAME + '.DBO.SYSCOMMENTS.ID' 
        + ' WHERE  ( '  + @DBNAME 
        + '.DBO.SYSCOMMENTS.TEXT LIKE ' 
        + '''' + @STRINGTOFIND + '''' + ')'

-- Order by object type, then object name
+ ' ORDER BY [OBJECT TYPE], [OBJECT NAME]'

PRINT @SQL -- USEFUL FOR DEBUGGING.

-- Find the passed string.
EXECUTE SP_EXECUTESQL @SQL
GO