DECLARE @table_name SYSNAME
SELECT @table_name = 'dbo.bo_user_master'
DECLARE
@object_name SYSNAME
, @object_id INT
SELECT
@object_name = '[' + s.name + '].[' + o.name + ']'
, @object_id = o.[object_id]
FROM sys.objects o WITH (NOWAIT)
JOIN sys.schemas s WITH (NOWAIT) ON o.[schema_id] = s.[schema_id]
WHERE s.name + '.' + o.name = @table_name
AND o.[type] = 'U'
AND o.is_ms_shipped = 0
DECLARE @SQL NVARCHAR(MAX) = ''
;WITH index_column AS
(
SELECT
ic.[object_id]
, ic.index_id
, ic.is_descending_key
, ic.is_included_column
, c.name
FROM sys.index_columns ic WITH (NOWAIT)
JOIN sys.columns c WITH (NOWAIT) ON ic.[object_id] = c.[object_id] AND ic.column_id = c.column_id
WHERE ic.[object_id] = @object_id
),
fk_columns AS
(
SELECT
k.constraint_object_id
, cname = c.name
, rcname = rc.name
FROM sys.foreign_key_columns k WITH (NOWAIT)
JOIN sys.columns rc WITH (NOWAIT) ON rc.[object_id] = k.referenced_object_id AND rc.column_id = k.referenced_column_id
JOIN sys.columns c WITH (NOWAIT) ON c.[object_id] = k.parent_object_id AND c.column_id = k.parent_column_id
WHERE k.parent_object_id = @object_id
)
SELECT @SQL = 'CREATE TABLE ' + @object_name + CHAR(13) + '(' + CHAR(13) + STUFF((
SELECT CHAR(9) + ', [' + c.name + '] ' +
CASE WHEN c.is_computed = 1
THEN 'AS ' + cc.[definition]
ELSE UPPER(tp.name) +
CASE WHEN tp.name IN ('varchar', 'char', 'varbinary', 'binary', 'text')
THEN '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length AS VARCHAR(5)) END + ')'
WHEN tp.name IN ('nvarchar', 'nchar', 'ntext')
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 THEN ' COLLATE ' + c.collation_name ELSE '' END +
CASE WHEN c.is_nullable = 1 THEN ' NULL' ELSE ' NOT NULL' END +
CASE WHEN dc.[definition] IS NOT NULL THEN ' DEFAULT' + dc.[definition] ELSE '' END +
CASE WHEN ic.is_identity = 1 THEN ' IDENTITY(' + CAST(ISNULL(ic.seed_value, '0') AS CHAR(1)) + ',' + CAST(ISNULL(ic.increment_value, '1') AS CHAR(1)) + ')' ELSE '' END
END + CHAR(13)
FROM sys.columns c WITH (NOWAIT)
JOIN sys.types tp WITH (NOWAIT) ON c.user_type_id = tp.user_type_id
LEFT JOIN sys.computed_columns cc WITH (NOWAIT) ON c.[object_id] = cc.[object_id] AND c.column_id = cc.column_id
LEFT JOIN sys.default_constraints dc WITH (NOWAIT) ON c.default_object_id != 0 AND c.[object_id] = dc.parent_object_id AND c.column_id = dc.parent_column_id
LEFT JOIN sys.identity_columns ic WITH (NOWAIT) ON c.is_identity = 1 AND c.[object_id] = ic.[object_id] AND c.column_id = ic.column_id
WHERE c.[object_id] = @object_id
ORDER BY c.column_id
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, CHAR(9) + ' ')
+ ISNULL((SELECT CHAR(9) + ', CONSTRAINT [' + k.name + '] PRIMARY KEY (' +
(SELECT STUFF((
SELECT ', [' + c.name + '] ' + CASE WHEN ic.is_descending_key = 1 THEN 'DESC' ELSE 'ASC' END
FROM sys.index_columns ic WITH (NOWAIT)
JOIN sys.columns c WITH (NOWAIT) ON c.[object_id] = ic.[object_id] AND c.column_id = ic.column_id
WHERE ic.is_included_column = 0
AND ic.[object_id] = k.parent_object_id
AND ic.index_id = k.unique_index_id
FOR XML PATH(N''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, ''))
+ ')' + CHAR(13)
FROM sys.key_constraints k WITH (NOWAIT)
WHERE k.parent_object_id = @object_id
AND k.[type] = 'PK'), '') + ')' + CHAR(13)
+ ISNULL((SELECT (
SELECT CHAR(13) +
'ALTER TABLE ' + @object_name + ' WITH'
+ CASE WHEN fk.is_not_trusted = 1
THEN ' NOCHECK'
ELSE ' CHECK'
END +
' ADD CONSTRAINT [' + fk.name + '] FOREIGN KEY('
+ STUFF((
SELECT ', [' + k.cname + ']'
FROM fk_columns k
WHERE k.constraint_object_id = fk.[object_id]
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
+ ')' +
' REFERENCES [' + SCHEMA_NAME(ro.[schema_id]) + '].[' + ro.name + '] ('
+ STUFF((
SELECT ', [' + k.rcname + ']'
FROM fk_columns k
WHERE k.constraint_object_id = fk.[object_id]
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
+ ')'
+ CASE
WHEN fk.delete_referential_action = 1 THEN ' ON DELETE CASCADE'
WHEN fk.delete_referential_action = 2 THEN ' ON DELETE SET NULL'
WHEN fk.delete_referential_action = 3 THEN ' ON DELETE SET DEFAULT'
ELSE ''
END
+ CASE
WHEN fk.update_referential_action = 1 THEN ' ON UPDATE CASCADE'
WHEN fk.update_referential_action = 2 THEN ' ON UPDATE SET NULL'
WHEN fk.update_referential_action = 3 THEN ' ON UPDATE SET DEFAULT'
ELSE ''
END
+ CHAR(13) + 'ALTER TABLE ' + @object_name + ' CHECK CONSTRAINT [' + fk.name + ']' + CHAR(13)
FROM sys.foreign_keys fk WITH (NOWAIT)
JOIN sys.objects ro WITH (NOWAIT) ON ro.[object_id] = fk.referenced_object_id
WHERE fk.parent_object_id = @object_id
FOR XML PATH(N''), TYPE).value('.', 'NVARCHAR(MAX)')), '')
+ ISNULL(((SELECT
CHAR(13) + 'CREATE' + CASE WHEN i.is_unique = 1 THEN ' UNIQUE' ELSE '' END
+ ' NONCLUSTERED INDEX [' + i.name + '] ON ' + @object_name + ' (' +
STUFF((
SELECT ', [' + c.name + ']' + CASE WHEN c.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END
FROM index_column c
WHERE c.is_included_column = 0
AND c.index_id = i.index_id
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + ')'
+ ISNULL(CHAR(13) + 'INCLUDE (' +
STUFF((
SELECT ', [' + c.name + ']'
FROM index_column c
WHERE c.is_included_column = 1
AND c.index_id = i.index_id
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + ')', '') + CHAR(13)
FROM sys.indexes i WITH (NOWAIT)
WHERE i.[object_id] = @object_id
AND i.is_primary_key = 0
AND i.[type] = 2
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
), '')
PRINT @SQL
----------------------------------------------------------------------
SQL Server stores information about all objects and their properties as metadata that can be accessed through system views. In addition, some of the system views hold interesting nuances that can help to better understand how a DBMS works.
To see the system view body, just as for any other script object, the OBJECT_DEFINITION function is used:
- PRINT OBJECT_DEFINITION(OBJECT_ID('sys.objects'))
However, OBJECT_DEFINITION, as well as its analogue sp_helptext, has a significant disadvantage; it does not allow the return script description for a table object.
- 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'))
When executing sp_helptext, we will get the following error:
Msg 15197, Level 16, State 1, Procedure sp_helptext, Line 107
There is no text for object 'dbo.Table1'.
There is no text for object 'dbo.Table1'.
Under the same conditions, a system function OBJECT_DEFINITION returns NULL.
Fetching from sys.sql_modules will also not solve the problem, since the same old OBJECT_DEFINITION function call is used inside this system view:
- CREATE VIEW sys.sql_modules AS
- SELECT object_id = o.id,
- definition = object_definition(o.id),
- ...
- FROM sys.sysschobjs o
Such behavior is rather disappointing. Sometimes it is neecessary to retrieve a script description of a table for some scripts. Well, let’s look at system views and create an OBJECT_DEFINITIONfunction analogue for working with table objects.
To start, let's create a test table, in order for the process of script writing to be more clear:
To start, let's create a test table, in order for the process of script writing to be more clear:
- 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
And proceed to the first step, getting a list of columns and their properties.
Essentially, the list of columns can be obtained by simply referencing one of the several system views. Thus, it is important to fetch from the simplest system views, in order for the query execution time to be minimal.
Essentially, the list of columns can be obtained by simply referencing one of the several system views. Thus, it is important to fetch from the simplest system views, in order for the query execution time to be minimal.
- --#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')
The presented plans show that the #1 and #2 approaches contain excessive amount of connections that will increase the query execution time, while the #3 approach leads to the complete scan of the index, making it the least efficient of all.
In terms of performance, the #4 approach remains the most attractive to me.
However, data contained in sys.columns (as well as in INFORMATION_SCHEMA.COLUMNS) is not enough to completely describe the table structure. This forces joins to other system views to be established as in the following:
- 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')
Accordingly, the execution plan will look not so optimistic, as before. Note that the column list is even read out 3 times:
Have a look inside 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
There is an OBJECT_DEFINITION call inside the system view. So, to retrieve the description of the default constraint, we don’t need to establish joining.
OBJECT_DEFINITION is still used in sys.computed_columns:
OBJECT_DEFINITION is still used in sys.computed_columns:
- 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
We seem to have already avoided 2 joins. The case with sys.identity_columns is more curious:
- 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
To retrieve information about IDENTITY properties, an undocumented property IDENTITYPROPERTYis used. After a check, its unchanging behavior on SQL Server 2005 and higher was ascertained.
As a result of calling these functions directly, the column list obtaining query becomes significantly simplified:
As a result of calling these functions directly, the column list obtaining query becomes significantly simplified:
- 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')
And the execution plan becomes more efficient:
Finally, instead of joining to sys.schemas, the SCHEMA_NAME system function can be called, that triggers much more faster than JOIN. This is true, provided that the number of schemes does not exceed the number of user objects. And since such a situation is unlikely, it can be neglected.
Next, get a list of columns included in the primary key. The most obvious approachis to usesys.key_constraints:
Next, get a list of columns included in the primary key. The most obvious approachis to usesys.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'
In most cases, PRIMARY KEY is a clustered index and the Unique constraint.
At the metadata level, SQL Server sets index_id to 1 for all clustered indexes, so we can make a selection from sys.indexes filtering by is_primary_key = 1 or by index_id = 1 (not recommended).
Additionally, to avoid joining to sys.columns, the COL_NAME system function can be used:
Additionally, to avoid joining to sys.columns, the COL_NAME system function can be used:
- 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')
Now combine the obtained queries into one query to get the following final query:
- 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 ' CONSTRAINT [' + OBJECT_NAME(c.default_object_id) + ']' +
- ' DEFAULT ' + OBJECT_DEFINITION(c.default_object_id)
- ELSE ''
- END +
- CASE WHEN cc.[object_id] IS NOT NULL
- THEN ' CONSTRAINT [' + cc.name + '] CHECK ' + cc.[definition]
- 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
- LEFT JOIN sys.check_constraints cc WITH(NOLOCK)
- ON c.[object_id] = cc.parent_object_id
- AND cc.parent_column_id = c.column_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 ' +
- CASE WHEN i.index_id = 1
- THEN 'CLUSTERED'
- ELSE 'NONCLUSTERED'
- END +' (' + (
- 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
Which, when executed, will generate the following script for the test table:
- 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 CONSTRAINT [DF__WorkOut__IsMainW__52442E1F] DEFAULT ((1))
- , [DepartmentUID] [UNIQUEIDENTIFIER] NOT NULL
- , [WorkShiftCD] [NVARCHAR](10) COLLATE Cyrillic_General_CI_AS NULL
- , [WorkHours] [REAL] NULL
- , [AbsenceCode] [VARCHAR](25) COLLATE Cyrillic_General_CI_AS NULL
- , [PaymentType] [CHAR](2) COLLATE Cyrillic_General_CI_AS NULL
- , CONSTRAINT [PK_WorkOut] PRIMARY KEY CLUSTERED ([WorkOutID])
- );
As you can see, the topic is too broad and it is not limited to a column list and primary key.
That's why generation of indexes, foreign keys and other statements are planned to be revealed in the next part of this topic.
No comments:
Post a Comment