Search All Columns in All Tables using TSQL

1…Works like a charm:

   1: DECLARE @SearchStr nvarchar(100)

2:

   3: SET @SearchStr = 'SEARCH_KEYWORD'

4:

   5: CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

6:

   7: SET NOCOUNT ON

8:

   9: DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)

  10: SET  @TableName = ''

  11: SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

12:

  13: WHILE @TableName IS NOT NULL

  14: BEGIN

  15:     SET @ColumnName = ''

  16:     SET @TableName =

  17:     (

  18:         SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))

  19:         FROM     INFORMATION_SCHEMA.TABLES

  20:         WHERE         TABLE_TYPE = 'BASE TABLE'

  21:             AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName

  22:             AND    OBJECTPROPERTY(

  23:                     OBJECT_ID(

  24:                         QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)

  25:                          ), 'IsMSShipped'

  26:                            ) = 0

  27:     )

28:

  29:     WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)

  30:     BEGIN

  31:         SET @ColumnName =

  32:         (

  33:             SELECT MIN(QUOTENAME(COLUMN_NAME))

  34:             FROM     INFORMATION_SCHEMA.COLUMNS

  35:             WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)

  36:                 AND    TABLE_NAME    = PARSENAME(@TableName, 1)

  37:                 AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')

  38:                 AND    QUOTENAME(COLUMN_NAME) > @ColumnName

  39:         )

40:

  41:         IF @ColumnName IS NOT NULL

  42:         BEGIN

  43:             INSERT INTO #Results

  44:             EXEC

  45:             (

  46:                 'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)

  47:                 FROM ' + @TableName + ' (NOLOCK) ' +

  48:                 ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2

  49:             )

  50:         END

  51:     END

  52: END

53:

  54: SELECT ColumnName, ColumnValue FROM #Results

2….Here is another TSQL code which helps in searching all stored procedures:

   1: SELECT Name FROM sys.procedures

   2: WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%ProcessLog%'

OR use (this will search sprocs, udfs, triggers etc. (not just sprocs)):

   1: SELECT object_name(id) FROM syscomments where text like '%ProcessLog%'

3….. Here is another SQL which is helpful. If you have a lot of columns in a table and you need to find columns which are of a particular data type (e.g. datetime) then we can use this:

   1: SELECT * FROM SYSCOLUMNS WHERE Id = OBJECT_ID('geneva_security_extract') AND XTYPE = 61

. you pass in the name of the table to the OBJECT_ID function. Hope this helps

Posted on Friday, June 18, 2010 5:06 AM | Back to top