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 ...

    • 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 ...

    • Other: Total Record Counts

      The following SQL script will count all records in all tables in a database. DECLARE @TableRowCounts TABLE ([TableName] VARCHAR(128), [RowCount] INT) ; INSERT INTO @TableRowCounts ([TableName], [RowCount]) EXEC sp_MSforeachtable 'SELECT ''?'' ...

    • Synthesize Missing Practice Areas

      The following SQL script will create practice areas where they do not exist in the Matters table. --Synthesize missing practice areas from Matters INSERT INTO __M_Matters_PracticeAreas( Id, Final_Subject ) SELECT DISTINCT Final_PracticeArea_Id, ...