Other: Global SQL Search

Other: Global SQL Search

The following query will create a stored procedure named SearchAllTables which will let you search all tables in the database for a value. 

It can be used the following ways:
  1. --Search for a column that exactly contains 'Test':
  2. exec SearchAllTables 'Test'
  3. --Search for a column that exactly contains 'Test' anywhere in it:
  4. exec SearchAllTables '%Test%'

Script:


  2. CREATE OR ALTER PROC SearchAllTables
  3. (
  4.     @SearchStr nvarchar(100)
  5. )
  6. AS BEGIN
  7.  
  8.     CREATE TABLE #Results (
  9.                         TableName                  nvarchar(70),
  10.                         ColumnName              nvarchar(370),
  11.                         ColumnValue              nvarchar(3630)
  12.             )
  13.  
  14.     SET NOCOUNT ON
  15.  
  16.     DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
  17.     SET @TableName = ''
  18.     SET @SearchStr2 = QUOTENAME(@SearchStr, '''')
  19.  
  20.     WHILE @TableName IS NOT NULL BEGIN
  21.         SET @ColumnName = ''
  22.         SET @TableName =  (
  23.             SELECT
  24.                                                 MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
  25.             FROM
  26.                                                 INFORMATION_SCHEMA.TABLES
  27.             WHERE 1=1
  28.                                                 AND TABLE_TYPE = 'BASE TABLE'
  29.                 AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
  30.                 AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
  31.         )
  32.  
  33.         WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL) BEGIN
  34.             SET @ColumnName = (
  35.                 SELECT
  36.                                                             MIN(QUOTENAME(COLUMN_NAME))
  37.                 FROM
  38.                                                             INFORMATION_SCHEMA.COLUMNS
  39.                 WHERE 1=1
  40.                                                             AND TABLE_SCHEMA = PARSENAME(@TableName, 2)
  41.                     AND TABLE_NAME    = PARSENAME(@TableName, 1)
  42.                     AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal', 'uniqueidentifier')
  43.                     AND QUOTENAME(COLUMN_NAME) > @ColumnName
  44.             )
  45.  
  46.             IF @ColumnName IS NOT NULL BEGIN
  47.                 INSERT INTO
  48.                                                             #Results
  49.                 EXEC (''
  50.                     + 'SELECT ''' + @TableName + ''', ''' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)'
  51.                     + ' FROM ' + @TableName + ' (NOLOCK) '
  52.                     + ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
  53.                 )
  54.             END
  55.         END   
  56.     END
  57.  
  58.     SELECT
  59.                         TableName,
  60.                         ColumnName,
  61.                         ColumnValue,
  62.                         COUNT(*) as Count,
  63.                         Query = CONCAT('SELECT * FROM ', TableName, ' WHERE ', ColumnName, ' LIKE ', @SearchStr2)
  64.             FROM
  65.                         #Results
  66.             GROUP BY
  67.                         TableName, ColumnName,     ColumnValue
  68.             ORDER BY
  69.                         TableName, ColumnName, ColumnValue
  70. END


Once you run the above command, you can search all tables by using the following command:

  1. EXEC SearchAllTables 'SearchTerm'

 




    • Related Articles

    • SQL Spreadsheets: DocType Mappings

      The following SQL script will generate a Google Spreadsheet that will allow users to override document category names. This is extremely helpful when merging systems. SELECT V1.Id, V1.Final_ReferenceCode as 'Old Code', V1.Final_Subject as 'Old Name', ...

    • SQL Spreadsheets: User Mappings

      The following SQL script will generate a Google Spreadsheet that will allow users to override email addresses. This is extremely helpful when merging old user accounts together. SELECT V1.Id, V1.Final_FullName as 'Old User (FullName)', ...

    • SQL Spreadsheets: Manually Prioritizing Matters

      --The following SQL will generate a list of matters and allow their priorities to easily be specified. CREATE OR ALTER FUNCTION SafeSingleLine ( @Input NVARCHAR(MAX) ) RETURNS NVARCHAR(MAX) AS BEGIN DECLARE @Ret NVARCHAR(MAX) = COALESCE(@Input, '') ...

    • SQL Spreadsheets: Reassigning Practice Areas

      Sometimes users want to reassign practice areas as part of the data restore into a new system. The following process makes it easy. Generate the SQL Spreadsheet Run the following command then copy the results and the headers into a Google Doc. ...

    • SQL Spreadsheets: Cherry Picking Contacts

      The following SQL command will generate a spreadsheet that can be used to cherry-pick specific contacts. CREATE OR ALTER FUNCTION SafeSingleLine ( @Input NVARCHAR(MAX) ) RETURNS NVARCHAR(MAX) AS BEGIN DECLARE @Ret NVARCHAR(MAX) = COALESCE(@Input, '') ...