T-SQL从日期值中搜索所有列和表

Question

hi我正在尝试搜索数据服务器中包含特定日期值的所有列和表，例如SQL server数据库中的"2021-07-22"。

请注意，它不是字符串类型，而是日期时间类型。

我在谷歌上搜索了一下，做了很多研究，但找不到任何可用的东西。有人能帮忙吗？感谢

Answer 1

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