Search a value in database – with schema

Here is another version of database scanning query, which includes schema now. This query scans varchar and nvarchar. If you need search for a number, change the types to data types to int, bigint and so on.

Note: This script runs OK in a small to medium database and need to be optimized for large database.

DECLARE @VALUE varchar(30)

SET @VALUE = ‘000134’

DECLARE @ROWID int

DECLARE @PARAMETERS NVARCHAR(100)

DECLARE @SCHEMANAME VARCHAR(50)

DECLARE @TABLENAME VARCHAR(50)

DECLARE @COLUMNNAME VARCHAR(50)

DECLARE @SCRIPT NVARCHAR(MAX)

DECLARE @MATCH BIT

SET @ROWID = 1

SET @PARAMETERS = N’@MATCH bit OUTPUT’

SELECT

    SCHEMA_NAME(T.schema_id) as SCHEMANAME

    ,T.NAME AS TABLENAME

    , C.NAME AS COLUMNNAME

    , ‘IF EXISTS (SELECT ‘ + QUOTENAME(C.NAME) + ‘ FROM ‘ + QUOTENAME(SCHEMA_NAME(T.schema_id)) + ‘.’ + QUOTENAME(T.NAME) + ‘ WHERE ‘ + QUOTENAME(C.NAME) + ‘ = ”’ + CONVERT(VARCHAR,@VALUE) + ”’) SET @MATCH = 1 ELSE SET @MATCH = 0′ AS SCRIPT

    –, ROW_NUMBER() OVER (ORDER BY T.NAME, C.NAME) AS [ROW_NUMBER]

INTO #TABLES

FROM

    sys.tables T

        INNER JOIN sys.columns C

            INNER JOIN sys.types TYPE ON C.SYSTEM_TYPE_ID = TYPE.SYSTEM_TYPE_ID

    ON T.OBJECT_ID = C.OBJECT_ID

WHERE

    T.TYPE_DESC=’USER_TABLE’

    AND TYPE.NAME IN  (‘varchar’, ‘nvarchar’)

ORDER BY

    T.object_id

    , C.NAME

–Select * from #Tables

SELECT * INTO #RESULT FROM #TABLES WHERE 1=2

DECLARE COLUMN_CURSOR CURSOR FAST_FORWARD FOR SELECT SCHEMANAME, TABLENAME, COLUMNNAME, SCRIPT FROM #TABLES

OPEN COLUMN_CURSOR

FETCH NEXT FROM COLUMN_CURSOR INTO @SCHEMANAME, @TABLENAME, @COLUMNNAME,@SCRIPT

WHILE (@@FETCH_STATUS = 0)

BEGIN

    PRINT @SCHEMANAME + ‘.’ + @TABLENAME + ‘,’ + @COLUMNNAME +’,’ + @SCRIPT

    EXEC sp_executesql @SCRIPT,@PARAMETERS,@MATCH = @MATCH OUTPUT
    IF (@MATCH = 1)

        INSERT INTO #RESULT VALUES(@SCHEMANAME, @TABLENAME, @COLUMNNAME,@SCRIPT)

    FETCH NEXT FROM COLUMN_CURSOR INTO @SCHEMANAME, @TABLENAME, @COLUMNNAME,@SCRIPT

END

SELECT * FROM #RESULT

–CLOSE COLUMNE_CURSOR;

DEALLOCATE COLUMN_CURSOR;

DROP TABLE #TABLES

DROP TABLE #RESULT

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s