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 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: Генерация скриптового описания таблицы разумеется не ограничивается список столбов и первичным ключем.

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