A SQL Table Function to Script Another Table

ScriptTable_TF is a table-valued function for Microsoft SQL Server that generates a T-SQL script for recreating an existing table in another database. Instead of scripting the table by hand or through SQL Server Management Studio, you call the function with the table name and the target database name, and it returns the script as rows that can be read, copied and executed.

What the function does

The function reads the table structure with sys.dm_exec_describe_first_result_set and joins it with the system views sys.indexes, sys.index_columns, sys.computed_columns and sys.default_constraints. For every column of the source table it returns one row with three columns:

• column_ordinal – the order of the statement in the script,
• sql – the T-SQL statement,
• cname – the column name.

The first column is created with CREATE TABLE, and every following column is added with ALTER TABLE ... ADD. For each column the script keeps:

• the data type,
• the collation, when the column has one,
• NULL or NOT NULL,
• IDENTITY(1,1) for identity columns,
• the definition of computed columns,
• the primary key, as a NONCLUSTERED constraint named PK_table_column,
• the default value, as a constraint named DF_table_column.

The first and the last row of the result wrap the whole script in a transaction with SET XACT_ABORT ON and a TRY...CATCH block. If any statement fails, the table creation is rolled back completely.

Parameters

• @TABLE_NAME – the name of the source table in the dbo schema of the current database,
• @ScriptForDb – the name of the database the script is generated for.

Constraint names are written as delimited identifiers in double quotes, so they may contain any character that the table or column name contains. The script therefore has to be executed with SET QUOTED_IDENTIFIER ON, which is the default in SQL Server Management Studio.

Function code

The code starts with ALTER FUNCTION; on the first installation replace it with CREATE FUNCTION.

ALTER FUNCTION [dbo].[ScriptTable_TF](
    @TABLE_NAME nvarchar(127),
	@ScriptForDb nvarchar(127))
returns @t table(column_ordinal int, sql nvarchar(4000), cname nvarchar(127))
as
begin

insert into @t(column_ordinal, sql, cname) values(0, 'SET XACT_ABORT ON;  BEGIN TRAN  BEGIN TRY', '')

;WITH A AS
(
SELECT  
        c.is_identity_column
	  , c.column_ordinal
	  , c.name
      , c.is_nullable
	  , c.system_type_name
	  , c.collation_name
	  , c.is_xml_document
	  , c.is_part_of_unique_key
	  , c.is_computed_column
	  , cc.definition computed_definition
	  , dc.definition default_constraint
	  , (SELECT  sc.name AS ColumnName
		FROM    sys.indexes AS i INNER JOIN 
				sys.index_columns AS ic ON  i.OBJECT_ID = ic.OBJECT_ID
										AND i.index_id = ic.index_id JOIN
	            sys.columns sc on ic.column_id = sc.column_id and ic.object_id = sc.object_id
		WHERE   i.is_primary_key = 1
			AND OBJECT_NAME(I.object_id) = @TABLE_NAME
			AND sc.name collate Latin1_General_CI_AS  = c.name collate Latin1_General_CI_AS 
			) pk
FROM sys.dm_exec_describe_first_result_set('select * from [dbo].[' + @TABLE_NAME +']', NULL, 0) c left JOIN
    sys.computed_columns cc on OBJECT_NAME(cc.object_id) = @TABLE_NAME and cc.name collate Latin1_General_CI_AS = c.name collate Latin1_General_CI_AS left join
	sys.default_constraints dc on OBJECT_NAME(parent_object_id) = @TABLE_NAME and c.name = COL_NAME(parent_object_id, parent_column_id)
)
insert into @t(column_ordinal, sql, cname)
SELECT TOP 100 PERCENT column_ordinal, 
CASE WHEN column_ordinal = 1 THEN 
	'CREATE TABLE "' + @ScriptForDb + '".dbo."' + @TABLE_NAME + '" ("' + NAME + '"'  
ELSE 
	'ALTER TABLE "' + @ScriptForDb + '".dbo."' + @TABLE_NAME + '" ADD "' + NAME + '"'
END +
CASE WHEN is_computed_column = 1 THEN 
	' AS ' + computed_definition COLLATE Latin1_General_CI_AS
ELSE 
	' ' + system_type_name + 
	CASE WHEN collation_name IS NOT NULL THEN 
		' COLLATE ' + collation_name 
	ELSE
		''
	END +
	CASE WHEN is_nullable = 0 THEN 
		' NOT' 
	ELSE
		''
	END + 
	' NULL'+
	CASE WHEN is_identity_column = 1 THEN
		' IDENTITY(1,1)'
	ELSE
		''
	END 
END +
CASE WHEN column_ordinal = 1 THEN 
		')' 
ELSE 
		''
END +
CASE WHEN pk IS NOT NULL THEN 
		';ALTER TABLE "' + @ScriptForDb + '"."dbo"."' + @TABLE_NAME + '" ADD CONSTRAINT "PK_' + @TABLE_NAME + '_' + NAME + '" PRIMARY KEY NONCLUSTERED ("' + NAME + '") '
     WHEN default_constraint IS NOT NULL THEN 
		';ALTER TABLE "' + @ScriptForDb + '"."dbo"."' + @TABLE_NAME + '" ADD CONSTRAINT "DF_' + @TABLE_NAME + '_' + NAME + '" DEFAULT ' + default_constraint + ' FOR "' + NAME + '"'
	ELSE
		''
END
SQL, NAME
FROM A
ORDER BY column_ordinal

insert into @t(column_ordinal, sql, cname) values((select count(*) from @t) + 1, 'END TRY BEGIN CATCH IF (XACT_STATE()) = -1 BEGIN ROLLBACK TRAN; THROW; END END CATCH; IF (XACT_STATE()) = 1  COMMIT TRAN;', '')

return 
end
  

Usage example

The rows have to be read in the order of column_ordinal:

SELECT sql
FROM dbo.ScriptTable_TF('Customers', 'TargetDb')
ORDER BY column_ordinal

The resulting rows are copied, in that order, into a new query window and executed on the server that holds the target database. Since the table name in the script is qualified with the database name, the script can be run from any database on the same server.

Limitations

• only tables in the dbo schema are supported,
• foreign keys, indexes other than the primary key, CHECK constraints and triggers are not scripted,
• the primary key is always created as NONCLUSTERED and per column, so a table with a composite primary key needs manual adjustment,
• IDENTITY always gets seed and increment 1,1, regardless of the original values,
• only the structure is scripted, not the data.