OBJECT_DEFINITION for table objects or Metadata underground…

SQL Server хранит информацию обо всех объектах и их свойствах в виде метаданных, доступ к которым возможен через системные представления. Кроме того, некоторые из системных представлений скрывают в себе интересные нюансы, позволяющие лучше понять как устроена DBMS.

Чтобы просмотреть тело системного преставления, как впрочем и любого другого скриптового объекта, применяют функцию – OBJECT_DEFINITION:

PRINT OBJECT_DEFINITION(OBJECT_ID('sys.objects'))

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

IF OBJECT_ID('dbo.Table1', 'U') IS NOT NULL
  DROP TABLE dbo.Table1
GO

CREATE TABLE dbo.Table1 (ColumnID INT PRIMARY KEY)
GO

EXEC sys.sp_helptext 'dbo.Table1'
SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.Table1', 'U'))

При выполнении sp_helptext мы получим ошибку:

Msg 15197, Level 16, State 1, Procedure sp_helptext, Line 107
There is no text for object 'dbo.Table1'.

При тех же условиях, системная функция OBJECT_DEFINITION вернет NULL.

Также не решит проблемы выборка из sys.sql_modules, поскольку внутри этого системного представления используется все тот же вызов функции OBJECT_DEFINITION:

CREATE VIEW sys.sql_modules AS
    SELECT object_id = o.id,
        definition = object_definition(o.id),
        ...
    FROM sys.sysschobjs o

Такое поведение весьма печально, поскольку для некоторых сценариев, бывает полезно получить скриптовое описание таблицы. Что ж, заглянем в системные представления и создадим аналог функции OBJECT_DEFINITION для работы с табличными объектами.

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

IF OBJECT_ID('dbo.WorkOut', 'U') IS NOT NULL
    DROP TABLE dbo.WorkOut
GO

CREATE TABLE dbo.WorkOut 
(
    WorkOutID BIGINT IDENTITY(1,1) NOT NULL,
    TimeSheetDate AS DATEADD(DAY, -(DAY(DateOut) - 1), DateOut),
    DateOut DATETIME NOT NULL,
    EmployeeID INT NOT NULL,
    IsMainWorkPlace BIT NOT NULL DEFAULT 1,
    DepartmentUID UNIQUEIDENTIFIER NOT NULL,
    WorkShiftCD NVARCHAR(10) NULL,
    WorkHours REAL NULL,
    AbsenceCode VARCHAR(25) NULL,
    PaymentType CHAR(2) NULL,
    CONSTRAINT PK_WorkOut PRIMARY KEY CLUSTERED (WorkOutID)
)
GO

И приступим к первому шагу – получение списка столбцов и их свойств:

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

Приведу пару примеров вместе с планами их выполнения, сделанных в dbForge Studio for SQL Server ^[1]:

--#1
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_SCHEMA = 'dbo'
    AND c.TABLE_NAME = 'WorkOut'

--#2
SELECT c.* 
FROM sys.columns c WITH(NOLOCK)
JOIN sys.tables t WITH(NOLOCK) ON c.[object_id] = t.[object_id]
JOIN sys.schemas s WITH(NOLOCK) ON t.[schema_id] = s.[schema_id]
WHERE t.name = 'WorkOut'
    AND s.name = 'dbo'

--#3
SELECT * 
FROM sys.columns c WITH(NOLOCK)
WHERE OBJECT_NAME(c.[object_id]) = 'WorkOut'
    AND OBJECT_SCHEMA_NAME(c.[object_id]) = 'dbo'

--#4
SELECT *
FROM sys.columns c WITH(NOLOCK)
WHERE c.[object_id] = OBJECT_ID('dbo.WorkOut', 'U')

Из представленных планов выполнения видно, что варианты #1 и #2 содержат избыточное количество соединений, которые увеличивают время выполнения запроса, при этом #3 подход приводит к полному сканированию индекса, что делает его наименее эффективным из всех.

С точки зрения производительности, для меня наиболее привлекательным остается #4 вариант.

Однако, данные которые содержатся sys.columns (как впрочем и в INFORMATION_SCHEMA.COLUMNS) не достаточно, чтобы полностью описать табличную структуру. Это вынуждает выполнять соединения с другими системными представлениями:

SELECT 
	  c.name
	, [type_name] = tp.name
	, type_schema_name = s.name
	, c.max_length
	, c.[precision]
	, c.scale
	, c.collation_name
	, c.is_nullable
	, c.is_identity
	, ic.seed_value
	, ic.increment_value	
	, computed_definition = cc.[definition]
	, default_definition = dc.[definition]
FROM sys.columns c WITH(NOLOCK)
JOIN sys.types tp WITH(NOLOCK) ON c.user_type_id = tp.user_type_id
JOIN sys.schemas s WITH(NOLOCK) ON tp.[schema_id] = s.[schema_id]
LEFT JOIN sys.computed_columns cc WITH(NOLOCK) ON 
		c.[object_id] = cc.[object_id] 
	AND c.column_id = cc.column_id
LEFT JOIN sys.identity_columns ic WITH(NOLOCK) ON 
		c.[object_id] = ic.[object_id] 
	AND c.column_id = ic.column_id
