Find All Columns of All Tables For a Keyword - Sql Server

Feb 20, 2019 BY xtreme

a custom function

declare @StringToSearch nvarchar(100)
set @StringToSearch='Establishment'

CREATE TABLE #Results (_ColumnName nvarchar(370), ColumnValue nvarchar(3630))
DECLARE @TableName nvarchar(256), @_ColumnName nvarchar(128), @SearchStr nvarchar(110)
SET @TableName = ''
SET @SearchStr = QUOTENAME('%' + @StringToSearch + '%','''')

SET NOCOUNT ON

WHILE @TableName IS NOT NULL
BEGIN
     SET @_ColumnName = ''
     SET @TableName =
     (
             SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
             FROM INFORMATION_SCHEMA.TABLES
             WHERE TABLE_TYPE = 'BASE TABLE'
             AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
             AND OBJECTPROPERTY(
                 OBJECT_ID(
                     QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                ), 'IsMSShipped'
             ) = 0
     )

     WHILE (@TableName IS NOT NULL) AND (@_ColumnName IS NOT NULL)
     BEGIN
          SET @_ColumnName =
          (
               SELECT MIN(QUOTENAME(COLUMN_NAME)) FROM INFORMATION_SCHEMA.COLUMNS
               WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2) AND TABLE_NAME = PARSENAME(@TableName, 1)
               AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar') AND QUOTENAME(COLUMN_NAME) > @_ColumnName
          )

          IF @_ColumnName IS NOT NULL
          BEGIN
               INSERT INTO #Results
               EXEC
               (
                   'SELECT ''' + @TableName + '.' + @_ColumnName + ''', LEFT(' + @_ColumnName + ', 3630)
                   FROM ' + @TableName + ' (NOLOCK) ' +
                   ' WHERE ' + @_ColumnName + ' LIKE ' + @SearchStr
               )
          END
     END
END

SELECT _ColumnName, ColumnValue FROM #Results




Related