SQL search entire database for partial datetime return

Question

I'm trying to search an entire database for all returns of a specific date. I've used the code below however its not picking up the column I expected.

The table column with the data is a datetime column, and the specific value contained is 2010-09-10 13:33:11.441, my search string (based on the script from this website) is like %2010-09-10%, but the search return doesnt show the datetime column expected. It does pick up on a varchar field that has the text 2010-09-10.

I'm sure its something simple to do with searching a datetime specific field?? Any pointers please.

USE MAIN_DATABASE
DECLARE @SearchStr nvarchar(100) = '2010-09-10'
DECLARE @Results TABLE (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', 'datetime', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
                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

https://meta.stackoverflow.com/questions/388759/why-should-i-tag-my-rdbms — Reporter, Jun 13 '23 at 14:06
[How do I search an SQL Server database for a string?](https://stackoverflow.com/questions/9185871/how-do-i-search-an-sql-server-database-for-a-string) ? — Luuk, Jun 13 '23 at 14:18
Luuk - I'm not sure that helps? I can happily search for a string using the above code. the issue is the above doesnt return when its a datetime which is what I need to do. — Richard, Jun 13 '23 at 14:30
@Richard I think the code that Luuk is pointing to is a similar to yours, but it looks superior in that it converts to the column being searched to a varchar so that your date look up doesn't fail with non-varchar types. The code you are sharing is a little naive about column types and is leading to the issue you are facing. — JNevill, Jun 13 '23 at 14:32
@JNevill - thanks, I've tried that new sp now and its the same as the above, it only returns the varchar column hit not the datatime column. — Richard, Jun 13 '23 at 14:52
Unless this query is stopping at the first find? and not continuing through the whole database? — Richard, Jun 13 '23 at 15:00
@Richard: You should do more than just copy/paste, look at that stored procedure, and see that it is using: ` And Data_Type In ('ntext','text','nvarchar','nchar','varchar','char')`. So, it will only find your date when it is stored using one of those data types..... — Luuk, Jun 13 '23 at 15:07
I copied the SP, and changed it (for `datetime2` fields), and it works, see: https://dbfiddle.uk/26udGsSK — Luuk, Jun 13 '23 at 15:17
@Luuk I ran that again with the change and it still only returns the hit for the varchar column on the 2nd table. What I do notice is that the procedure stops as soon as it finds that return (takes about 2 minutes). Does the script only find the first hit? or does it only print the result once finished? This date is in a lot of tables so there should be more than just one return (which is the wrong one) — Richard, Jun 13 '23 at 16:23
How many results do YOU see at the end of this dbfidde? (https://dbfiddle.uk/26udGsSK , it is the same one as posted earlier) — Luuk, Jun 13 '23 at 16:35
Please share INFO, like the DDL of the table that hold the data that you cannot find. — Luuk, Jun 13 '23 at 16:36

score 0 · Answer 1 · answered Jun 13 '23 at 14:47

I can't say that I fully understand what you want to do. But I Think You're Trying To Achieve Something Like This

DECLARE @SearchStr nvarchar(100) = '2010-09-10' 
DECLARE @Results TABLE (ColumnName nvarchar(370), ColumnValue varchar(3630))
SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @DataType nvarchar(50)

DECLARE CRSR CURSOR FOR
SELECT QUOTENAME(TBL.TABLE_SCHEMA) + '.' + QUOTENAME(TBL.TABLE_NAME), QUOTENAME(COLUMN_NAME), CLMN.DATA_TYPE
FROM INFORMATION_SCHEMA.TABLES TBL INNER JOIN INFORMATION_SCHEMA.COLUMNS CLMN ON CLMN.TABLE_SCHEMA = TBL.TABLE_SCHEMA AND CLMN.TABLE_NAME = TBL.TABLE_NAME 
WHERE TBL.TABLE_TYPE = 'BASE TABLE' AND OBJECTPROPERTY( OBJECT_ID( QUOTENAME(TBL.TABLE_SCHEMA) + '.' + QUOTENAME(TBL.TABLE_NAME) ), 'IsMSShipped' ) = 0
AND CLMN.DATA_TYPE IN ('char', 'datetime', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
OPEN CRSR
FETCH NEXT FROM CRSR
INTO @TableName, @ColumnName, @DataType
WHILE @@FETCH_STATUS = 0
BEGIN
/*I've added this here to avoid Type conversion errors. You can change it If you want*/
DECLARE @WhereClause nvarchar(500) = CASE WHEN @DataType IN('int') THEN  @ColumnName + ' = CAST(CAST(''' + @SearchStr + ''' AS DATETIME) AS INT)'
WHEN @DataType IN('decimal') THEN  @ColumnName + ' = CAST(CAST(''' + (@SearchStr) + ''' AS DATETIME) AS DECIMAL)'
WHEN @DataType ='datetime' THEN @ColumnName + ' = ''' + (@SearchStr) + ''''
ELSE @ColumnName + ' LIKE ' + ('''%' + @SearchStr + '%''') END

INSERT INTO @Results
    EXEC
    (
        'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
        FROM ' + @TableName + ' (NOLOCK) ' +
        ' WHERE ' + @WhereClause
    )
FETCH NEXT FROM CRSR
INTO @TableName, @ColumnName, @DataType

END
CLOSE CRSR
DEALLOCATE CRSR

SELECT ColumnName, ColumnValue FROM @Results

Adem, Thanks - this got back a lot more entries, picking up the format 'Sept 10 2010 12:00AM' as well as '40429' for some reason? It still didn't get the table column I was looking for thou. I'll try to explain it further. I have two tables. The first one contains a datetime column where the information is stored as 2010-09-10 13:33:11.44. The second table contains data in varchar format and has an entry " this data was entered 2010-09-10 and is correct". I need to search all tables and return hits on 2010-09-10. So far all queries run only return the varchar table. — Richard, Jun 13 '23 at 15:11
I've simplified it a lot, thers actually 100+ tables and the database is about 6 gb in size. I need to check all tables for any column that has the date 2010-09-10 contained. — Richard, Jun 13 '23 at 15:16

SQLpro · Answer 2 · 2023-06-13T15:21:52.823

First of all DATETIME datatype is unprecise so :

SELECT CAST('2010-09-10 13:33:11.441' AS DATETIME)

Will return 2010-10-09 13:33:11.440

It is recomended to use DATETIME2 instead of DATETIME.

Second prefer to USE standard ISO SQL views like INFORMATION_SCHEMA.COLUMNS instaed of systeme views (sys. ). INFORMATION_SCHEMA limitis always objects to user's one and not user and systeme objetcs...

The right solution is :

DECLARE @DH CHAR(21) = FORMAT(CAST('2010-09-10 13:33:11.441' AS DATETIME), 'yyyyMMdd HH:mm:ss.fff');
DECLARE @SQL NVARCHAR(max) = N'';


SELECT @SQL = @SQL + N'SELECT ''' + C.TABLE_SCHEMA + '.' + C.TABLE_NAME + ''' AS TABLENAME, ''' 
       + COLUMN_NAME + ''' AS COLUMNAME, * FROM [' +
       C.TABLE_SCHEMA + '].[' + C.TABLE_NAME + '] WHERE [' + COLUMN_NAME +'] = ''' + @DH + ''';'
FROM   INFORMATION_SCHEMA.COLUMNS AS C
       JOIN INFORMATION_SCHEMA.TABLES AS T
          ON C.TABLE_SCHEMA = T.TABLE_SCHEMA
          AND C.TABLE_NAME = T.TABLE_NAME
WHERE  T.TABLE_TYPE = 'BASE TABLE'
  AND  C.DATA_TYPE = 'datetime';

  PRINT @SQL

EXEC (@SQL);

thanks, but that doesn't return anything. looking at the sql message after running I notice is searching for '20100910 13:33:11.441' the data in the datetime field i'm looking for is 2010-09-10 13:33:11.441 — Richard, Jun 13 '23 at 16:37
if I go into the table and run a simple query select * from datatable where datestart = '2010-09-10 13:33:11:441' then it finds the relevant data. — Richard, Jun 13 '23 at 16:49

score 0 · Accepted Answer · answered Jun 13 '23 at 15:36

Here's a version that might work for you:

DROP TABLE IF EXISTS dummy

CREATE TABLE dummy (
    a varchar(30) DEFAULT '2023-03-06'
,   b char(30) DEFAULT '2023-03-06'
,   c nchar(30) DEFAULT '2023-03-06'
,   d nvarchar(30) DEFAULT '2023-03-06'
,   e nvarchar(max) DEFAULT '2023-03-06'
,   f xml DEFAULT '<root a="2023-03-06"></root>'
,   g text DEFAULT '2023-03-06'
,   h ntext DEFAULT '2023-03-06'
,   i sql_variant DEFAULT '2023-03-06'
,   j datetime DEFAULT '20230306'
,   k date DEFAULT '20230306'
,   l smalldatetime DEFAULT '20230306'
,   m datetime2 DEFAULT '20230306'
,   nERROR sql_variant default 0
,   o sql_variant default CAST('20230306' AS DATETIME)
,   p sql_variant default CAST('20230306' AS DATETIME2)
,   q sql_variant default CAST('20230306' AS DATE)
,   r sql_variant default CAST('20230306' AS SMALLDATETIME)
,   s sql_variant default CAST('20230306' AS DATETIMEOFFSET)
,   t datetimeoffset DEFAULT '20230306'
)
INSERT INTO dummy
DEFAULT VALUES;



DECLARE @SearchStr nvarchar(100) = '%2023-03-06%'
DROP TABLE IF EXISTS #Results 
CREATE TABLE #Results (tablename sysname, ColumnName sysname, ColumnValue nvarchar(MAX))
DECLARE @sql nvarchar(max), @table sysname, @col sysname, @type int

SET NOCOUNT ON

DECLARE cr_x CURSOR  read_only forward_only local static FOR
    SELECT  so.name, sc.name, CASE WHEN ts.name IN ('char', 'nchar','varchar', 'nvarchar', 'text', 'ntext', 'xml') THEN 0 when ts.name = 'sql_variant' then 1 ELSE 2 END
    FROM    sys.tables so
    INNER JOIN sys.columns sc
        ON  sc.object_id = so.object_id
    INNER JOIN sys.types ts
        ON  ts.user_type_id = sc.user_type_id
    WHERE   ts.name IN ('char', 'nchar','varchar', 'nvarchar', 'text', 'ntext', 'xml', 'datetime', 'date', 'smalldatetime', 'datetime2','datetimeoffset','sql_variant')
OPEN cr_x
WHILE 1 = 1
BEGIN
    FETCH next FROM cr_x INTO @table, @col, @type
    IF @@fetch_status <> 0
        BREAK;

    SELECT  @sql = 'insert into #Results  (tableName, columnName, columnValue)
    select  @table, @col, ' + colNew + N'
    from    ' + quotename(@table) + '
    where ' + colNew + N' LIKE @SearchStr 
    group by ' + colNew
    FROM    (
            SELECT  CASE 
                WHEN @type = 0 THEN 'cast(' + QUOTENAME(@col) + ' as nvarchar(max))' 
                when @type = 1 then '
                    case when SQL_VARIANT_PROPERTY(' + QUOTENAME(@col) + ',''BaseType'') in (''char'', ''nchar'',''varchar'', ''nvarchar'', ''text'', ''ntext'', ''xml'')
                    then cast(' + QUOTENAME(@col) + N' as nvarchar(max))
                    when  SQL_VARIANT_PROPERTY(' + QUOTENAME(@col) + ',''BaseType'') IN (''datetime'', ''date'', ''smalldatetime'', ''datetime2'',''datetimeoffset'')
                    then convert(nvarchar(max), cast(' + QUOTENAME(@col) + ' as datetime), 121)
                    end
                '
                when @type = 2 then 'convert(varchar(30), ' + QUOTENAME(@col) + ', 121)' END AS colNew
        ) x
    
    EXEC    sp_executesql @sql
    ,   N'@table sysname, @col sysname, @searchstr nvarchar(max)'
    , @table = @table, @col = @col, @searchstr = @searchstr
    
END
CLOSE CR_X
DEALLOCATE CR_X

SELECT  TOP 301110 *
FROM    #Results

It checks the tables and columns with specific datatypes and for datetimes it does a bit of conversion as well as grouping to avoid too many rows. I added a little dummy table so you can verify various values.

It also handles sql_variant wrapping datatypes

this worked perfectly!! thank you so much, 1742 results returned so I have a bit of work to go through :-) — Richard, Jun 13 '23 at 17:05

SQL search entire database for partial datetime return

3 Answers3