LEFT JOIN sys.default_constraints dc WITH(NOLOCK) ON dc.[object_id] = c.default_object_id
WHERE c.[object_id] = OBJECT_ID('dbo.WorkOut', 'U')

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

Заглянем внутрь sys.default_constraints:

ALTER VIEW sys.default_constraints AS
	SELECT name, object_id, parent_object_id,
		...
		object_definition(object_id) AS definition,
		is_system_named
	FROM sys.objects$
	WHERE type = 'D ' AND parent_object_id > 0

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

В sys.computed_columns используется все та же OBJECT_DEFINITION:

ALTER VIEW sys.computed_columns AS
	SELECT object_id = id,
		name = name,
		column_id = colid,
		system_type_id = xtype,
		user_type_id = utype,
		...
		definition = object_definition(id, colid),
		...
	FROM sys.syscolpars
	WHERE number = 0
		AND (status & 16) = 16 -- CPM_COMPUTED
		AND has_access('CO', id) = 1

Получается, что от двух соединений мы уже избавились. С sys.identity_columns ситуация более интересная:

ALTER VIEW sys.identity_columns AS
	SELECT object_id = id,
		name = name,
		column_id = colid,
		system_type_id = xtype,
		user_type_id = utype,
		...
		seed_value = IdentityProperty(id, 'SeedValue'),
		increment_value = IdentityProperty(id, 'IncrementValue'),
		last_value = IdentityProperty(id, 'LastValue'),
		...
	FROM sys.syscolpars
	WHERE number = 0 -- SOC_COLUMN
		AND (status & 4) = 4 -- CPM_IDENTCOL
		AND has_access('CO', id) = 1

Для получения информации о свойствах IDENTITY применяется недокументированная функция IDENTITYPROPERTY. В результате проверки, было установлено ее неизменное поведение на 2005 версии SQL Server и выше.

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

SELECT 
      c.name
    , [type_name] = tp.name
    , type_schema_name = s.name
    , c.max_length
    , c.[precision]
    , c.scale
    , c.collation_name
    , c.is_nullable
    , c.is_identity
    , seed_value = CASE WHEN c.is_identity = 1 THEN IDENTITYPROPERTY(c.[object_id], 'SeedValue') END
    , increment_value = CASE WHEN c.is_identity = 1 THEN IDENTITYPROPERTY(c.[object_id], 'IncrementValue') END	
    , computed_definition = OBJECT_DEFINITION(c.[object_id], c.column_id)
    , default_definition = OBJECT_DEFINITION(c.default_object_id)
FROM sys.columns c WITH(NOLOCK)
JOIN sys.types tp WITH(NOLOCK) ON c.user_type_id = tp.user_type_id
JOIN sys.schemas s WITH(NOLOCK) ON tp.[schema_id] = s.[schema_id]
WHERE c.[object_id] = OBJECT_ID('dbo.WorkOut', 'U')

Да и план выполнения станет более лояльным:

В завершение можно вместо соединения с sys.schemas делать вызов системной функции SCHEMA_NAME, которая отрабатывает заметно быстрее соединения. Это утверждение верно, при условии, если количество схем не превышает количество пользовательских объектов. А поскольку такая ситуация маловероятна — ею можно пренебречь.

Далее получим список столбцов входящих в состав первичного ключа. Самый очевидный вариант — обращение к sys.key_constraints:

SELECT 
	  pk_name = kc.name
	, column_name = c.name
	, ic.is_descending_key 
FROM sys.key_constraints kc WITH(NOLOCK)
JOIN sys.index_columns ic WITH(NOLOCK) ON 
		kc.parent_object_id = ic.object_id 
	AND ic.index_id = kc.unique_index_id
JOIN sys.columns c WITH(NOLOCK) ON 
		ic.[object_id] = c.[object_id]
	AND ic.column_id = c.column_id
WHERE kc.parent_object_id = OBJECT_ID('dbo.WorkOut', 'U')
	AND kc.[type] = 'PK'

Если вспомнить теорию, то PRIMARY KEY — это кластерный индекс и ограничение Unique.

На уровне метаданных, SQL Server для всех кластерных индексов задает index_id равный 1, поэтому можно сделать выборку из sys.indexes фильтруя по index_id = 1 либо is_primary_key = 1.

Дополнительно, что избавится от соединения с sys.columns, можно использовать системную функцию COL_NAME:

SELECT 
	  pk_name = i.name
	, column_name = COL_NAME(ic.[object_id], ic.column_id)
	, ic.is_descending_key
FROM sys.indexes i WITH(NOLOCK)
JOIN sys.index_columns ic WITH(NOLOCK) ON 
		i.[object_id] = ic.[object_id] 
	AND i.index_id = ic.index_id
WHERE i.is_primary_key = 1
	AND i.[object_id] = object_id('dbo.WorkOut', 'U')

Теперь объедим полученные выборки в одну и получим следующий запрос:

DECLARE
      @object_name SYSNAME
    , @object_id INT
    , @SQL NVARCHAR(MAX)

