CREATE PROC SearchAllTables

(

    @SearchStr nvarchar(100)

)

AS

BEGIN

    -- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.

    -- Purpose: To search all columns of all tables for a given search string

    -- Written by: Narayana Vyas Kondreddi

    -- Site: http://vyaskn.tripod.com

    -- Tested on: SQL Server 7.0 and SQL Server 2000

    -- Date modified: 28th July 2002 22:50 GMT

    CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

    SET NOCOUNT ON

    DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)

    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')

                    AND    QUOTENAME(COLUMN_NAME) > @ColumnName

            )

            IF @ColumnName IS NOT NULL

            BEGIN

                INSERT INTO #Results

                EXEC

                (

                    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 

                    FROM ' + @TableName + ' (NOLOCK) ' +

                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2

                )

            END

        END    

    END

    SELECT ColumnName, ColumnValue FROM #Results

END

CREATE Procedure spDeleteRows

    (

    @cTableName varchar(50), /* name of the table where rows are to be deleted */

    @cCriteria nvarchar(1000), /* criteria used to delete the rows required */

    @iRowsAffected int OUTPUT /* number of records affected by the delete */

    )

As

set nocount on

declare     @cTab varchar(255), /* name of the child table */

    @cCol varchar(255), /* name of the linking field on the child table */

    @cRefTab varchar(255), /* name of the parent table */

    @cRefCol varchar(255), /* name of the linking field in the parent table */

    @cFKName varchar(255), /* name of the foreign key */

    @cSQL nvarchar(1000), /* query string passed to the sp_ExecuteSQL procedure */

    @cChildCriteria nvarchar(1000), /* criteria to be used to delete 

                                           records from the child table */

    @iChildRows int /* number of rows deleted from the child table */

/* declare the cursor containing the foreign key constraint information */

DECLARE cFKey CURSOR LOCAL FOR 

SELECT SO1.name AS Tab, 

       SC1.name AS Col, 

       SO2.name AS RefTab, 

       SC2.name AS RefCol, 

       FO.name AS FKName

FROM dbo.sysforeignkeys FK  

INNER JOIN dbo.syscolumns SC1 ON FK.fkeyid = SC1.id 

                              AND FK.fkey = SC1.colid 

INNER JOIN dbo.syscolumns SC2 ON FK.rkeyid = SC2.id 

                              AND FK.rkey = SC2.colid 

INNER JOIN dbo.sysobjects SO1 ON FK.fkeyid = SO1.id 

INNER JOIN dbo.sysobjects SO2 ON FK.rkeyid = SO2.id 

INNER JOIN dbo.sysobjects FO ON FK.constid = FO.id

WHERE SO2.Name = @cTableName

OPEN cFKey

FETCH NEXT FROM cFKey INTO @cTab, @cCol, @cRefTab, @cRefCol, @cFKName

WHILE @@FETCH_STATUS = 0

     BEGIN

    /* build the criteria to delete rows from the child table. As it uses the 

           criteria passed to this procedure, it gets progressively larger with 

           recursive calls */

    SET @cChildCriteria = @cCol + ' in (SELECT [' + @cRefCol + '] FROM [' + 

                              @cRefTab +'] WHERE ' + @cCriteria + ')'

    print 'Deleting records from table ' + @cTab

    /* call this procedure to delete the child rows */

    EXEC spDeleteRows @cTab, @cChildCriteria, @iChildRows OUTPUT

    FETCH NEXT FROM cFKey INTO @cTab, @cCol, @cRefTab, @cRefCol, @cFKName

     END

Close cFKey

DeAllocate cFKey

/* finally delete the rows from this table and display the rows affected  */

SET @cSQL = 'DELETE FROM [' + @cTableName + '] WHERE ' + @cCriteria

print @cSQL

EXEC sp_ExecuteSQL @cSQL

print 'Deleted ' + CONVERT(varchar, @@ROWCOUNT) + ' records from table ' + @cTableName

exec spDeleteRows 'X', 'field1 = ''234''', 0