Searching in all tables and columns of a database

Share on facebook
Share on twitter
Share on linkedin
Share on reddit

I was forced recently to do some maintenance and bug fixing on an aging .NET-based CMS.

Most of the problems were in the SQL Server database, with lots of corrupted entries. The most frustrating thing however was that at times I didn’t even know where to find the entries. Try finding some specific strings in a database with a hundred tables, each with many columns (poorly named, obviously) and tens of thousands of records — it’s like finding the proverbial needle in the haystack.

You may remember a similar issue we had a few months ago, where we wrote a stored procedure to search & replace all occurences of a string in a table column. That was easy enough since we knew were to look.

The cool thing (even though it’s daunting at first) about SQL server is that you can obtain any information about the databases using SQL queries. After having a look at System Databases – master and its Views, it became a little clearer how to do it. Even better, I discovered that someone else had the same problem as myself, sparing me the need to reinvent the wheel.

The full source code of the stored procedure is here:

  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, SQL Server 2000, SQL server 2005
  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

The original article can be found here.

Armand Niculescu

Armand Niculescu

As the Senior Project manager, Armand is one of the rare kind of developers that can do both design and programming with equal skill. This, coupled with a solid background and many years of experience, enables him to see the big picture and plan for the small details.

2 Responses

  1. 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. 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

Comments are closed.