Поиск имен таблиц и столбцов, имеющих некоторый известный диапазон значений, с использованием сервера MS SQL

Среда: SQL Server 2016 (v13.00.4259), где у меня есть разрешения только на чтение (в основном только SELECT из нескольких таблиц).

Пример использования: я хочу найти все имена таблиц и столбцов из базы данных, где тип столбца datetime, а имя столбца содержит %END%, а любое значение (в table.column) находится между некоторым временным интервалом ( в данном случае один день).

Я могу найти все имена таблиц и столбцов из базы данных, используя это:

SELECT TABLE_NAME as tab, COLUMN_NAME as col
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'datetime' and COLUMN_NAME like '%END%';

Думаю, мне понадобится динамический запрос, потому что имена таблиц и столбцов должны быть статическими в статическом SQL-запросе, поэтому:

WITH enddates AS 
(
    SELECT TABLE_NAME AS tab, COLUMN_NAME AS col
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE DATA_TYPE = 'datetime' AND  COLUMN_NAME LIKE '%END%'
)
exec('select tab, col 
      from enddates
      where ' + tab + '.' + col + ' >= ''2019-10-21'' and ' 
        + tab + '.' + col + '< ''2019-10-22'' ')

  -- would want it to eval to:
  -- SELECT tab, col FROM enddates 
  -- WHERE $tab.$col >= '2019-10-21' AND $tab.$col < '2019-10-22'

Хотелось бы получить список имен таблиц и столбцов, где% END% datetime находится в 2019-10-21.

Но это не работает:

«Ошибка SQL [156] [S1000]: неправильный синтаксис рядом с ключевым словом 'exec'».

Одна из причин может заключаться в том, что псевдоним enddates для подзапроса не отображается внутри EXEC, но я предполагаю, что это не единственная проблема. Для ясности, я думаю, это показывает, чего я добиваюсь.

Я больше работал с PostgreSQL, но теперь мне нужно получать данные такого типа из SQL Server с доступом только для чтения, поэтому я не могу создавать там игрушечные таблицы данных для тестирования или представления. Конечно, было бы проще сначала создать таблицу из этих интересных пар таблица-столбец и избежать необходимости в подзапросе. Но все же я бы не знал, как делать динамические запросы из этих имен таблиц и столбцов тогда в SQL Server.

Изменить: еще одна более семантически правильная идея «псевдокода», использующая предыдущий подзапрос WITH:

SELECT tab, col 
FROM enddates 
WHERE EXEC('select ' + tab + '.' + col + ' >= ''2019-10-21'' and ' + tab + '.' + col + ' < ''2019-10-22''') = True

dynamic search sql-server information-schema tablename
person zimon    schedule 22.10.2019    source источник

Ответы (1)


arrow_upward
1
arrow_downward

Один из возможных подходов - создать и выполнить динамический оператор:

-- Dynamic statement
DECLARE @stm nvarchar(max) = N''

SELECT @stm = CONCAT(
    @stm,
    CASE WHEN @stm = N'' THEN N'' ELSE N' UNION ALL ' END,
    N'SELECT ''',
    QUOTENAME(TABLE_NAME),
    N''' AS Tab, ''',
    QUOTENAME(COLUMN_NAME),
    N''' AS Col, COUNT(*) AS Cnt FROM ',
    QUOTENAME(TABLE_NAME),
    N' WHERE ',
    QUOTENAME(COLUMN_NAME),
    N' >= ''20191021'' AND ',
    QUOTENAME(COLUMN_NAME),
    N' < ''20191022'' ',
    N'HAVING COUNT(*) > 0'
)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'datetime' and COLUMN_NAME LIKE '%END%'

-- Validate and execute
PRINT @stm
EXEC sp_executesql @stm
person Zhorov    schedule 22.10.2019
comment
Говорит: Произошла ошибка при выполнении запроса SQL. Причина: Ошибка SQL [137] [S1000]: необходимо объявить скалярную переменную @stm. Хотя @stm ОБЪЯВЛЯЕТСЯ с самого начала. Может быть, какая-то цитируемая цитата или чего-то не хватает. (Трудно читать код, но со временем попытаюсь его проанализировать.) Ваш предыдущий подход (который удален) было легче читать и вроде как помог мне найти то, что я искал, но только за счет исключения верхних пределов . - person zimon; 22.10.2019
comment
@zimon DECLARE @stm nvarchar(max) = N'' - первая строка в заявлении. И, конечно же, проверяется все высказывание. Можете еще раз проверить исполнение? Спасибо. - person Zhorov; 22.10.2019
comment
Я дважды / трижды проверил, что копирование + вставка ничего не пропустили и не добавили. Тем не менее, он дает ту же ошибку, утверждая, что @stm не объявлен. Я пытаюсь найти причину. Может быть, это более старая версия SQL Server? - person zimon; 22.10.2019
comment
@zimon Я обновил ответ с небольшой поправкой. Но он работает с моей тестовой базой данных. И я не думаю, что здесь важна версия. Спасибо еще раз. - person Zhorov; 22.10.2019
comment
Обнаружил ошибку (которая как минимум мешала работе на этом SQL Server). Если оставить точку с запятой (;) после "% END%", все заработало. БЛАГОДАРНОСТЬ! - person zimon; 22.10.2019