Data Management

Script Table definitions using TSQL

Generating scripts in SQL Server using TSQL has never been easy. In today's SQL Server tip, I will show you how you can use TSQL in SQL Server 2005 code to script a table's definition.

 

Using TSQL to generate CREATE TABLE scripts in SQL Server has never been something that is easy to do.  The functionality to script a table is not inherently built into the database engine, so developers have generally used some type of SQL Server interface tool (such as Management Studio) to generate the script.  These tools use some type of SQL Server object model component, such as SQL Server Management Objects (SMO).  In today's SQL Server tip, I will show you how you can use TSQL in SQL Server 2005 code to script a table's definition.

OBJECT_DEFINITION

In a previous article, I looked at how you can use the new OBJECT_DEFINITION function in SQL Server 2005 to generate the definition script of an object.  This function is a fantastic step forward for DBAs and database developers because of the ease involved in generating stored procedure, function, and view scripts.  However, there is a huge limitation to the function; it will not script a table definition.

sp_ScriptTable

I have created the below system stored procedure to generate the CREATE TABLE statement for any existing table in the current database.  The only required parameter is the @TableName parameter, while other parameters include options such as scripting any table constraints, indexes, supplying a new table name for the CREATE TABLE statement, and an indication to use system defined data types or to use any user-defined data types defined on the table.

