Search a Value from any Columns in any SQL Tables

Paopatsin/ October 9, 2020/ Database, MS SQL, Technology

Please read our Disclaimer

Works With
-Microsoft SQL Server 2016 (RTM) – 13.0.1601.5 (X64)
-Microsoft SQL Server 2008 (RTM) – 10.0.1600.22 (X64)

Savra, our new database engineer needs to check existence of a certain keyword on the database. So she went for it…

Command:

Steps and Sample Usage:

1. Open and log into your SQL Management Studio
2. Open a New Query Windows or (CTRL+N)
3. Run the following commands: (Copy and Paste the code block to your Query Window)

DECLARE @searchString VARCHAR(100), @tableName SYSNAME, @tableSchema SYSNAME, @columnName SYSNAME, @sqlString VARCHAR(2000)
SET @searchString = 'Yosemite'

DECLARE tables_cur CURSOR FOR
   SELECT TABLE_SCHEMA, TABLE_NAME
   FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_TYPE = 'BASE TABLE'

OPEN tables_cur --table cursor
FETCH NEXT FROM tables_cur INTO @tableSchema, @tableName
WHILE (@@FETCH_STATUS = 0)
BEGIN
   DECLARE columns_cur CURSOR FOR
      SELECT COLUMN_NAME
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE TABLE_SCHEMA = @tableSchema
         AND TABLE_NAME = @tableName
         AND COLLATION_NAME IS NOT NULL -- Only strings have this and they always have it

   OPEN columns_cur --columns cursor
   FETCH NEXT FROM columns_cur INTO @columnName
   WHILE (@@FETCH_STATUS = 0)
   BEGIN
      SET @sqlString = 'IF EXISTS (SELECT * FROM ' + QUOTENAME(@tableSchema)
      + '.' + QUOTENAME(@tableName)
      + ' WHERE ' + QUOTENAME(@columnName) + ' LIKE ''%' + @searchString + '%'')
      PRINT ''' + QUOTENAME(@tableSchema) + '.' + QUOTENAME(@tableName) + ', ' + QUOTENAME(@columnName) + ''''

      EXECUTE(@sqlString)

      FETCH NEXT FROM columns_cur INTO @columnName
   END

   CLOSE columns_cur
   DEALLOCATE columns_cur

   FETCH NEXT FROM tables_cur INTO @tableSchema, @tableName
END
CLOSE tables_cur
DEALLOCATE tables_cur

4. Execute the query by pressing F5.

Use Case:

1. You can use this if you are searching for some values and you don’t know where to begin.
2. Can be used to determine dependencies among database tables.

Caveat:

1. This can indirectly cause a bottleneck on the server as it does it search through all existing tables on a selected database.

Sample Data:
-AdventureWorks2016 Database