SQL SERVER "SUPER QUERY" Resurrection...

I just rescued the SUPER QUERY script and re-wrote it in order to list all more complete info on the records found. Basically it lists the pk column from the table containing the searched value, along with the pk value, table name and columnn where the searched value was found.

This "SUPER QUERY" procedure is restricted to a especific schema named 'Global', but you can change that, altering (removing) this line:
AND Schema_name(schema_id) = 'Global'

Doing so, the proc will search all your database on all columns that are CHAR, VARCHAR, NCHAR AND NVARCHAR...

There you go:

/*
SELECT * FROM [Global].[Employee]
EXEC spSearchGlobal @search_string='Domain1'
*/
ALTER PROCEDURE Spsearchglobal(@search_string VARCHAR)
AS
  BEGIN
      DECLARE @table_name  SYSNAME,
              @table_id    INT,
              @column_name SYSNAME,
              @id          VARCHAR(100),
              @sql_string  VARCHAR(2000)

      CREATE TABLE ##superquery
        (
           id     VARCHAR(100),
           pk     VARCHAR(100),
           tabela VARCHAR(100),
           coluna VARCHAR(100)
        )

      DECLARE tables_cur CURSOR FOR
        SELECT name,
               object_id
        FROM   sys.tables
        WHERE  type = 'U'
               AND Schema_name(schema_id) = 'Global'

      OPEN tables_cur

      FETCH next FROM tables_cur INTO @table_name, @table_id

      WHILE ( @@FETCH_STATUS = 0 )
        BEGIN
            DECLARE columns_cur CURSOR FOR
              SELECT name
              FROM   sys.columns
              WHERE  object_id = @table_id
                     AND system_type_id IN ( 167, 175, 231, 239 )

            OPEN columns_cur

            FETCH next FROM columns_cur INTO @column_name

            WHILE ( @@FETCH_STATUS = 0 )
              BEGIN
                  SELECT @id = name
                  FROM   sys.columns
                  WHERE  object_id = Object_id('Global.' + @table_name)
                         AND is_identity = 1

                  SET @sql_string =
                  'INSERT INTO ##SuperQuery(id,pk,tabela,coluna) '
                  + 'SELECT ' + Isnull(@id+',', '') + '''' + @id
                  + ''' ' + ',''Global.' + @table_name + ''' ' +
                  ','''
                  + @column_name + ''' ' + ' FROM Global.'
                  + @table_name + ' WHERE ' + @column_name
                  + ' LIKE ''%' + @search_string + '%'' ';

                  PRINT @sql_string

                  EXECUTE(@sql_string)

                  FETCH next FROM columns_cur INTO @column_name
              END

            CLOSE columns_cur

            DEALLOCATE columns_cur

            FETCH next FROM tables_cur INTO @table_name, @table_id
        END

      CLOSE tables_cur

      DEALLOCATE tables_cur

      SELECT *
      FROM   ##superquery

      DROP TABLE ##superquery
  END;  

Check out my new book on MariaDB http://www.amazon.com/dp/B00MQC06HC