检索数据库中所有表的最大加载日期

Question

我试图获得的输出；

(数据库名称=ATT(

1. 表名
2. 列名
3. 最大加载日期=仅此列的最大(加载日期(

loaded_date是数据库中大约50个表中的一列，具有相同的名称和数据类型(Datetime(

select * FROM sys.tables 
select * FROM syscolumns

我一直在探索系统表，但运气不好，看了一些帖子，它可能是动态SQL，这是我从未做过的。

Answer 1

您可以编写一个sql来编写sql。。

SELECT REPLACE(
'select ''{tn}'' as table_name, max(loaded_date) as ld from {tn} union all'
,'{tn}',table_name)
FROM
information_schema.columns
WHERE 
column_name = 'loaded_date'

运行它，然后将除最后一个UNION all外的所有UNION all从结果窗口复制到查询窗口中，然后再次运行

---

如果你想把所有这些都放在一个字符串中进行动态执行，我想它看起来像(未经测试的(一个包含以下内容的过程：

DECLARE @x NVARCHAR(MAX);
SELECT @x =
STRING_AGG(
REPLACE(
'select ''{tn}'' as table_name, max(loaded_date) as ld from {tn}'
,'{tn}',table_name)
,' union all ') 
FROM
information_schema.columns
WHERE 
column_name = 'loaded_date';
EXECUTE sp_executesql @x;

如果你的SQL是旧的，并且没有string_agg，那就有点尴尬了——但有很多例子是"将行转换成CSV"；在sql server中，看起来像STUFF..FOR XML PATH-https://duckduckgo.com/?t=ffab&q＝行+到+CSV+SQLS&ia=卷筒纸

Answer 2

我写了一个更永久的脚本类型来实现这一点。它返回当前数据库中表列表的结果集，其中包含一个名为loaded_date的列，以及每个表的MAX(loaded_date)结果。该脚本通过在每个表上单独循环和运行查询，并跟踪表变量中每个表的最大值，来单独查询每个表。它还有一个@Debug变量，允许您查看将要运行而不是实际运行的查询的文本，并实现自定义错误消息以解决任何问题。

/*disable row count messages*/
SET NOCOUNT ON; 
/*set to 1 to debug (aka just print queries instead of running)*/
DECLARE @Debug bit = 0; 
/*get list of tables to query and assign a unique index to row to assist in looping*/
DECLARE @TableList TABLE(
SchemaAndTableName nvarchar(257) NOT NULL
,OrderToQuery bigint NOT NULL 
,MaxLoadedDate datetime NULL 
,PRIMARY KEY (OrderToQuery)
); 
INSERT INTO @TableList (SchemaAndTableName,OrderToQuery) 
SELECT 
CONCAT(QUOTENAME(s.name),N'.', QUOTENAME(t.name)) AS SchemaAndTableName
,ROW_NUMBER() OVER(ORDER BY s.name, t.name) AS OrderToQuery
FROM 
sys.columns AS c 
INNER JOIN sys.tables AS t ON c.object_id = t.object_id 
INNER JOIN sys.schemas AS s ON t.schema_id = s.schema_id
WHERE
c.name = N'loaded_date'; 

/*declare and set some variables for loop*/
DECLARE @NumTables int = (SELECT TOP (1) OrderToQuery FROM @TableList ORDER BY OrderToQuery DESC); 
DECLARE @I int = 1;
DECLARE @CurMaxDate datetime; 
DECLARE @CurTable nvarchar(257);
DECLARE @CurQuery nvarchar(max); 
/*start loop*/
WHILE @I <= @NumTables
BEGIN 
/*build text of current query*/
SET @CurTable = (SELECT SchemaAndTableName FROM @TableList WHERE OrderToQuery = @I); 
SET @CurQuery = CONCAT(N'SELECT @MaxDateOut = MAX(loaded_date) FROM ', @CurTable, N';'); 
/*check debugging status*/ 
IF @Debug = 0
BEGIN 
BEGIN TRY 
EXEC sys.sp_executesql @stmt = @CurQuery
,@params = N'@MaxDateOut datetime OUTPUT'
,@MaxDateOut = @CurMaxDate OUTPUT; 
END TRY 
BEGIN CATCH 
DECLARE @ErrorMessage nvarchar(max) = CONCAT(
N'Error querying table ', @CurTable, N'.', NCHAR(13), NCHAR(10)
,N'Errored query: ', NCHAR(13), NCHAR(10), @CurQuery, NCHAR(13), NCHAR(10)
,N'Error message: ', ERROR_MESSAGE()
); 
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT;
/*on error end loop so error can be investigated*/
SET @I = @NumTables + 1; 
END CATCH; 
END; 
ELSE /*currently debugging*/ 
BEGIN 
PRINT(CONCAT(N'Debug output: ', @CurQuery)); 
END; 
/*update value in our table variable*/
UPDATE @TableList 
SET MaxLoadedDate = @CurMaxDate
WHERE 
OrderToQuery = @I; 
/*increment loop*/ 
SET @I = @I + 1;
END; 
SELECT 
SchemaAndTableName AS TableName
,MaxLoadedDate AS Max_Loaded_date 
FROM 
@TableList; 

我更喜欢这个解决方案，因为一次查询一个表比尝试一个大型UNION ALL查询对系统的影响小得多。同时查询一大组表可能会导致一些严重的资源信号量或锁定争用(取决于数据库的使用情况(。

它的评论相当不错，但如果有什么不清楚的地方，请告诉我。

另外，请注意，动态SQL应该作为最后手段使用。我提供这个脚本是为了回答你的问题，但你应该探索比这样更好的选择。

Answer 3

您可以使用未记录的存储过程sp_MSforeachtable。但是，不要在生产代码中使用，因为这个存储过程在未来的版本中可能不可用。

阅读sp_MSforeachtable 的详细信息

EXEC sp_MSforeachtable 'SELECT ''?'' as tablename, max(loaded_Date) FROM ?'