Search All tables within a Database

 

I came across this very useful script the other day, it simply creates a Stored Procedure, where it allows you to search all columns within every table of a selected database, While it was said to be tested on SQL 7 and 2000 I can confirm it works on SQL 2005 just as well :)

   1: CREATE PROC SearchAllTables
   2: (
   3:     @SearchStr nvarchar(100)
   4: )
   5: AS
   6: BEGIN
   7:  
   8:     -- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
   9:     -- Purpose: To search all columns of all tables for a given search string
  10:     -- Written by: Narayana Vyas Kondreddi
  11:     -- Site: http://vyaskn.tripod.com
  12:     -- Tested on: SQL Server 7.0 and SQL Server 2000
  13:     -- Date modified: 28th July 2002 22:50 GMT
  14:  
  15:  
  16:     CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
  17:  
  18:     SET NOCOUNT ON
  19:  
  20:     DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
  21:     SET  @TableName = ''
  22:     SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
  23:  
  24:     WHILE @TableName IS NOT NULL
  25:     BEGIN
  26:         SET @ColumnName = ''
  27:         SET @TableName = 
  28:         (
  29:             SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
  30:             FROM     INFORMATION_SCHEMA.TABLES
  31:             WHERE         TABLE_TYPE = 'BASE TABLE'
  32:                 AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
  33:                 AND    OBJECTPROPERTY(
  34:                         OBJECT_ID(
  35:                             QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
  36:                              ), 'IsMSShipped'
  37:                                ) = 0
  38:         )
  39:  
  40:         WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
  41:         BEGIN
  42:             SET @ColumnName =
  43:             (
  44:                 SELECT MIN(QUOTENAME(COLUMN_NAME))
  45:                 FROM     INFORMATION_SCHEMA.COLUMNS
  46:                 WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
  47:                     AND    TABLE_NAME    = PARSENAME(@TableName, 1)
  48:                     AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
  49:                     AND    QUOTENAME(COLUMN_NAME) > @ColumnName
  50:             )
  51:     
  52:             IF @ColumnName IS NOT NULL
  53:             BEGIN
  54:                 INSERT INTO #Results
  55:                 EXEC
  56:                 (
  57:                     'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
  58:                     FROM ' + @TableName + ' (NOLOCK) ' +
  59:                     ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
  60:                 )
  61:             END
  62:         END    
  63:     END
  64:  
  65:     SELECT ColumnName, ColumnValue FROM #Results
  66: END

 

Once you have ran the SP, you must call it, then within the ‘ ‘ you can type what you are looking for, the execute and wait!

EXEC SearchAllTables ‘your query here’

 

the original script was Found at :

http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm


3 Responses to “Search All tables within a Database”

  • Dean Says:

    I would love to see this modified to also search text and ntext data as well.

  • AmmarR Says:

    its a very useful script
    i did a small change to the script and its searchs text and ntext now

    Create PROC [dbo].[SearchAllTables]
    (
    @SearchStr nvarchar(100)
    )
    AS
    BEGIN

    — Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
    — Purpose: To search all columns of all tables for a given search string
    — Written by: Narayana Vyas Kondreddi
    — Site: http://vyaskn.tripod.com
    — Tested on: SQL Server 7.0 and SQL Server 2000
    — Date modified: 28th July 2002 22:50 GMT

    CREATE TABLE #Results (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’,'text’,'ntext’)
    AND QUOTENAME(COLUMN_NAME) > @ColumnName
    )

    IF @ColumnName IS NOT NULL
    BEGIN
    INSERT INTO #Results
    EXEC
    (
    ‘SELECT ”’ + @TableName + ‘.’ + @ColumnName + ”’, ‘ + @ColumnName + ‘
    FROM ‘ + @TableName + ‘ (NOLOCK) ‘ +
    ‘ WHERE ‘ + @ColumnName + ‘ LIKE ‘ + @SearchStr2
    )
    END
    END
    END

    SELECT ColumnName, ColumnValue FROM #Results
    END

Leave a Reply