0

Is there a way to search for a value in the entire database without quoting each table that it might or might not be in?

For example: 

select all.tables from db
where all.tables = value.x
Sid M
  • 4,354
  • 4
  • 30
  • 50
Angie
  • 119
  • 2
  • 10
  • There might be a way in SQL to do this.. But how about dumping all your tables, and just use notepad to search? Is it sufficient? – rcs May 30 '14 at 08:54
  • Depending on which database you are on, it might be possible to do this. Almost all databases have metadata tables which store the table names and column names. You can write a procedure which will select the tablenames/columnnames etc and then loop a select through each of them – visakh May 30 '14 at 08:56
  • This seems like a duplicate of [this](http://stackoverflow.com/questions/436351/how-do-i-find-a-value-anywhere-in-a-sql-server-database) and [this](http://stackoverflow.com/questions/1796506/search-all-tables-all-columns-for-a-specific-value-sql-server) question, at least for SQL Server. – Blaž Dakskobler May 30 '14 at 08:57
  • With Mysql, I used to do it with PHPmyAdmin, who would just build all the necessary queries automatically. – mika May 30 '14 at 09:32

1 Answers1

0

DECLARE @SearchStr nvarchar(100) SET @SearchStr = 'tablename'

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', 'int', 'decimal')
                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

DROP TABLE #Results

Angie
  • 119
  • 2
  • 10