SQL遍历所有表并从特定列获取最大值 [英] SQL Loop through all tables and get the max value from a specific column

问题描述

我正试图创建一个审核表，以检查该表的加载日期。

I'm trying to create an audit table that checks the loaded date for that table.

基本上，我想遍历数据库中的所有表并检查特定列- LoadedDate 并返回每个表的该列的最大值

Basically, I want to loop through all tables in the database and check for a specific column - LoadedDate and return the max value for that column for each table

SELECT TABLE_NAME
INTO #TableList 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE column_name = 'LoadedDate'


SELECT MAX(LoadedDate) FROM @TableName -- I guess using a cursor to loop through #TableList

循环到结果表中

TableName     LoadedDate  
Table 1       2016-06-01
Table 2       2016-07-01
Table 3       2016-06-01

等。

推荐答案

您可以尝试这段代码，但是会花费一些时间

You can try this code, but it will consume some time

SELECT TABLE_NAME,TABLE_SCHEMA
INTO #TableList 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE column_name = 'LoadedDate'

CREATE TABLE #TempResult (TableName VARCHAR(100), MaxDate DATETIME2)

DECLARE @TableName      VARCHAR(100)
        ,@TableSchema   VARCHAR(100)
DECLARE @SqlQuery   NVARCHAR(MAX)

WHILE(EXISTS(SELECT TOP(1) * FROM #TableList))
BEGIN
    SELECT TOP(1) @TableName = TABLE_NAME, @TableSchema = TABLE_SCHEMA FROM #TableList
    DELETE #TableList WHERE TABLE_NAME = @TableName

    SET @TableName = @TableSchema +'.'+ @TableName
    SET @SqlQuery = 'SELECT '''+@TableName+''' AS ''TableName'', MAX(UpdatedDate) AS MaxDate FROM '+ @TableName
    INSERT INTO #TempResult
    EXECUTE sp_executesql @SqlQuery
END


SELECT * from #TempResult

DROP TABLE #TableList
DROP TABLE #TempResult

这篇关于SQL遍历所有表并从特定列获取最大值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！