Posted By

Eloi on 07/09/10

Tagged

Versions (?)

Last Edited at 07/09/10 09:47pm

Statistics

Viewed 754 times

Favorited by 1 user(s)

Related snippets

SQL Searh in ALL DataBase

/ Published in: SQL

Stored Procedure for search a string in all fields of all tables of one DataBase

Expand | Embed | Plain Text

Copy this code and paste it in your HTML

CREATE Proc SEARCH
(
    @SearchStr nVarChar(100)
)
AS
BEGIN
 
    CREATE TABLE #Results ( TableName nVarChar(370), 
                            ColumnName nVarChar(370), 
                            ColumnValue nVarChar(3630))
 
    SET NoCount ON
 
    DECLARE @TableName nVarChar(256), 
            @ColumnName nVarChar(128),
            @SearchStr2 nVarChar(110),
            @ColumnValue nVarChar(4000)
 
    SET    @TableName   =   ''
    SET    @SearchStr2  =   QuoteName('%' + @SearchStr + '%','''')
 
    While @TableName IS NOT NULL
    BEGIN
        SET @ColumnName = ''
        SET @TableName  = 
        (
            SELECT  MIN(QuoteName(TABLE_SCHEMA) + '.' + QuoteName(TABLE_NAME))
            FROM    INFORMATION_SCHEMA.Tables
            WHERE   Table_TYPE = 'BASE Table'
            AND     QuoteName(TABLE_SCHEMA) + '.' + QuoteName(TABLE_NAME) > @TableName
            AND     ObjectProperty  (
                                        Object_Id(QuoteName(TABLE_SCHEMA) + '.' + QuoteName(TABLE_NAME)),
                                        'IsMSShipped'
                                     ) = 0
        )
 
        While (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
        BEGIN
            SET @ColumnName =
            (
                SELECT  MIN(QuoteName(COLUMN_NAME))
                FROM    INFORMATION_SCHEMA.COLUMNS
                WHERE   TABLE_SCHEMA    =    PARSENAME(@TableName, 2)
                AND     TABLE_NAME      =    PARSENAME(@TableName, 1)
                AND     DATA_TYPE       IN   ('char', 'VarChar', 'nchar', 'nVarChar', 'int', 'decimal')
                AND     QuoteName(COLUMN_NAME) > @ColumnName
            )
 
            IF @ColumnName IS NOT NULL
            BEGIN
                INSERT INTO #Results
                EXEC
                (
                    'Select ''' + @TableName + ''', ' +
                    '''' + @TableName + '.' + @ColumnName + ''', ' +
                    'LEFT(' + @ColumnName + ', 3630) From ' + @TableName + ' (NOLOCK) Where ' + @ColumnName + ' LIKE ' + @SearchStr2
                )
            END
        END   
    END
 
    SELECT    TableName, ColumnName, ColumnValue
    FROM    #Results
 
END

Report this snippet Tweet

Comments

Subscribe to comments

Comment:

You need to login to post a comment.