How to generate a CREATE TABLE script for an existing table

SQL Server stores information about all objects and their properties as metadata, which can be accessed through system views. In addition, some of the system views conceal interesting nuances in themselves that allow a better understanding of how a DBMS works.

To view the body of the system image, as well as of any other scripted object, use the function OBJECT_DEFINITION :

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

However, OBJECT_DEFINITION , as well as its counterpart sp_helptext , has a significant drawback - they cannot be used to return a 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 get an error:

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

Under the same conditions, the OBJECT_DEFINITION system function will return NULL .

Sampling from sys.sql_modules also does not solve the problem, because inside this system view, the same OBJECT_DEFINITION function call is used :

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

This behavior is very sad, since for some scenarios, it is useful to get a script description of the table. Well, let's take a look at the system views and create an analogue of the OBJECT_DEFINITION function to work with table objects.

To begin with, we will create a test pattern so that the process of writing the script is more visual:

 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:

In principle, a list of columns can be obtained by simply referring to one of several system views. At the same time, it is important to make a selection of the easiest system representations so that the query execution time is minimal.

I will give a couple of examples along with plans for their implementation:

 --#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') 

From the presented execution plans, it is clear that options # 1 and # 2 contain an excessive number of connections that increase the query execution time, while the # 3 approach leads to a complete index scan, which makes it the least effective of all.

In terms of performance, for me the most attractive remains the # 4 option.

However, the data contained in sys.columns (as well as in INFORMATION_SCHEMA.COLUMNS ) is not enough to completely describe the table structure. This forces connections to other system views:

 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 not look as cheerful as before. If you pay attention, then the list of columns we, in general, read three times:



Let's 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 

Inside the system view, you can see the OBJECT_DEFINITION call, respectively, to get a description of the default framework, we do not need to make a connection.

In sys.computed_columns , the same OBJECT_DEFINITION is used :

 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 

It turns out that we have already got rid of two compounds. With sys.identity_columns, the situation is more interesting:

 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 

The undocumented IDENTITYPROPERTY function is used to obtain information about the IDENTITY properties. As a result of the test, its unchanged behavior was established on the 2005 version of SQL Server and above.

As a result of calling these functions directly, the request for getting the list of columns is noticeably simpler:

 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 will become more loyal:



In conclusion, instead of connecting to sys.schemas, you can make a call to the system function SCHEMA_NAME , which performs a much faster connection. This statement is true, provided that the number of schemas does not exceed the number of user objects. And since such a situation is unlikely - it can be neglected.

Next, we obtain a list of columns included in the primary key. The most obvious option is to call 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' 

If we recall the theory, the PRIMARY KEY is a cluster index and the Unique constraint.

At the metadata level, SQL Server for all clustered indexes sets index_id to 1, so you can select from sys.indexes by filtering by is_primary_key = 1 .

Additionally, to get rid of the connection with sys.columns , you can use the system function 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') 

Now let's merge the received samples into one and get the following 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 our 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 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 CLUSTERED ([WorkOutID]) ); 

PS : The generation of the script description of the table is, of course, not limited to the list of columns and the primary key. If it is interesting, then you can continue this topic and show the generation of indexes, foreign keys and other related constructions.