Блог

Вывод, к которому я пытаюсь добраться;

(Имя базы данных = ATT)

1. Имя таблицы
2. Имя столбца
3. МАКСИМАЛЬНАЯ дата загрузки = MAX(loaded_date) только для этого столбца

loaded_date-это столбец примерно в 50 таблицах базы данных с одинаковым именем и типом данных (Datetime).

 select * FROM sys.tables 
select * FROM syscolumns
 

Я без особого успеха изучал системные таблицы, просматривая некоторые сообщения, в которых может быть сделан динамический SQL, чего я никогда не делал.

Вы можете написать 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'
 

Запустите это, затем скопируйте все, кроме окончательного ОБЪЕДИНЕНИЯ, из окна результатов в окно запроса и запустите снова

Если бы вы хотели собрать все это в одну строку для dynamic exec, я думаю, это выглядело бы как (непроверенная) процедура, содержащая:

 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;
 

Если ваш SQLS старый и в нем нет string_agg, это немного неудобно, но в sql server есть много примеров «преобразования строк в CSV», которые выглядят так STUFF..FOR XML PATH : https://duckduckgo.com/?t=ffabamp;q=rows к CSV SQLS и ia=веб

Я написал более постоянный тип сценария, который делает это. Он возвращает результирующий набор списка таблиц в текущей базе данных с именем столбца 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 запрос. Одновременный запрос большого набора таблиц a может привести к серьезному конфликту между семафором ресурсов или блокировкой (в зависимости от использования вашей базы данных).

Это довольно хорошо прокомментировано, но дайте мне знать, если что-то не ясно.

Кроме того, просто обратите внимание, что динамический SQL следует использовать в качестве последнего средства. Я предоставил этот сценарий, чтобы ответить на ваш вопрос, но вам следует изучить лучшие варианты, чем что-то подобное.

Вы можете перейти к недокументированной хранимой процедуре sp_MSforeachtable . Но не используйте в производственном коде, так как эта хранимая процедура может быть недоступна в будущих версиях.

Подробнее читайте в sp_MSforeachtable

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