use master go CREATE PROCEDURE sp_ScriptTable (     @TableName SYSNAME,     @IncludeConstraints BIT = 1,     @IncludeIndexes BIT = 1,     @NewTableName SYSNAME = NULL,     @UseSystemDataTypes BIT = 0 ) AS BEGIN     DECLARE @MainDefinition TABLE     (         FieldValue VARCHAR(200)     )     DECLARE @DBName SYSNAME     DECLARE @ClusteredPK BIT     DECLARE @TableSchema NVARCHAR(255)     SET @DBName = DB_NAME(DB_ID())     SELECT @TableName = name FROM sysobjects WHERE id = OBJECT_ID(@TableName)     DECLARE @ShowFields TABLE     (         FieldID INT IDENTITY(1,1),         DatabaseName VARCHAR(100),         TableOwner VARCHAR(100),         TableName VARCHAR(100),         FieldName VARCHAR(100),         ColumnPosition INT,         ColumnDefaultValue VARCHAR(100),         ColumnDefaultName VARCHAR(100),         IsNullable BIT,         DataType VARCHAR(100),         MaxLength INT,         NumericPrecision INT,         NumericScale INT,         DomainName VARCHAR(100),         FieldListingName VARCHAR(110),         FieldDefinition CHAR(1),         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 VARCHAR(200)     )     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,         COLUMN_NAME + ',',         '' 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'     WHERE c.TABLE_NAME = @TableName     ORDER BY         c.TABLE_NAME, c.ORDINAL_POSITION     SELECT TOP 1 @TableSchema = TableOwner     FROM @ShowFields     INSERT INTO @HoldingArea (Flds) VALUES('(')     INSERT INTO @Definition(FieldValue)     VALUES('CREATE TABLE ' + CASE WHEN @NewTableName IS NOT NULL THEN @NewTableName ELSE @DBName + '.' + @TableSchema + '.' + @TableName END)     INSERT INTO @Definition(FieldValue)     VALUES('(')     INSERT INTO @Definition(FieldValue)         SELECT                 CHAR(10) + FieldName + ' ' +                                     CASE                                         WHEN DomainName IS NOT NULL AND @UseSystemDataTypes = 0 THEN DomainName + CASE WHEN IsNullable = 1 THEN ' NULL ' ELSE ' NOT NULL ' END                                         ELSE UPPER(DataType) +                                             CASE WHEN IsCharColumn = 1 THEN '(' + CAST(MaxLength AS VARCHAR(10)) + ')' ELSE '' END +                                             CASE WHEN 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 @IncludeConstraints = 1 THEN 'CONSTRAINT [' + ColumnDefaultName + '] DEFAULT' + UPPER(ColumnDefaultValue) ELSE '' END                                     END +                                     CASE WHEN FieldID = (SELECT MAX(FieldID) FROM @ShowFields) THEN '' ELSE ',' END         FROM    @ShowFields         IF @IncludeConstraints = 1         BEGIN             INSERT INTO @Definition(FieldValue)             SELECT             ',CONSTRAINT [' + name + '] FOREIGN KEY (' + ParentColumns + ') REFERENCES [' + ReferencedObject + '](' + ReferencedColumns + ')'             FROM             (                 SELECT                 ReferencedObject = OBJECT_NAME(fk.referenced_object_id), ParentObject = OBJECT_NAME(parent_object_id),fk.name,                 REVERSE(SUBSTRING(REVERSE((                 SELECT 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                 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                 FOR XML PATH('')                 )), 2, 8000)) ReferencedColumns                 FROM sys.foreign_keys fk             ) a             WHERE ParentObject = @TableName             INSERT INTO @Definition(FieldValue)             SELECT',CONSTRAINT [' + name + '] CHECK ' + definition FROM sys.check_constraints             WHERE OBJECT_NAME(parent_object_id) = @TableName         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             OBJECT_NAME(parent_object_id) = @TableName    AND                i.type = 1 AND             is_primary_key = 1         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             OBJECT_NAME(parent_object_id) = @TableName AND                    i.type = 2 AND             is_primary_key = 0 AND             is_unique_constraint = 1         SET @ClusteredPK = CASE WHEN @@ROWCOUNT > 0 THEN 1 ELSE 0 END         INSERT INTO @Definition(FieldValue)         SELECT    ',CONSTRAINT ' + name + 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                 c.name +  + CASE WHEN cc.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END + ','             FROM                 sys.key_constraints ccok                 LEFT JOIN sys.index_columns cc ON ccok.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id                 LEFT JOIN sys.columns c ON cc.object_id = c.object_id AND cc.column_id = c.column_id                 LEFT 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             OBJECT_NAME(cco.parent_object_id) = @TableName         END         INSERT INTO @Definition(FieldValue)         VALUES(')')         IF @IncludeIndexes = 1         BEGIN             INSERT INTO @Definition(FieldValue)             SELECT                 'CREATE ' + type_desc + ' INDEX [' + [name] COLLATE SQL_Latin1_General_CP1_CI_AS + '] ON [' +  OBJECT_NAME(object_id) + '] (' +                 REVERSE(SUBSTRING(REVERSE((                     SELECT 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                         OBJECT_NAME(sc.object_id) = @TableName AND                         sc.object_id = i.object_id AND                         sc.index_id = i.index_id                     ORDER BY index_column_id ASC                     FOR XML PATH('')             )), 2, 8000)) + ')'             FROM sys.indexes i             WHERE                 OBJECT_NAME(object_id) = @TableName                 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         END             INSERT INTO @MainDefinition(FieldValue)             SELECT FieldValue FROM @Definition             ORDER BY DefinitionID ASC         SELECT * FROM @MainDefinition END GO

EXECUTE sp_MS_marksystemobject ' sp_ScriptTable'  

Room For Improvement

The above system stored procedure works well for scripting out a table's definition, including constraints and indexes.  However, it is not perfect.  I didn't include error handling, file-group options, or some other miscellaneous options available when scripting a table.  If you spot a portion of the procedure that needs improvement, please let me know.  I'd love to hear any comments or improvement ideas for the above procedure.

About

Tim Chapman is a SQL Server MVP, a database architect, and an administrator who works as an independent consultant in Raleigh, NC, and has more than nine years of IT experience.

17 comments
dvokic
dvokic

Hey Tim, it's really helpful script. Although, there is a small bug - if you have a column of data type TEXT it will script that column as CREATE TABLE [dbo].[tablename]( [textcoulmnname] [text] (2147483647) .... so, you have to exclude text data type from other data types with "IsCharColumn = 1". Simply, replace this: ELSE UPPER(DataType) + CASE WHEN IsCharColumn = 1 THEN '(' + CAST(MaxLength AS VARCHAR(10)) + ')' ELSE '' END with this: ELSE UPPER(DataType) + CASE WHEN IsCharColumn = 1 and DataType!='text' THEN '(' + CAST(MaxLength AS VARCHAR(10)) + ')' ELSE '' END Once again, great and helpful script. Kind regards, Dragan

skillm
skillm

That is a truly awesome script. I prefer to keep the constraints in the table def also. Thanks plenty for that! I made one minor mod by adding the schema table to the foreign key section so that the schema for the referenced table in the FK is added. Thanks again. SELECT ',CONSTRAINT [' + name + '] FOREIGN KEY (' + ParentColumns + ') REFERENCES ['+ ReferencedSchema + '].[' + ReferencedObject + '](' + ReferencedColumns + ')' FROM ( SELECT ReferencedObject = OBJECT_NAME(fk.referenced_object_id), ReferencedSchema = s.name, ParentObject = OBJECT_NAME(fk.parent_object_id), fk.name, REVERSE(SUBSTRING(REVERSE(( SELECT 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 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 FOR XML PATH('') )), 2, 8000)) ReferencedColumns FROM sys.foreign_keys fk join sys.objects o on fk.referenced_object_id = o.object_id join sys.schemas s on o.schema_id = s.schema_id ) a WHERE ParentObject = @TableName

akhil393
akhil393

Error converting data type nvarchar to bit. for any tables

joeller
joeller

We also need to script extended properties and triggers. Is there an easy way to do this.

jeffudesu
jeffudesu

It would be nice if this could work for views too. Currently views are supported by the system OBJECT_DEFINITION function but indexes are not scripted. Adding view support to this Stored Procedure would be very useful for this purpose.

MarkLoew
MarkLoew

Was using this to create some historical tracking tables. I noticed that you have two tables with the same name but in different schemas the query logic will merge the rows of both tables together. By modifying the join slightly you can make it take into account the schema name as well. JOIN sys.columns sc ON OBJECT_ID( c.TABLE_SCHEMA + '.' + c.TABLE_NAME ) = sc.object_id

chapman.tim
chapman.tim

Sorry about that...not sure why the ticks didn't trasnfer properly. Here is the revised code. USE [master] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sp_ScriptTable] ( @TableName SYSNAME, @IncludeConstraints BIT = 1, @IncludeIndexes BIT = 1, @NewTableName SYSNAME = NULL, @UseSystemDataTypes BIT = 0 ) AS BEGIN DECLARE @MainDefinition TABLE ( FieldValue VARCHAR(200) ) DECLARE @DBName SYSNAME DECLARE @ClusteredPK BIT DECLARE @TableSchema NVARCHAR(255) SET @DBName = DB_NAME(DB_ID()) --//Make sure the table name is the same case. SELECT @TableName = name FROM sysobjects WHERE id = OBJECT_ID(@TableName) DECLARE @ShowFields TABLE ( FieldID INT IDENTITY(1,1), DatabaseName VARCHAR(100), TableOwner VARCHAR(100), TableName VARCHAR(100), FieldName VARCHAR(100), ColumnPosition INT, ColumnDefaultValue VARCHAR(100), ColumnDefaultName VARCHAR(100), IsNullable BIT, DataType VARCHAR(100), MaxLength INT, NumericPrecision INT, NumericScale INT, DomainName VARCHAR(100), FieldListingName VARCHAR(110), FieldDefinition CHAR(1), 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 VARCHAR(200) ) 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, COLUMN_NAME + ',', '' 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' WHERE c.TABLE_NAME = @TableName ORDER BY c.TABLE_NAME, c.ORDINAL_POSITION SELECT TOP 1 @TableSchema = TableOwner FROM @ShowFields INSERT INTO @HoldingArea (Flds) VALUES('(') INSERT INTO @Definition(FieldValue) VALUES('CREATE TABLE ' + CASE WHEN @NewTableName IS NOT NULL THEN @NewTableName ELSE @DBName + '.' + @TableSchema + '.' + @TableName END) INSERT INTO @Definition(FieldValue) VALUES('(') INSERT INTO @Definition(FieldValue) SELECT CHAR(10) + FieldName + ' ' + CASE WHEN DomainName IS NOT NULL AND @UseSystemDataTypes = 0 THEN DomainName + CASE WHEN IsNullable = 1 THEN ' NULL ' ELSE ' NOT NULL ' END ELSE UPPER(DataType) + CASE WHEN IsCharColumn = 1 THEN '(' + CAST(MaxLength AS VARCHAR(10)) + ')' ELSE '' END + CASE WHEN 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 @IncludeConstraints = 1 THEN 'CONSTRAINT [' + ColumnDefaultName + '] DEFAULT' + UPPER(ColumnDefaultValue) ELSE '' END END + CASE WHEN FieldID = (SELECT MAX(FieldID) FROM @ShowFields) THEN '' ELSE ',' END FROM @ShowFields IF @IncludeConstraints = 1 BEGIN INSERT INTO @Definition(FieldValue) SELECT ',CONSTRAINT [' + name + '] FOREIGN KEY (' + ParentColumns + ') REFERENCES [' + ReferencedObject + '](' + ReferencedColumns + ')' FROM ( SELECT ReferencedObject = OBJECT_NAME(fk.referenced_object_id), ParentObject = OBJECT_NAME(parent_object_id),fk.name, REVERSE(SUBSTRING(REVERSE(( SELECT 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 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 FOR XML PATH('') )), 2, 8000)) ReferencedColumns FROM sys.foreign_keys fk ) a WHERE ParentObject = @TableName INSERT INTO @Definition(FieldValue) SELECT',CONSTRAINT [' + name + '] CHECK ' + definition FROM sys.check_constraints WHERE OBJECT_NAME(parent_object_id) = @TableName -------------------------------------------------------------------------------------------- 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 OBJECT_NAME(parent_object_id) = @TableName AND i.type = 1 AND is_primary_key = 1 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 OBJECT_NAME(parent_object_id) = @TableName AND i.type = 2 AND is_primary_key = 0 AND is_unique_constraint = 1 SET @ClusteredPK = CASE WHEN @@ROWCOUNT > 0 THEN 1 ELSE 0 END INSERT INTO @Definition(FieldValue) SELECT ',CONSTRAINT ' + name + 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 c.name + + CASE WHEN cc.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END + ',' FROM sys.key_constraints ccok LEFT JOIN sys.index_columns cc ON ccok.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id LEFT JOIN sys.columns c ON cc.object_id = c.object_id AND cc.column_id = c.column_id LEFT 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 OBJECT_NAME(cco.parent_object_id) = @TableName END ---------------------------------------------------------------------------------------------- INSERT INTO @Definition(FieldValue) VALUES(')') IF @IncludeIndexes = 1 BEGIN INSERT INTO @Definition(FieldValue) SELECT 'CREATE ' + type_desc + ' INDEX [' + [name] COLLATE SQL_Latin1_General_CP1_CI_AS + '] ON [' + OBJECT_NAME(object_id) + '] (' + REVERSE(SUBSTRING(REVERSE(( SELECT 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 OBJECT_NAME(sc.object_id) = @TableName AND sc.object_id = i.object_id AND sc.index_id = i.index_id ORDER BY index_column_id ASC FOR XML PATH('') )), 2, 8000)) + ')' FROM sys.indexes i WHERE OBJECT_NAME(object_id) = @TableName 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 END INSERT INTO @MainDefinition(FieldValue) SELECT FieldValue FROM @Definition ORDER BY DefinitionID ASC SELECT * FROM @MainDefinition END

abbas_zolfaghari
abbas_zolfaghari

this script does not work properly in MS SQL 2000 :( error are in FOR XML PATH('') I can't remove error.

abbas_zolfaghari
abbas_zolfaghari

Thanks so much But I Change this code to insert "UNIQUE" for index/keys that are unique. Notice : in next edition is good to insert collation (collate) of each field in script. Change { 'CREATE ' + type_desc } To { 'CREATE ' + CASE is_unique WHEN 1 THEN 'UNIQUE ' ELSE '' END + type_desc } All script is here. [CODE] CREATE PROCEDURE [dbo].[sp_ScriptTable] ( @TableName SYSNAME, @IncludeConstraints BIT = 1, @IncludeIndexes BIT = 1, @NewTableName SYSNAME = NULL, @UseSystemDataTypes BIT = 0 ) AS BEGIN DECLARE @MainDefinition TABLE ( FieldValue VARCHAR(200) ) DECLARE @DBName SYSNAME DECLARE @ClusteredPK BIT DECLARE @TableSchema NVARCHAR(255) SET @DBName = DB_NAME(DB_ID()) SELECT @TableName = name FROM sysobjects WHERE id = OBJECT_ID(@TableName) DECLARE @ShowFields TABLE ( FieldID INT IDENTITY(1,1), DatabaseName VARCHAR(100), TableOwner VARCHAR(100), TableName VARCHAR(100), FieldName VARCHAR(100), ColumnPosition INT, ColumnDefaultValue VARCHAR(100), ColumnDefaultName VARCHAR(100), IsNullable BIT, DataType VARCHAR(100), MaxLength INT, NumericPrecision INT, NumericScale INT, DomainName VARCHAR(100), FieldListingName VARCHAR(110), FieldDefinition CHAR(1), 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 VARCHAR(200) ) 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, COLUMN_NAME + ',', '' 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' WHERE c.TABLE_NAME = @TableName ORDER BY c.TABLE_NAME, c.ORDINAL_POSITION SELECT TOP 1 @TableSchema = TableOwner FROM @ShowFields INSERT INTO @HoldingArea (Flds) VALUES('(') INSERT INTO @Definition(FieldValue) VALUES('CREATE TABLE ' + CASE WHEN @NewTableName IS NOT NULL THEN @NewTableName ELSE @DBName + '.' + @TableSchema + '.' + @TableName END) INSERT INTO @Definition(FieldValue) VALUES('(') INSERT INTO @Definition(FieldValue) SELECT CHAR(10) + FieldName + ' ' + CASE WHEN DomainName IS NOT NULL AND @UseSystemDataTypes = 0 THEN DomainName + CASE WHEN IsNullable = 1 THEN ' NULL ' ELSE ' NOT NULL ' END ELSE UPPER(DataType) + CASE WHEN IsCharColumn = 1 THEN '(' + CASE WHEN MaxLength > 0 THEN CAST(MaxLength AS VARCHAR(10)) ELSE 'Max' END + ')' ELSE '' END + CASE WHEN 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 @IncludeConstraints = 1 THEN 'CONSTRAINT [' + ColumnDefaultName + '] DEFAULT' + UPPER(ColumnDefaultValue) ELSE '' END END + CASE WHEN FieldID = (SELECT MAX(FieldID) FROM @ShowFields) THEN '' ELSE ',' END FROM @ShowFields IF @IncludeConstraints = 1 BEGIN INSERT INTO @Definition(FieldValue) SELECT ',CONSTRAINT [' + name + '] FOREIGN KEY (' + ParentColumns + ') REFERENCES [' + ReferencedObject + '](' + ReferencedColumns + ')' FROM ( SELECT ReferencedObject = OBJECT_NAME(fk.referenced_object_id), ParentObject = OBJECT_NAME(parent_object_id),fk.name, REVERSE(SUBSTRING(REVERSE(( SELECT 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 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 FOR XML PATH('') )), 2, 8000)) ReferencedColumns FROM sys.foreign_keys fk ) a WHERE ParentObject = @TableName INSERT INTO @Definition(FieldValue) SELECT ',CONSTRAINT [' + name + '] CHECK ' + definition FROM sys.check_constraints WHERE OBJECT_NAME(parent_object_id) = @TableName 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 OBJECT_NAME(parent_object_id) = @TableName AND i.type = 1 AND is_primary_key = 1 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 OBJECT_NAME(parent_object_id) = @TableName AND i.type = 2 AND is_primary_key = 0 AND is_unique_constraint = 1 SET @ClusteredPK = CASE WHEN @@ROWCOUNT > 0 THEN 1 ELSE 0 END INSERT INTO @Definition(FieldValue) SELECT ',CONSTRAINT ' + name + 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 c.name + + CASE WHEN cc.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END + ',' FROM sys.key_constraints ccok LEFT JOIN sys.index_columns cc ON ccok.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id LEFT JOIN sys.columns c ON cc.object_id = c.object_id AND cc.column_id = c.column_id LEFT 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 OBJECT_NAME(cco.parent_object_id) = @TableName END INSERT INTO @Definition(FieldValue) VALUES(')') IF @IncludeIndexes = 1 BEGIN INSERT INTO @Definition(FieldValue) SELECT 'CREATE ' + CASE is_unique WHEN 1 THEN 'UNIQUE ' ELSE '' END + type_desc + ' INDEX [' + [name] COLLATE SQL_Latin1_General_CP1_CI_AS + '] ON [' + OBJECT_NAME(object_id) + '] (' + REVERSE(SUBSTRING(REVERSE(( SELECT 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 OBJECT_NAME(sc.object_id) = @TableName AND sc.object_id = i.object_id AND sc.index_id = i.index_id ORDER BY index_column_id ASC FOR XML PATH('') )), 2, 8000)) + ')' FROM sys.indexes i WHERE OBJECT_NAME(object_id) = @TableName 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 END INSERT INTO @MainDefinition(FieldValue) SELECT FieldValue FROM @Definition ORDER BY DefinitionID ASC SELECT * FROM @MainDefinition END EXECUTE sp_MS_marksystemobject 'sp_ScriptTable' [/CODE]

don.bryan
don.bryan

The decimal datatype gets generated without a precision or scale which defaults to Decimal(18,0).

don.bryan
don.bryan

When the varchar(max) data type is used the MaxLength = -1. This causes an invalid create statement MYCOL VARCHAR(-1). The solution is modifying the datatype case statement to replace -1 with 'MAX' for character fileds CASE WHEN IsCharColumn = 1 THEN '(' + case when MaxLength >0 then CAST(MaxLength AS VARCHAR(10)) else 'Max' end + ')' ELSE '' END +

godfrey.yung
godfrey.yung

It is an interesting article, however it looks like the script has been edited (did you prepare the script in Word or TechRepublic auto edit text before publish?). You can't simply copy and paste the code as it does not pass the query parser. It is understood that all quotation marks should be ', not ??? or ??? but it is a matter of courtesy to provide a decent code for people to use. Nice work!

BALTHOR
BALTHOR

The program turns modules into script.

marketing.it
marketing.it

Hi Mr. Tim Chapman, and all, Thanks a lot for your effort. BTW, Could you please help to fix bug: "Decimal datatype error" as described by don.bryan@ If you can, pls send me email: hieu79lt @ yahoo. com Thanks for your help Hieu

Editor's Picks