T-SQL search all the columns and the tables from a date value

Question

hi I am trying to search all the columns and tables in a data server that contain a specific date value e.g. '2021-07-22' in SQL server database.

Please notice is NOT a string type, but a datetime type.

I googled and did quite a bit research but could not find anything available. Can anyone provide some help please? Thanks

Does this answer your question? [Search all tables, all columns for a specific value SQL Server \[duplicate\]](https://stackoverflow.com/questions/1796506/search-all-tables-all-columns-for-a-specific-value-sql-server) — Thom A, Oct 25 '22 at 09:50
Does this answer your question? [Find a value anywhere in a database](https://stackoverflow.com/questions/436351/find-a-value-anywhere-in-a-database) — Thom A, Oct 25 '22 at 09:50
Does this answer your question? [Find a string by searching all tables in SQL Server](https://stackoverflow.com/questions/15757263/find-a-string-by-searching-all-tables-in-sql-server) — Thom A, Oct 25 '22 at 09:50
I found those 3 with a *very quick* [search](https://www.google.com/search?q=Search+all+tables+and+columns+for+value+sql+server) and there are a wealth more answers here on [Stack Overflow](https://www.google.com/search?q=Search+all+tables+and+columns+for+value+sql+server+site:stackoverflow.com); why are *none* of those Q&A, that you found when doing your research, helpful? — Thom A, Oct 25 '22 at 09:52
No, not to find a string, the value is a datetime, I have found those ones, but not helpful — Alice jinx, Oct 25 '22 at 09:52
because my searching is based on a DATETIME type not a string type, it is easy to google/find those answers — Alice jinx, Oct 25 '22 at 09:55
Ok, so what is the difficulty you are having changing those answers to searching for a date and time value instead? The answers are still applicable, as the metholody is **identical**. Show us *your attempts* to use those solutions for a date and time data type instead, and *explain* why it isn't working. — Thom A, Oct 25 '22 at 09:56
because it is hard to convert the logic to a datetime, I tried and asked other people help online, no one can convert it correctly. Please provide some suggestive answers if you could — Alice jinx, Oct 25 '22 at 09:58
Changing `DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')` to `DATA_TYPE IN ('datetime')` would be a start. Without knowing where your attempt isn't working, I can't explain why it isn't working — Thom A, Oct 25 '22 at 10:09
Your using `datetime` and searching for `2021-07-22`. Is it a "match" to the `datetime` value for "half past noon on the 22nd of July in 2021 C.E.", or do you only want to match midnight? And you don't want to match any `date` or other data type values? — HABO, Oct 26 '22 at 02:21

score 2 · Answer 1 · answered Oct 25 '22 at 13:58

DECLARE @SearchDate DATE = '2022-01-01'

DECLARE @SQL NVARCHAR(MAX) = ''
DECLARE @SchemaName SYSNAME
DECLARE @TableName SYSNAME
DECLARE @ColumnName SYSNAME
DECLARE @RowIDName SYSNAME
DECLARE @Results TABLE(ColumnName SYSNAME,RowIDName SYSNAME,RowIDValue NVARCHAR(255), ColumnValue DATETIME)

DECLARE @TableCols CURSOR

SET @TableCols = CURSOR FOR

SELECT QUOTENAME(s.name) AS SchemaName,QUOTENAME(t.name) AS TableName,QUOTENAME(c.name) AS ColumnName,QUOTENAME(Ids.ID) AS RowIDName
FROM sys.tables t
JOIN sys.columns c ON t.object_id = c.object_id
JOIN sys.schemas s ON s.schema_id = t.schema_id
CROSS APPLY (SELECT TYPE_NAME(c.system_type_id))TypeNames(TypeName)
CROSS APPLY (SELECT c2.Name FROM sys.Columns c2 WHERE c2.column_id = 1 AND c2.object_id = c.object_id)IDs(ID)
WHERE t.is_ms_shipped = 0
AND TypeNames.TypeName IN ('date','datetime','datetime2','smalldatetime')

OPEN @TableCols

FETCH NEXT FROM @TableCols INTO @SchemaName,@TableName, @ColumnName,@RowIDName

WHILE @@fetch_status = 0
  BEGIN

    INSERT INTO @Results(ColumnName,RowIDName,RowIDValue,ColumnValue)
    EXEC
            (
                'SELECT ''' + @SchemaName+'.'+@TableName + '.' + @ColumnName + ''','''+@RowIDName+''','+@RowIDName+' ,' + @ColumnName + '  
                FROM ' + @TableName + 
                ' WHERE ' + @ColumnName + ' = ''' + @SearchDate+''''
                )
    FETCH NEXT FROM @TableCols INTO @SchemaName,@TableName, @ColumnName,@RowIDName
  END

CLOSE @TableCols
DEALLOCATE  @TableCols

SELECT * FROM @Results

T-SQL search all the columns and the tables from a date value

1 Answers1