Блог

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

 WITH src AS 
(
  SELECT [procedure] = QUOTENAME(s.name)   N'.'   QUOTENAME(o.name),
      ref = 
      COALESCE(QUOTENAME(d.referenced_server_name)     N'.', N'')
      QUOTENAME(d.referenced_schema_name)   N'.'
      QUOTENAME(d.referenced_entity_name),
                d.referenced_database_name
  FROM sys.sql_expression_dependencies AS d
  INNER JOIN sys.objects AS o
  ON d.referencing_id = o.[object_id]
  INNER JOIN sys.schemas AS s
  ON o.[schema_id] = s.[schema_id]
  WHERE o.[type] = N'P'
)
SELECT [procedure],ref, referenced_database_name
FROM src
GROUP BY [procedure],ref,referenced_database_name;
  

Используя этот запрос, если процедура выглядит примерно так:

 Select * from A where date=(select max(date) from B)
  

Я бы получил только таблицу A, но мне также нужна таблица B для отображения.
Кажется, что все SQL-запросы вокруг собирают только данные, которые фактически интегрируются в окончательные инструкции select, но ссылки, такие как «где», «в», «не в», coalesce и т.д., оставлены в стороне.
Может быть, это немного сыро, но мне просто интересно, как я могу сделать, чтобы убедиться, что если таблица просто упоминается каким-либо образом, я могу ее получить.
Я ничего не смог найти, также пытался с помощью синтаксического анализатора python SQL, но без каких-либо результатов вообще.

вот более современный способ:

 SELECT DISTINCT p.name AS proc_name, t.name AS table_name
FROM sys.sql_dependencies d 
INNER JOIN sys.procedures p ON p.object_id = d.object_id
INNER JOIN sys.tables     t ON t.object_id = d.referenced_major_id
ORDER BY proc_name, table_name
  

более привлекательный подход:

 SELECT DISTINCT 
      [object_name] = SCHEMA_NAME(o.[schema_id])   '.'   o.name
    , o.type_desc
FROM sys.dm_sql_referenced_entities ('dbo.usp_test1', 'OBJECT') d
JOIN sys.objects o ON d.referenced_id = o.[object_id]
WHERE o.[type] IN ('U', 'V')
  

Технически sys.objects устарел, поэтому в будущем YMMV.

Я запустил оба запроса из OP и других ответов, но ни один из них не предоставил все таблицы. Исходный запрос OP предоставил 12 454 таблицы в одной базе данных, а первый запрос Джона предоставил 2921 таблицу. Произошло некоторое перекрытие, и с помощью приведенного ниже пересмотренного запроса были предоставлены все 13 255 таблиц.

 SELECT DISTINCT *
FROM
(
    SELECT
        S.name   '.'   O.name AS 'Procedure',
        D.referenced_database_name AS 'Database',
        D.referenced_schema_name AS 'Schema',
        D.referenced_entity_name AS 'Table'
    FROM sys.sql_expression_dependencies D
    JOIN sys.objects O ON O.object_id = D.referencing_id
    JOIN sys.schemas S ON S.schema_id = O.schema_id
    WHERE O.type = N'P'

    UNION ALL

    SELECT DISTINCT
        SCHEMA_NAME(P.SCHEMA_ID)   '.'   P.name AS 'Procedure',
        DB_Name() AS 'Database',
        SCHEMA_NAME(T.SCHEMA_ID) AS 'Schema',
        T.name AS 'Table'
    FROM sys.sql_dependencies D
    JOIN sys.procedures P ON P.object_id = D.object_id
    JOIN sys.tables T ON T.object_id = D.referenced_major_id
) T