SQL : Cauta orice

Lasă un comentariu

7 Iulie 2009 de liviustoica55


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) )
AS
BEGIN
                   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
                  BEGIN
                             SET @Column = ”
                             SET @Table =
                             (SELECT MIN(QUOTENAME(TABLE_SCHEMA) + ‘.’ +
                             QUOTENAME(TABLE_NAME))
                             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)
                BEGIN
                            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
                           BEGIN
                                       INSERT INTO #Results
                                       EXEC (’SELECT ”’ + @Table+ ‘.’ + @Column + ”’,
                                                   LEFT(’ + @Column + ‘, 3630)
                                                   FROM ‘ + @Table+ ‘ (NOLOCK) ‘ +
                                                   ‘ WHERE ‘ + @Column + ‘ LIKE ‘ + @XSearch)
                                                  END
                                      END
                          END

            SELECT ColumnName, ColumnValue FROM #Results
END
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 despre tine sau dă clic pe un icon pentru autentificare:

Logo WordPress.com

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

Poză Twitter

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

Fotografie Facebook

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

Fotografie Google+

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

Conectare la %s

%d blogeri au apreciat asta: