Searching for data in all existing tables of a database

Question

I want to search for the date '2017-08-08 09:26' in all existing tables of a database and get the column name and table name of the table that contains that date. So I can find that date in table A in column a.

example: '2017-08-08 09:26' exists in column 'a' of table 'A'

Please give me some suggestions for a solution.

Right now I am using this query in which I can search for columns, but I want to search the data in the columns of the tables in my database.

SELECT   
  sys.columns.name AS ColumnName,
  sys.tables.name AS TableName    
FROM
  sys.columns
JOIN    
  sys.tables   
ON    
  sys.columns.object_id = sys.tables.object_id    
WHERE   
  sys.columns.name LIKE '%service%'

What possible value could you get from knowing some random timestamp, or even a specific one, exists somewhere in your database? — Andrew, Sep 18 '19 at 21:03
Not an exact duplicate but close: [Find a string by searching all tables in SQL Server Management Studio 2008](https://stackoverflow.com/q/15757263/1260204). See [google...](https://www.google.com/search?q=searching+in+all+tables+of+a+database+site:stackoverflow.com) for more. — Igor, Sep 18 '19 at 21:05
I want that column of the table in which my searched data exists. — Devendra Singh, Sep 18 '19 at 21:05
@Andrew the "possible value" is that there is a date/time appearing somewhere in an application or a report, but you don't have the code, so some forensic work is required to find it. A perfectly reasonable question. The dynamic sql answer is the way to go. — TomC, Sep 19 '19 at 05:25

score 0 · Answer 1 · answered Sep 18 '19 at 21:26

You need some good old-fashion dynamic SQL for this:

DECLARE @Sql varchar(max);
SELECT @Sql = STUFF((SELECT 'UNION ALL ' + CHAR(10) +'SELECT '''+ 
  sys.columns.name +''' AS ColumnName, '''+ 
  sys.tables.name +''' AS TableName '+ CHAR(10) +
  'FROM sys.tables.name ' + CHAR(10) +
  'WHERE '+ QUOTENAME(sys.columns.name) +' = @DateTimeValue ' + CHAR(10)

FROM
  sys.columns
JOIN    
  sys.tables     
ON    
  sys.columns.object_id = sys.tables.object_id    
-- type ids:  datetime2, smalldatetime, datetime 
WHERE sys.columns.system_type_id IN(42, 58, 61)
FOR XML PATH('')
), 1, 11, 'DECLARE @DateTimeValue datetime = ''2017-08-08T09:26:00'';' + CHAR(10))

-- When using dynamic SQL, PRINT is your best friend.
PRINT @Sql

/*
@Sql should contain something like this:

DECLARE @DateTimeValue datetime = '2017-08-08T09:26:00';
SELECT 'Col1' AS ColumnName, 'Table1' AS TableName 
FROM sys.tables.name 
WHERE [Col1] = @DateTimeValue 
UNION ALL 
SELECT 'Col2' AS ColumnName, 'Table12' AS TableName 
FROM sys.tables.name 
WHERE [Col2] = @DateTimeValue 
...

 */

-- Unremark only after you've seen what's inside @Sql.
--EXEC(@Sql)

You might also want to check out my blog post called [The do’s and don’ts of dynamic SQL for SQL Server](https://zoharpeled.wordpress.com/2019/09/12/the-does-and-donts-of-dynamic-sql-for-sql-server-%ef%bb%bf/) — Zohar Peled, Sep 18 '19 at 21:34

score 0 · Answer 2 · answered Sep 19 '19 at 06:08

Use the below script for getting entire DB search

DECLARE
@SearchStr nvarchar(100) = 'Pass'

BEGIN

DECLARE @Results TABLE(ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 varchar(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
        )
    END
END 
END

SELECT ColumnName, ColumnValue FROM @Results
END

Searching for data in all existing tables of a database

2 Answers2