Armand Niculescu, BEng, MSM, is a 34 year old Art Director at Media Division. and he enjoys working with visual arts for film, web and print.

2 responses to “Searching in all tables and columns of a database”

  1. James

    The script works fine provided you don’t need to search through ‘text or ‘ntext’ columns. I’ve tried adding these data types to line

    AND DATA_TYPE IN (‘char’, ‘varchar’, ‘nchar’, ‘nvarchar’, ‘ntext’, ‘text’)
    but this just throuws the following error:
    Msg 8116, Level 16, State 1, Line 1
    Argument data type ntext is invalid for argument 1 of left function.

    Can anyone please help?

  2. Ricardo Alfaro

    Hi, this procedure is very useful but does not search the numeric fields (getting tested). Here the modified procedure that includes the numeric fields:
     
    CREATE PROC 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

    — ****************************************
    — Also for numeric field
    — Modified by Ricardo Alfaro Vega

    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’)
    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 ISNUMERIC(@SearchStr) = 1
    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 (‘int’,'bigint’,'smallint’,'tinyint’,'decimal’,'float’,'real’)
    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
    )
    END                   
    END
    END
    END
    END

    SELECT ColumnName, ColumnValue FROM #Results
    END

    GO