[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 970
  • Last Modified:

SELECT TABLE_NAME, COLUMN_NAME from columns WHERE... (a value exists)

Experts:

I have millions of tables to search through for a word (i.e., cotton) but I don't know which table the word is located in and I don't know what the column name will be for any given table.  Is there a way to search all tables, all columns where in any of the columns the word exists?

Thanks in advance for your help
0
pdlarue
Asked:
pdlarue
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
CREATE PROC SearchAllTables
    @SearchStr nvarchar(100)
AS
    BEGIN


        CREATE TABLE #Results
            (
              i int identity,
              TableName varchar(450),
              ColumnName nvarchar(370),
              ColumnValue nvarchar(3630)
            )

        SET NOCOUNT ON

        DECLARE @TableName nvarchar(256),
            @ColumnName nvarchar(128),
            @SearchStr2 nvarchar(110)
        SET @TableName = ''
        SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%', '''')

        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', 'uniqueidentifier' )
                                                    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 '
                                          + @SearchStr2 )
                                IF @@ROWCOUNT = 0
                                    DELETE  FROM #results
                                    where   i = SCOPE_IDENTITY() - 1
                            END
                    END      
            END

        SELECT DISTINCT
                TableName,
                ColumnName,
                ColumnValue
        FROM    #Results
    END



GO
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now