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: 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, '') ...

    • Create a "Legacy Matter Number" Custom Field

      Some firms rely heavily on Matter Numbers to search for a specific matter. Sometimes when they migrate to a new system it can't replicate the automatic numbering system from their old system so they want to renumber all their matters to a new scheme ...

    • Create a "Legacy Client Number" Custom Field

      Some firms rely heavily on Client Numbers to search for a specific client. Sometimes when they migrate to a new system it can't replicate the automatic numbering system from their old system so they want to renumber all their matters to a new scheme ...

    • Audit Scripts: Row Counts and Progress Monitoring

      This article shows you a script that can be used to monitor the progress of both a Backup and a Restore. It can also be used to just review row counts on all the Universal Database tables. The first code block is used to populate a SQL Server Temp ...

    • Delete unused Practice Areas

      The following SQL script will delete unused practice areas. --Delete any practice areas that are not used UPDATE __M_Matters_PracticeAreas SET Final_Status = 'Deleted' WHERE 1=1 AND Id NOT IN( SELECT Final_PracticeArea_Id FROM __M_Matters WHERE ...