SQL

SQL Server: определяем базовые столбцы представления

Время прочтения: 4 мин.

Представления (view) базы данных SQL Server имеют ряд особенностей, одна из которых – выполнение сложных по структуре запросов без необходимости знания схемы базы данных. Иными словами, единожды создав представление (вот тут, конечно, знание схемы БД автору представления потребуется), в дальнейшем, при запросе представления не нужно запоминать. Например, все имена таблиц и столбцов, участвующих в сохранении результатов запроса (виртуальной таблицы) представления.

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

Причем, если воспользоваться системной хранимой процедурой sp_help, то можно получить информацию о том, какие столбцы возвращает представление, но мы не получим информации — на какие базовые таблицы и столбцы этих таблиц имеются ссылки в представлении.

В этой статье я приведу пример того, какие способы можно применить для получения базовых таблиц и их столбцов, использованных при создании представления, задействовав функцию sys.dm_exec_describe_first_result_set.

Возвратим с ее помощью метаданные о наборе результатов при запросе представления. Получим информацию о базовой таблице и столбцах в виде списка, в котором базовые столбцы указаны в отдельной строке (в примерах использована общедоступная тестовая БД AdventureWorks). В дальнейшем для уточнения запроса можно исключить те столбцы, которые присутствуют в представлении, но не относятся к конкретному запросу.

SELECT 
    CONCAT(
        source_server + '.', 
        source_database + '.', 
        source_schema + '.', 
        source_table + '.', 
        source_column) AS [сolumnOnSource],
    name AS [columnOnView],
    user_type_name,
    system_type_name,
    max_length,
    [precision],
    scale
FROM sys.dm_exec_describe_first_result_set(
    N'SELECT * FROM HumanResources.vEmployeeDepartment',
    NULL, 
    1
);

Фрагмент результата вывода такой:

Видим, что базовые столбцы и столбцы, возвращаемые представлением, имеют одно и тоже имя. Когда в представлении применяются алиасы столбцов, отличающиеся от имен  базовых столбцов, то это может повлиять на вывод результата выполнения запроса. Вот такой характерный пример с представлением vStateProvinceCountryRegion:

SELECT 
    CONCAT(
        source_server + '.', 
        source_database + '.', 
        source_schema + '.', 
        source_table + '.', 
        source_column) AS [сolumnOnSource],
    name AS [columnOnView],
    user_type_name,
    system_type_name,
    max_length,
    [precision],
    scale
FROM sys.dm_exec_describe_first_result_set(
    N'SELECT * FROM Person.vStateProvinceCountryRegion',
    NULL,
    1
);

Вывод результата:

Обратите внимание на то, что есть отличия в названиях базовых столбцов от названий столбцов представления.

Надо заметить, что функция sys.dm_exec_describe_first_result_set описывает набор результатов, а не представление. Иными словами, результат зависит и от передаваемого запроса, а не только от самого рассматриваемого представления. Ниже приведен пример запроса, в котором выбирается только один необходимый столбец BusinessEntityID вместо всех столбцов (*).

SELECT 
    CONCAT(
        source_server + '.', 
        source_database + '.', 
        source_schema + '.', 
        source_table + '.', 
        source_column) AS [сolumnOnSource],
    name AS [columnOnView],
    user_type_name,
    system_type_name,
    max_length,
    [precision],
    scale
FROM sys.dm_exec_describe_first_result_set(
    N'SELECT BusinessEntityID FROM HumanResources.vEmployeeDepartment',
    NULL, 
    1
);

Вывод:

В результате, видим только семь столбцов в выводе этого запроса.

В другом примере покажу, как можно получить базовые столбцы более чем из одного представления:

SELECT 
    CONCAT(
        source_server + '.', 
        source_database + '.', 
        source_schema + '.', 
        source_table + '.', 
        source_column) AS [сolumnOnSource],
    name AS [columnOnView],
    user_type_name,
    system_type_name,
    max_length,
    [precision],
    scale
FROM sys.dm_exec_describe_first_result_set(
    N'SELECT * FROM HumanResources.vEmployeeDepartment t1
      INNER JOIN HumanResources.vEmployee t2
	    ON t1.BusinessEntityID = t2.BusinessEntityID',
    NULL, 
    1
);

Фрагмент вывода результата:

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

Советуем почитать