SELECT
      @object_name = '[' + OBJECT_SCHEMA_NAME(o.[object_id]) + '].[' + OBJECT_NAME([object_id]) + ']'
    , @object_id = [object_id]
FROM (SELECT [object_id] = OBJECT_ID('dbo.WorkOut', 'U')) o

SELECT @SQL = 'CREATE TABLE ' + @object_name + CHAR(13) + '(' + CHAR(13) + STUFF((
	SELECT CHAR(13) + '    , [' + c.name + '] ' + 
        CASE WHEN c.is_computed = 1
            THEN 'AS ' + OBJECT_DEFINITION(c.[object_id], c.column_id)
            ELSE 
				CASE WHEN c.system_type_id != c.user_type_id 
					THEN '[' + SCHEMA_NAME(tp.[schema_id]) + '].[' + tp.name + ']' 
					ELSE '[' + UPPER(tp.name) + ']' 
				END  + 
                CASE 
                    WHEN tp.name IN ('varchar', 'char', 'varbinary', 'binary')
                        THEN '(' + CASE WHEN c.max_length = -1 
										THEN 'MAX' 
										ELSE CAST(c.max_length AS VARCHAR(5)) 
									END + ')'
                    WHEN tp.name IN ('nvarchar', 'nchar')
                        THEN '(' + CASE WHEN c.max_length = -1 
										THEN 'MAX' 
										ELSE CAST(c.max_length / 2 AS VARCHAR(5)) 
									END + ')'
                    WHEN tp.name IN ('datetime2', 'time2', 'datetimeoffset') 
                        THEN '(' + CAST(c.scale AS VARCHAR(5)) + ')'
                    WHEN tp.name = 'decimal'
                        THEN '(' + CAST(c.[precision] AS VARCHAR(5)) + ',' + CAST(c.scale AS VARCHAR(5)) + ')'
                    ELSE ''
                END +
                CASE WHEN c.collation_name IS NOT NULL AND c.system_type_id = c.user_type_id 
                    THEN ' COLLATE ' + c.collation_name
                    ELSE ''
                END +
                CASE WHEN c.is_nullable = 1 
                    THEN ' NULL'
                    ELSE ' NOT NULL'
                END +
                CASE WHEN c.default_object_id != 0 
                    THEN ISNULL(' DEFAULT ' + OBJECT_DEFINITION(c.default_object_id), '') 
                    ELSE ''
                END + 
                CASE WHEN c.is_identity = 1 
                    THEN ' IDENTITY(' + CAST(IDENTITYPROPERTY(c.[object_id], 'SeedValue') AS VARCHAR(5)) + ',' + 
									CAST(IDENTITYPROPERTY(c.[object_id], 'IncrementValue') AS VARCHAR(5)) + ')' 
                    ELSE '' 
                END 
        END
    FROM sys.columns c WITH(NOLOCK)
    JOIN sys.types tp WITH(NOLOCK) ON c.user_type_id = tp.user_type_id
    WHERE c.[object_id] = @object_id
    ORDER BY c.column_id
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 7, '      ') + 
    ISNULL((SELECT '
    , CONSTRAINT [' + i.name + '] PRIMARY KEY (' + (
    SELECT STUFF(CAST((
        SELECT ', [' + COL_NAME(ic.[object_id], ic.column_id) + ']' + 
                CASE WHEN ic.is_descending_key = 1 
                    THEN ' DESC' 
                    ELSE '' 
                END
        FROM sys.index_columns ic WITH(NOLOCK)
        WHERE i.[object_id] = ic.[object_id] 
			AND i.index_id = ic.index_id 
        FOR XML PATH(N''), TYPE) AS NVARCHAR(MAX)), 1, 2, '')) + ')'
    FROM sys.indexes i WITH(NOLOCK)
    WHERE i.[object_id] = @object_id
        AND i.is_primary_key = 1), '') + CHAR(13) + ');'

PRINT @SQL

Который при выполнении будет генерировать следующий скрипт для нашей таблицы:

CREATE TABLE [dbo].[WorkOut]
(
      [WorkOutID] [BIGINT] NOT NULL IDENTITY(1,1)
    , [TimeSheetDate] AS (dateadd(day, -(datepart(day,[DateOut])-(1)),[DateOut]))
    , [DateOut] [DATETIME] NOT NULL
    , [EmployeeID] [INT] NOT NULL
    , [IsMainWorkPlace] [BIT] NOT NULL DEFAULT ((1))
    , [DepartmentUID] [UNIQUEIDENTIFIER] NOT NULL
    , [WorkShiftCD] [NVARCHAR](10) COLLATE Cyrillic_General_CI_AI NULL
    , [WorkHours] [REAL] NULL
    , [AbsenceCode] [VARCHAR](25) COLLATE Cyrillic_General_CI_AI NULL
    , [PaymentType] [CHAR](2) COLLATE Cyrillic_General_CI_AI NULL
    , CONSTRAINT [PK_WorkOut] PRIMARY KEY ([WorkOutID])
);

PS: Генерация скриптового описания таблицы разумеется не ограничивается список столбов и первичным ключем.

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

Автор: AlanDenton

Источник ^[2]