sp_GenerateTableDDLScript.sql

use [master]
go
IF OBJECT_ID('dbo.sp_GenerateTableDDLScript') IS NOT NULL DROP PROCEDURE dbo.sp_GenerateTableDDLScript
GO
/*
---------------------------------------------------------------------------
sp_GenerateTableDDLScript version 1.0 by Eitan Blumin
https://github.com/EitanBlumin/sp_GenerateTableDDLScript
---------------------------------------------------------------------------
Copyright 2019 by Eitan Blumin at https://www.eitanblumin.com all rights reserved
---------------------------------------------------------------------------
Purpose:
--------------------------------------
	This procedure can be used to generate a CREATE TABLE script for a given table.
	You may create this procedure in the master database, and use the following
	command to turn it into a system stored procedure, usable anywhere in the instance:
	
	EXECUTE sp_MS_marksystemobject 'sp_GenerateTableDDLScript'

---------------------------------------------------------------------------
License:
--------------------------------------
@TableName SYSNAME,					-- The name of the source table. This parameter is mandatory.
									-- If the table's schema is not default (dbo), then please specify the 
									-- schema name as well as part of the parameter.
@NewTableName SYSNAME = NULL,		-- The name of the new (target) table. You may also include database and schema as part of the name.
									-- If not specified, same name as source table will be used.
@Result NVARCHAR(MAX) OUTPUT,		-- Output textual parameter that will contain the result TSQL command for creating the table.
@IncludeDefaults BIT = 1,			-- Set whether to include default constraints
@IncludeCheckConstraints BIT = 1,	-- Set whether to include check constraints
@IncludeForeignKeys BIT = 1,		-- Set whether to include foreign key constraints
@IncludeIndexes BIT = 1,			-- Set whether to include indexes
@IncludePrimaryKey BIT = 1,			-- Set whether to include primary key constraints
@IncludeIdentity BIT = 1,			-- Set whether to include identity property
@IncludeUniqueIndexes BIT = 1,		-- Set whether to include unique index constraints
@IncludeComputedColumns BIT = 1,	-- Set whether to include computed columns (if not, they will also be automatically ignored by constraints and indexes)
@UseSystemDataTypes BIT = 0,		-- Set whether to use system data type names instead of user data type names
@ConstraintsNameAppend SYSNAME = '',-- This is an optional text string to append to constraint names, 
									-- in order to avoid the duplicate object name exception.
									-- This is useful when creating the new table within the same database.
@Verbose BIT = 0					-- Optional parameter. If set to 1, will display informative messages, and will output a table representing the table fields

---------------------------------------------------------------------------
Example Usages:
--------------------------------------
	-- Example use case 1: Creating a table in an archive database, without foreign keys and identity property:
	DECLARE @CMD NVARCHAR(MAX)
	EXEC sp_GenerateTableDDLScript 'Sales.OrderDetails', 'ArchiveDB.Sales.OrderDetails', @CMD OUTPUT, @IncludeForeignKeys = 0, @IncludeIdentity = 0
	SELECT @CMD

	-- Example use case 2: Duplicating a table within the same database;
	DECLARE @CMD NVARCHAR(MAX)
	EXEC sp_GenerateTableDDLScript 'Sales.OrderDetails', 'Sales.OrderDetails_New', @CMD OUTPUT, @ConstraintsNameAppend = '_New'
	SELECT @CMD

	-- Example use case 3: Duplicating a table as a temporary table, without computed columns:
	DECLARE @CMD NVARCHAR(MAX)
	EXEC sp_GenerateTableDDLScript 'Sales.OrderDetails', '#temp_OrderDetails', @CMD OUTPUT, @ConstraintsNameAppend = '_Temp', @IncludeComputedColumns = 0
	SELECT @CMD

---------------------------------------------------------------------------
Remarks:
--------------------------------------
- The source table must exist, otherwise an exception will be raised.
- The script does not check whether the target table already exists,
  it falls on you to make sure that it doesn't before running the result script.
- The script does not check whether constraint names already exist,
  it falls on you to use the @ConstraintsNameAppend parameter to generate unique names.
- The script (at the moment) does NOT support the following:
	- Column Sets
	- Collations different from Database Default
	- Filestream columns
	- Sparse columns
	- Not for replication property
	- XML document collections
	- Rule objects
	- Non-default Filegroups
	- In-Memory tables

---------------------------------------------------------------------------
Acknowledgements:
--------------------------------------
	The script is mainly based off of the sp_ScriptTable stored procedure
	originally published by Tim Chapman in this URL:
	https://www.techrepublic.com/blog/the-enterprise-cloud/script-table-definitions-using-tsql/

---------------------------------------------------------------------------
Version History:
--------------------------------------
2019-04-17: First publication
2019-07-09: Fixed bug returning -1 character length for MAX length columns
2019-07-09: Added optional @Verbose parameter
---------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[sp_GenerateTableDDLScript]
(
@TableName NVARCHAR(500),
@NewTableName SYSNAME = NULL,
@Result NVARCHAR(MAX) OUTPUT,
@IncludeDefaults BIT = 1,
@IncludeCheckConstraints BIT = 1,
@IncludeForeignKeys BIT = 1,
@IncludeIndexes BIT = 1,
@IncludePrimaryKey BIT = 1,
@IncludeIdentity BIT = 1,
@IncludeUniqueIndexes BIT = 1,
@IncludeComputedColumns BIT = 1,
@UseSystemDataTypes BIT = 0,
@ConstraintsNameAppend SYSNAME = '',
@Verbose BIT = 0
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @MainDefinition TABLE
(
FieldValue NVARCHAR(4000)
)

DECLARE @TableObjId INT
DECLARE @ClusteredPK BIT
DECLARE @TableSchema NVARCHAR(255)
DECLARE @RCount INT

SELECT @TableName = name, @TableObjId = id, @TableSchema = OBJECT_SCHEMA_NAME(id) FROM sysobjects WHERE id = OBJECT_ID(@TableName);

IF @TableObjId IS NULL
BEGIN
	RAISERROR(N'Table %s not found within current database!', 16, 1, @TableName);
	RETURN -1;
END

SET @NewTableName = ISNULL(@NewTableName, QUOTENAME(DB_NAME(DB_ID())) + '.' + QUOTENAME(@TableSchema) + '.' + @TableName);

DECLARE @ShowFields TABLE
(
FieldID INT IDENTITY(1,1),
DatabaseName SYSNAME,
TableOwner SYSNAME,
TableName SYSNAME,
FieldName SYSNAME,
ColumnPosition INT,
ColumnDefaultValue NVARCHAR(1000),
ColumnDefaultName SYSNAME NULL,
IsNullable BIT,
DataType SYSNAME,
MaxLength INT,
NumericPrecision INT,
NumericScale INT,
DomainName SYSNAME NULL,
FieldListingName NVARCHAR(300),
FieldDefinition NVARCHAR(4000),
IdentityColumn BIT,
IdentitySeed INT,
IdentityIncrement INT,
IsCharColumn BIT
) 

DECLARE @HoldingArea TABLE
(
FldID SMALLINT IDENTITY(1,1),
Flds VARCHAR(4000),
FldValue CHAR(1) DEFAULT(0)
)

DECLARE @PKObjectID TABLE
(
ObjectID INT
)

DECLARE @Uniques TABLE
(
ObjectID INT
) 

DECLARE @HoldingAreaValues TABLE
(
FldID SMALLINT IDENTITY(1,1),
Flds VARCHAR(4000),
FldValue CHAR(1) DEFAULT(0)
)

DECLARE @Definition TABLE
(
DefinitionID SMALLINT IDENTITY(1,1),
FieldValue NVARCHAR(4000)
)

INSERT INTO @ShowFields
(
DatabaseName,
TableOwner,
TableName,
FieldName,
ColumnPosition,
ColumnDefaultValue,
ColumnDefaultName,
IsNullable,
DataType,
MaxLength,
NumericPrecision,
NumericScale,
DomainName,
FieldListingName,
FieldDefinition,
IdentityColumn,
IdentitySeed,
IdentityIncrement,
IsCharColumn
) 
SELECT
DB_NAME(),
TABLE_SCHEMA,
TABLE_NAME,
COLUMN_NAME,
CAST(ORDINAL_POSITION AS INT),
COLUMN_DEFAULT,
dobj.name AS ColumnDefaultName,
CASE WHEN c.IS_NULLABLE = 'YES' THEN 1 ELSE 0 END,
DATA_TYPE,
CAST(CHARACTER_MAXIMUM_LENGTH AS INT),
CAST(NUMERIC_PRECISION AS INT),
CAST(NUMERIC_SCALE AS INT),
DOMAIN_NAME,
QUOTENAME(COLUMN_NAME) + ',',
comp.definition + CASE WHEN comp.is_persisted = 1 THEN ' PERSISTED' ELSE '' END AS FieldDefinition,
CASE WHEN ic.object_id IS NULL THEN 0 ELSE 1 END AS IdentityColumn,
CAST(ISNULL(ic.seed_value,0) AS INT) AS IdentitySeed,
CAST(ISNULL(ic.increment_value,0) AS INT) AS IdentityIncrement,
CASE WHEN st.collation_name IS NOT NULL THEN 1 ELSE 0 END AS IsCharColumn
FROM
INFORMATION_SCHEMA.COLUMNS c
JOIN sys.columns sc ON c.TABLE_NAME = OBJECT_NAME(sc.object_id) AND c.COLUMN_NAME = sc.Name
LEFT JOIN sys.identity_columns ic ON c.TABLE_NAME = OBJECT_NAME(ic.object_id) AND c.COLUMN_NAME = ic.Name
JOIN sys.types st ON COALESCE(c.DOMAIN_NAME,c.DATA_TYPE) = st.name
LEFT OUTER JOIN sys.objects dobj ON dobj.object_id = sc.default_object_id AND dobj.type = 'D'
LEFT OUTER JOIN [sys].[computed_columns] comp ON comp.object_id = sc.object_id AND sc.column_id = comp.column_id
WHERE sc.object_id = @TableObjId
AND (comp.definition IS NULL OR @IncludeComputedColumns = 1)
ORDER BY
c.TABLE_NAME, c.ORDINAL_POSITION

SET @RCount = @@ROWCOUNT
IF @Verbose = 1 RAISERROR(N'Found %d fields',0,1,@RCount) WITH NOWAIT;
IF @Verbose = 1 SELECT * FROM @ShowFields;

INSERT INTO @HoldingArea (Flds) VALUES('(')

INSERT INTO @Definition(FieldValue)
VALUES('CREATE TABLE ' + @NewTableName)

INSERT INTO @Definition(FieldValue)
VALUES('(')

INSERT INTO @Definition(FieldValue)
SELECT
CHAR(10) + QUOTENAME(FieldName) + ' ' +
CASE
WHEN FieldDefinition IS NOT NULL THEN 'AS ' + FieldDefinition
WHEN DomainName IS NOT NULL AND @UseSystemDataTypes = 0 THEN QUOTENAME(DomainName) + CASE WHEN IsNullable = 1 THEN ' NULL ' ELSE ' NOT NULL ' END
ELSE QUOTENAME(UPPER(DataType)) +
CASE WHEN IsCharColumn = 1 THEN '(' + ISNULL(NULLIF(CAST(MaxLength AS VARCHAR(10)),'-1'),'MAX') + ')' ELSE '' END +
CASE WHEN @IncludeIdentity = 1 AND IdentityColumn = 1 THEN ' IDENTITY(' + CAST(IdentitySeed AS VARCHAR(5))+ ',' + CAST(IdentityIncrement AS VARCHAR(5)) + ')' ELSE '' END +
CASE WHEN IsNullable = 1 THEN ' NULL ' ELSE ' NOT NULL ' END +
CASE WHEN ColumnDefaultName IS NOT NULL AND @IncludeDefaults = 1 THEN ' CONSTRAINT ' + QUOTENAME(ColumnDefaultName + @ConstraintsNameAppend) + ' DEFAULT' + UPPER(ColumnDefaultValue) ELSE '' END
END +
CASE WHEN FieldID = (SELECT MAX(FieldID) FROM @ShowFields) THEN '' ELSE ',' END
FROM @ShowFields

INSERT INTO @Definition(FieldValue)
SELECT
', CONSTRAINT ' + QUOTENAME(name + @ConstraintsNameAppend) + ' FOREIGN KEY (' + ParentColumns + ') REFERENCES ' + ReferencedObject + '(' + ReferencedColumns + ')'
FROM
(
SELECT
ReferencedObject = QUOTENAME(OBJECT_SCHEMA_NAME(fk.referenced_object_id)) + '.' + QUOTENAME(OBJECT_NAME(fk.referenced_object_id)),
ParentObject = QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.' + QUOTENAME(OBJECT_NAME(parent_object_id)),
fk.name,
REVERSE(SUBSTRING(REVERSE((
SELECT QUOTENAME(cp.name) + ','
FROM
sys.foreign_key_columns fkc
JOIN sys.columns cp ON fkc.parent_object_id = cp.object_id AND fkc.parent_column_id = cp.column_id
WHERE fkc.constraint_object_id = fk.object_id
AND cp.name IN (SELECT FieldName FROM @ShowFields)
FOR XML PATH('')
)), 2, 8000)) ParentColumns,
REVERSE(SUBSTRING(REVERSE((
SELECT cr.name + ','
FROM
sys.foreign_key_columns fkc
JOIN sys.columns cr ON fkc.referenced_object_id = cr.object_id AND fkc.referenced_column_id = cr.column_id
WHERE fkc.constraint_object_id = fk.object_id
AND cr.name IN (SELECT FieldName FROM @ShowFields)
FOR XML PATH('')
)), 2, 8000)) ReferencedColumns
FROM sys.foreign_keys fk
WHERE fk.parent_object_id = @TableObjId
AND @IncludeForeignKeys = 1
) a

SET @RCount = @@ROWCOUNT
IF @Verbose = 1 RAISERROR(N'Found %d foreign keys',0,1,@RCount) WITH NOWAIT;

INSERT INTO @Definition(FieldValue)
SELECT CHAR(10) + ', CONSTRAINT ' + QUOTENAME(name + @ConstraintsNameAppend) + ' CHECK ' + definition FROM sys.check_constraints
WHERE parent_object_id = @TableObjId
AND @IncludeCheckConstraints = 1

SET @RCount = @@ROWCOUNT
IF @Verbose = 1 RAISERROR(N'Found %d check constraints',0,1,@RCount) WITH NOWAIT;

INSERT INTO @PKObjectID(ObjectID)
SELECT DISTINCT
PKObject = cco.object_id
FROM
sys.key_constraints cco
JOIN sys.index_columns cc ON cco.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id
JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id
WHERE
parent_object_id = @TableObjId AND
i.type = 1 AND
is_primary_key = 1
AND @IncludePrimaryKey = 1

SET @RCount = @@ROWCOUNT
IF @Verbose = 1 RAISERROR(N'Found %d primary key',0,1,@RCount) WITH NOWAIT;

INSERT INTO @Uniques(ObjectID)
SELECT DISTINCT
PKObject = cco.object_id
FROM
sys.key_constraints cco
JOIN sys.index_columns cc ON cco.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id
JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id
WHERE
parent_object_id = @TableObjId AND
i.type = 2 AND
is_primary_key = 0 AND
is_unique_constraint = 1
AND @IncludeUniqueIndexes = 1

SET @RCount = @@ROWCOUNT
IF @Verbose = 1 RAISERROR(N'Found %d unique indexes',0,1,@RCount) WITH NOWAIT;

SET @ClusteredPK = CASE WHEN @RCount > 0 THEN 1 ELSE 0 END

INSERT INTO @Definition(FieldValue)
SELECT CHAR(10) + ', CONSTRAINT ' + QUOTENAME(name + @ConstraintsNameAppend) + CASE type WHEN 'PK' THEN ' PRIMARY KEY ' + CASE WHEN pk.ObjectID IS NULL THEN ' NONCLUSTERED ' ELSE ' CLUSTERED ' END
WHEN 'UQ' THEN ' UNIQUE ' END + CASE WHEN u.ObjectID IS NOT NULL THEN ' NONCLUSTERED ' ELSE '' END + '(' +
REVERSE(SUBSTRING(REVERSE((
SELECT
QUOTENAME(c.name) + CASE WHEN cc.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END + ','
FROM
sys.key_constraints ccok
INNER JOIN sys.index_columns cc ON ccok.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id
INNER JOIN sys.columns c ON cc.object_id = c.object_id AND cc.column_id = c.column_id AND c.name IN (SELECT FieldName FROM @ShowFields)
INNER JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id
WHERE
i.object_id = ccok.parent_object_id AND
ccok.object_id = cco.object_id
FOR XML PATH('')
)), 2, 8000)) + ')'
FROM
sys.key_constraints cco
LEFT JOIN @PKObjectID pk ON cco.object_id = pk.ObjectID
LEFT JOIN @Uniques u ON cco.object_id = u.objectID
WHERE
cco.parent_object_id = @TableObjId
AND (@IncludePrimaryKey = 1 OR @IncludeUniqueIndexes = 1)

IF @IncludeIndexes = 1
BEGIN
INSERT INTO @Definition(FieldValue)
SELECT
CHAR(10) + ', INDEX ' + QUOTENAME([name]) COLLATE SQL_Latin1_General_CP1_CI_AS + ' ' + type_desc + ' (' +
REVERSE(SUBSTRING(REVERSE((
SELECT QUOTENAME(name) + CASE WHEN sc.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END + ','
FROM
sys.index_columns sc
JOIN sys.columns c ON sc.object_id = c.object_id AND sc.column_id = c.column_id
WHERE
sc.object_id = @TableObjId AND
sc.object_id = i.object_id AND
sc.index_id = i.index_id AND
c.name IN (SELECT FieldName FROM @ShowFields)
ORDER BY index_column_id ASC
FOR XML PATH('')
)), 2, 8000)) + ')'
FROM sys.indexes i
WHERE
object_id = @TableObjId
AND CASE WHEN @ClusteredPK = 1 AND is_primary_key = 1 AND type = 1 THEN 0 ELSE 1 END = 1
AND is_unique_constraint = 0
AND is_primary_key = 0

SET @RCount = @@ROWCOUNT
IF @Verbose = 1 RAISERROR(N'Found %d indexes',0,1,@RCount) WITH NOWAIT;
END

INSERT INTO @Definition(FieldValue)
VALUES(CHAR(10) + ')')

INSERT INTO @MainDefinition(FieldValue)
SELECT FieldValue FROM @Definition
ORDER BY DefinitionID ASC

SET @RCount = @@ROWCOUNT
IF @Verbose = 1 RAISERROR(N'Collected %d rows for main definition',0,1,@RCount) WITH NOWAIT;

SET @Result = N'';

SELECT @Result = @Result + CHAR(13) + FieldValue FROM @MainDefinition WHERE FieldValue IS NOT NULL;

END
GO

EXECUTE sp_MS_marksystemobject 'sp_GenerateTableDDLScript'
GO