SQL : Cauta orice

Let’s say you’re looking for a specific data value in a database, but you don’t know which table or data elements contain the value.  The SQL procedure below will scan all character string data elements in the database for the specified value. 

I. Create the procedure in the required database.  SQL Script below:

CREATE PROC SearchAllTables  (@Search char(100) )
                   CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue char(3630))
                  SET NOCOUNT ON
DECLARE @Table char(256),
@Column char(128),
@XSearch char(110)

                  SET  @Table = ”
                  SET @XSearch = QUOTENAME(’%’ + @Search + ‘%’,””)
                  WHILE @Table IS NOT NULL
                             SET @Column = ”
                             SET @Table =
                             (SELECT MIN(QUOTENAME(TABLE_SCHEMA) + ‘.’ +
                             FROM  INFORMATION_SCHEMA.TABLES
                             WHERE TABLE_TYPE = ‘BASE TABLE’
                             AND QUOTENAME(TABLE_SCHEMA) + ‘.’ +
                             QUOTENAME(TABLE_NAME) > @Table
                             AND OBJECTPROPERTY(OBJECT_ID
                             (QUOTENAME(TABLE_SCHEMA) + ‘.’ +  QUOTENAME (TABLE_NAME)), ‘IsMSShipped’) = 0)
                WHILE (@Table IS NOT NULL) AND (@Column IS NOT NULL)
                            SET @Column =
                           (SELECT MIN(QUOTENAME(COLUMN_NAME))
                           FROM  INFORMATION_SCHEMA.COLUMNS
                           WHERE TABLE_SCHEMA= PARSENAME(@Table, 2)
                           AND TABLE_NAME = PARSENAME(@Table, 1)
                           AND DATA_TYPE IN (’char’, ‘varchar’, ‘nchar’, ‘nvarchar’)
                           AND QUOTENAME(COLUMN_NAME) > @Column)
                           IF @Column IS NOT NULL
                                       INSERT INTO #Results
                                       EXEC (’SELECT ”’ + @Table+ ‘.’ + @Column + ”’,
                                                   LEFT(’ + @Column + ‘, 3630)
                                                   FROM ‘ + @Table+ ‘ (NOLOCK) ‘ +
                                                   ‘ WHERE ‘ + @Column + ‘ LIKE ‘ + @XSearch)

            SELECT ColumnName, ColumnValue FROM #Results
II. To search all columns of all tables in database for the keyword “Computer”

EXEC SearchAllTables ‘value’

This may be a long running process.  The amount of time to execute is dependent on the size of the database you are scanning.


Lasă un răspuns

Completează mai jos detaliile tale sau dă clic pe un icon pentru a te autentifica:

Logo WordPress.com

Comentezi folosind contul tău WordPress.com. Dezautentificare /  Schimbă )

Fotografie Google+

Comentezi folosind contul tău Google+. Dezautentificare /  Schimbă )

Poză Twitter

Comentezi folosind contul tău Twitter. Dezautentificare /  Schimbă )

Fotografie Facebook

Comentezi folosind contul tău Facebook. Dezautentificare /  Schimbă )


Conectare la %s