Copy complete structure of a table
Using some methods, when you create a copy of a table you lose indexes, PK, FK, etc. For example in SQL Server I can say:
select * into dbo.table2 from dbo.table1;This is just a simple copy of the table; all of the indexes / constraints are missing. How can I copy a table structure (without using a backup)?
I am primarily looking to do this manually, but if that's not possible I'll accept any solution.
You can script a table relatively easy using the UI of course:
This will output a
CREATE TABLEscript and you only have to search and replace the old name with the new name (and verify that an object with the new name doesn't already exist).But if you're trying to automate this (e.g. generate the create table script in code), it is a little more cumbersome. The above scripting option doesn't just pull the entire
CREATE TABLEDDL from a single location in the metadata; it does a whole bunch of magic behind the scenes in the code to generate the eventualCREATE TABLEscript (you can use Profiler to see where it gets its data, but you can't see how it assembles it). I suggested an option for this:http://connect.microsoft.com/SQLServer/feedback/details/273934
However this was met with very few votes and was quickly shot down by Microsoft. You may find it much more worthwhile to use a 3rd party tool for generating schema (I've blogged about this).
In SQL Server 2012 there are new metadata functions that allow you to get much closer than the work you have to do in 2005, 2008 and 2008 R2, piecing together column information from the metadata (which has a lot of caveats, for example if it's decimal you have to add the precision/scale, if [n[var[char]] you have to add the length specification, if n[var]char you have to cut the max_length in half, if it is a MAX you have to change -1 to MAX, etc etc). In SQL Server 2012 this part is a little easier:
SELECT name, system_type_name, is_nullable FROM sys.dm_exec_describe_first_result_set('select * from sys.objects', NULL, 0)Results:
name system_type_name is_nullable -------------------- ---------------- ----------- name nvarchar(128) 0 object_id int 0 principal_id int 1 schema_id int 0 parent_object_id int 0 type char(2) 0 type_desc nvarchar(60) 1 create_date datetime 0 modify_date datetime 0 is_ms_shipped bit 0 is_published bit 0 is_schema_published bit 0Arguably this is much closer to your targeted
CREATE TABLEstatement than a convoluted approach usingsys.columns, but there is still a lot of work to do. Keys, constraints, text in row options, filegroup information, compression settings, indexes, etc. It's a very long list and I'll once again suggest you look at a 3rd party tool for this instead of, at the risk of repeating an over-used analogy, re-inventing the wheel.That all said, if you need to do this through code but you can do it outside of SQL Server, you can consider SMO/PowerShell. See this tip and the Scripter.Script() method.
Please note that sys.dm_exec_describe_first_result_set will bring you nvarchar(27) instead of datetime2 if executed over OLEDB.
This is not meant to be antagonistic or negative at all, but this kind of thing is why I've moved to Postgres after 20 years with SQL. The Postgres solution is: CREATE TABLE tbl2 (LIKE tbl1 INCLUDING ALL); -- duplicates table including all indexes comments, etc.
@DamienSawyer To be fair, making a bunch of identical tables with the same indexes and everything else is kind of a design smell. And there are plenty of 3rd party tools (some free, some open source) that can help with that when you really do need it.
Sure, @AaronBertrand that's very true in many cases, especially in the ubiquitous 3NF CRUD style schemas. Sometimes we use databases less rigidly though, for example throwing together and cleaning up data from informal sources like CSVs to be migrated into a production system. For comparison, one of the reasons Excel is great is because while a workbook can get complex, it has very low ceremony to do the very simple. A piece of paper can be used both as a contract for buying a house and scribbling a shopping list.
@DamienSawyer And when you have an intermediary process where the thing is essentially a throw-away staging table, I'd argue that it doesn't have to include all of the indexes, constraints, extended properties, etc. that exist in the table it's being modeled after. The default behavior does have a very valid use case, even though other use cases may exist.
Sure. Like everything, "it depends". Another thing is though, even if indeed it "doesn't have to include all of the...." extras, it doesn't hurt me at all because it was quick to type and (surprisingly) quick to execute. If there was a case where you indeed didn't want the indexes or something as they'd increase burdon or data load or similar, then there are other options for the INCLUDING clause that omit them. One of the smartest guys I've ever worked with used to say that "the simple should be simple, the hard should be doable". This reminds me of that a little bit :-)
@DamienSawyer Well, this doesn't seem to be one of those things you'd switch platforms for, but what do I know...
haha. Of course not. Don't get me wrong, SQL Server has served well for years. It's never missed a beat for me. Not one! It's really cool though in these days of Docker et al where trying something new at dev time is five minutes work as opposed to 5 hours.
"...this doesn't seem to be one of those things you'd switch platforms for..." ...well, you're right. If you'll forgive me from being a little antagonistic, JSONB is though :-)
i wrote this sp to create automatically the schema with all things, pk, fk, partitions, constraints...
IMPORTANT!! before exec
create type TestTableType as table (ObjectID int)here the SP:
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --*************************************************************************************************' -- La procedura crea lo script di una tabella -- Tabella : xxxxx -- Creata da : E.Mantovanelli -- Data creazione : 28-06-2012 -- Data modifica: 28-06-2012 --*************************************************************************************************' /* -- ID----|-----Data-----|-- User --------- | ---- Note 20-11-2013 E.Mantovanelli distinzione schema delle tabelle estrazione da db selezionato aggiunta estrazione partizione */ CREATE PROCEDURE [dbo].[util_ScriptTable] @DBName SYSNAME ,@schema sysname ,@TableName SYSNAME ,@IncludeConstraints BIT = 1 ,@IncludeIndexes BIT = 1 ,@NewTableSchema sysname ,@NewTableName SYSNAME = NULL ,@UseSystemDataTypes BIT = 0 ,@script varchar(max) output AS BEGIN try if not exists (select * from sys.types where name = 'TestTableType') create type TestTableType as table (ObjectID int)--drop type TestTableType declare @sql nvarchar(max) 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 varchar(10) ,NumericPrecision INT ,NumericScale INT ,DomainName VARCHAR(100) ,FieldListingName VARCHAR(110) ,FieldDefinition CHAR(1) ,IdentityColumn BIT ,IdentitySeed INT ,IdentityIncrement INT ,IsCharColumn BIT ,IsComputed varchar(255)) 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)) set @sql= ' use '[email protected]+' SELECT distinct DB_NAME() ,TABLE_SCHEMA ,TABLE_NAME ,''[''+COLUMN_NAME+'']'' as 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 ,case CHARACTER_MAXIMUM_LENGTH when -1 then ''max'' else CAST(CHARACTER_MAXIMUM_LENGTH AS varchar) end--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 ,cc.definition 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 join sys.computed_columns cc on c.TABLE_NAME=OBJECT_NAME(cc.object_id) and sc.column_id=cc.column_id WHERE c.TABLE_NAME = @TableName and [email protected] ORDER BY c.TABLE_NAME, c.ORDINAL_POSITION ' print @sql INSERT INTO @ShowFields( DatabaseName ,TableOwner ,TableName ,FieldName ,ColumnPosition ,ColumnDefaultValue ,ColumnDefaultName ,IsNullable ,DataType ,MaxLength ,NumericPrecision ,NumericScale ,DomainName ,FieldListingName ,FieldDefinition ,IdentityColumn ,IdentitySeed ,IdentityIncrement ,IsCharColumn ,IsComputed) exec sp_executesql @sql, N'@TableName varchar(50),@schema varchar(50)', @[email protected],@[email protected] /* SELECT @DBName--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 @DBName + '.' + @NewTableSchema + '.' + @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 CASE WHEN DomainName IS NOT NULL AND @UseSystemDataTypes = 0 THEN DomainName + CASe WHEN IsNullable = 1 THEN ' NULL ' ELSE ' NOT NULL ' END ELSE case when IsComputed is null then UPPER(DataType) + CASE WHEN IsCharColumn = 1 THEN '(' + CAST(MaxLength AS VARCHAR(10)) + ')' ELSE CASE WHEN DataType = 'numeric' THEN '(' + CAST(NumericPrecision AS VARCHAR(10))+','+ CAST(NumericScale AS VARCHAR(10)) + ')' ELSE CASE WHEN DataType = 'decimal' THEN '(' + CAST(NumericPrecision AS VARCHAR(10))+','+ CAST(NumericScale AS VARCHAR(10)) + ')' ELSE '' end end 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 [' + replace(ColumnDefaultName,@TableName,@NewTableName) + '] DEFAULT' + UPPER(ColumnDefaultValue) ELSE '' END else ' as '+IsComputed+' ' end END + CASE WHEN FieldID = (SELECT MAX(FieldID) FROM @ShowFields) THEN '' ELSE ',' END FROM @ShowFields IF @IncludeConstraints = 1 BEGIN set @sql= ' use '[email protected]+' SELECT distinct '',CONSTRAINT ['' + replace(name,@TableName,@NewTableName) + ''] 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 inner join sys.schemas s on fk.schema_id=s.schema_id and [email protected]) a WHERE ParentObject = @TableName ' print @sql INSERT INTO @Definition(FieldValue) exec sp_executesql @sql, N'@TableName varchar(50),@NewTableName varchar(50),@schema varchar(50)', @[email protected],@[email protected],@[email protected] /* 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 */ set @sql= ' use '[email protected]+' SELECT distinct '',CONSTRAINT ['' + replace(c.name,@TableName,@NewTableName) + ''] CHECK '' + definition FROM sys.check_constraints c join sys.schemas s on c.schema_id=s.schema_id and [email protected] WHERE OBJECT_NAME(parent_object_id) = @TableName ' print @sql INSERT INTO @Definition(FieldValue) exec sp_executesql @sql, N'@TableName varchar(50),@NewTableName varchar(50),@schema varchar(50)', @[email protected],@[email protected],@[email protected] /* SELECT ',CONSTRAINT [' + name + '] CHECK ' + definition FROM sys.check_constraints WHERE OBJECT_NAME(parent_object_id) = @TableName */ set @sql= ' use '[email protected]+' 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 join sys.schemas s on cco.schema_id=s.schema_id and [email protected] WHERE OBJECT_NAME(parent_object_id) = @TableName AND i.type = 1 AND is_primary_key = 1 ' print @sql INSERT INTO @PKObjectID(ObjectID) exec sp_executesql @sql, N'@TableName varchar(50),@schema varchar(50)', @[email protected],@[email protected] /* 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 */ set @sql= ' use '[email protected]+' 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 join sys.schemas s on cco.schema_id=s.schema_id and [email protected] WHERE OBJECT_NAME(parent_object_id) = @TableName AND i.type = 2 AND is_primary_key = 0 AND is_unique_constraint = 1 ' print @sql INSERT INTO @Uniques(ObjectID) exec sp_executesql @sql, N'@TableName varchar(50),@schema varchar(50)', @[email protected],@[email protected] /* 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 declare @t TestTableType insert @t select * from @PKObjectID declare @u TestTableType insert @u select * from @Uniques set @sql= ' use '[email protected]+' SELECT distinct '',CONSTRAINT '' + replace(cco.name,@TableName,@NewTableName) + 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 order by key_ordinal FOR XML PATH(''''))), 2, 8000)) + '')'' FROM sys.key_constraints cco inner join sys.schemas s on cco.schema_id=s.schema_id and [email protected] LEFT JOIN @U u ON cco.object_id = u.objectID LEFT JOIN @t pk ON cco.object_id = pk.ObjectID WHERE OBJECT_NAME(cco.parent_object_id) = @TableName ' print @sql INSERT INTO @Definition(FieldValue) exec sp_executesql @sql, N'@TableName varchar(50),@NewTableName varchar(50),@schema varchar(50),@t TestTableType readonly,@u TestTableType readonly', @[email protected],@[email protected],@[email protected],@[email protected],@[email protected] /* 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(')') set @sql= ' use '[email protected]+' select '' on '' + d.name + ''([''+c.name+''])'' from sys.tables t join sys.indexes i on(i.object_id = t.object_id and i.index_id < 2) join sys.index_columns ic on(ic.partition_ordinal > 0 and ic.index_id = i.index_id and ic.object_id = t.object_id) join sys.columns c on(c.object_id = ic.object_id and c.column_id = ic.column_id) join sys.schemas s on t.schema_id=s.schema_id join sys.data_spaces d on i.data_space_id=d.data_space_id where [email protected] and [email protected] order by key_ordinal ' print 'x' print @sql INSERT INTO @Definition(FieldValue) exec sp_executesql @sql, N'@TableName varchar(50),@schema varchar(50)', @[email protected],@[email protected] IF @IncludeIndexes = 1 BEGIN set @sql= ' use '[email protected]+' SELECT distinct '' CREATE '' + i.type_desc + '' INDEX ['' + replace(i.name COLLATE SQL_Latin1_General_CP1_CI_AS,@TableName,@NewTableName) + ''] ON '[email protected]+'.'[email protected]+'.'[email protected]+' ('' + 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 [email protected] AND sc.object_id = i.object_id AND sc.index_id = i.index_id and is_included_column=0 ORDER BY key_ordinal ASC FOR XML PATH('''') )), 2, 8000)) + '')''+ ISNULL( '' include (''+REVERSE(SUBSTRING(REVERSE(( SELECT name + '','' FROM sys.index_columns sc JOIN sys.columns c ON sc.object_id = c.object_id AND sc.column_id = c.column_id WHERE [email protected] AND sc.object_id = i.object_id AND sc.index_id = i.index_id and is_included_column=1 ORDER BY key_ordinal ASC FOR XML PATH('''') )), 2, 8000))+'')'' ,'''')+'''' FROM sys.indexes i join sys.tables t on i.object_id=t.object_id join sys.schemas s on t.schema_id=s.schema_id AND CASE WHEN @ClusteredPK = 1 AND is_primary_key = 1 AND i.type = 1 THEN 0 ELSE 1 END = 1 AND is_unique_constraint = 0 AND is_primary_key = 0 where [email protected] and [email protected] ' print @sql INSERT INTO @Definition(FieldValue) exec sp_executesql @sql, N'@TableName varchar(50),@NewTableName varchar(50),@schema varchar(50), @ClusteredPK bit', @[email protected],@[email protected],@[email protected],@[email protected] END /* 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 */ INSERT INTO @MainDefinition(FieldValue) SELECT FieldValue FROM @Definition ORDER BY DefinitionID ASC ---------------------------------- declare @q varchar(max) set @q=(select replace((SELECT FieldValue FROM @MainDefinition FOR XML PATH('')),'</FieldValue>','')) set @script=(select REPLACE(@q,'<FieldValue>','')) --drop type TestTableType END try -- ############################################################################################################################################################################## BEGIN CATCH BEGIN -- INIZIO Procedura in errore ========================================================================================================================================================= PRINT '***********************************************************************************************************************************************************' PRINT 'ErrorNumber : ' + CAST(ERROR_NUMBER() AS NVARCHAR(MAX)) PRINT 'ErrorSeverity : ' + CAST(ERROR_SEVERITY() AS NVARCHAR(MAX)) PRINT 'ErrorState : ' + CAST(ERROR_STATE() AS NVARCHAR(MAX)) PRINT 'ErrorLine : ' + CAST(ERROR_LINE() AS NVARCHAR(MAX)) PRINT 'ErrorMessage : ' + CAST(ERROR_MESSAGE() AS NVARCHAR(MAX)) PRINT '***********************************************************************************************************************************************************' -- FINE Procedura in errore ========================================================================================================================================================= END set @script='' return -1 END CATCH -- ##############################################################################################################################################################################to exec it:
declare @s varchar(max) exec [util_ScriptTable] 'db','schema_source','table_source',1,1,'schema_dest','tab_dest',0,@s output select @sit does not working !!
I updated the script: fixed TEXT column support and added ability to Create Table And Copy Data. It could be use instead of 'SELECT * INTO NewTable FROM OldTable', which copied data, but not schema. See https://gist.github.com/MNF/eb905c7ec215670a3abc62f9c2cf9d7b
This might be using a jackhammer to put a nail in a wall, but given the broadness of the question I think it is a valid option to mention.
If you're using SQL Server 2012 SP4+, 2014 SP2+ or 2016 SP1+, you can leverage
DBCC CLONEDATABASEto create a schema-only copy of your database sans data. This is ideal for generating comprehensive schema copies of multiple tables and may alleviate the need to "automate" the process of looping through a series of tables, but be warned that all of the table copies will be created inside of a new read-only database.These tables will include foreign keys, primary keys, indexes, and constraints. They will also include statistics and query store data (unless you specify
NO_STATISTICSandNO_QUERYSTORE).The syntax is
DBCC CLONEDATABASE (source_database_name, target_database_name)[WITH [NO_STATISTICS][,NO_QUERYSTORE]]There are also a couple other caveats to be aware of, which Brent Ozar has a great post on, but it all really boils down to how and why you want to create copies of tables as to whether or not any of the subtleties are deal breakers.
You can use the "Generate script" command in SQL Server Management Studio to get a script that can create your table, including indexes, triggers, foreign keys, etc.
In SSMS
- Right-click on the database the table is in
- Choose Tasks -> Generate scripts
- Choose "Select specific database objects"
- Expand the "Tables" list, and check the checkbox next to the table you want to script
- Click "Next" to go to the next page of the wizard
- Set the save options as you want them, then click the "Advanced" button
- Set the advanced options as you want them. In particular, under "Table/View Options", note that (by default) indexes, triggers, and full-text indexes are not scripted; if you want those, switch them from "False" to "True"
- When done, click "OK" to save your advanced options, and "Next" to review your selections. Click "Next" again to actually generate the script.
You can then edit to include just what you need in your destination database.
Here is a version based on the one by E.Mantovanelli in this thread. This corrects an issue where a unique index did not include the key word UNIQUE in the resulting script. It also adds parameters so a table can be created without non-Clustered indexes or you can only script the non-clustered indexes. I use this to Create a stage table, load it add the non-Clustered indexes then do a table switch, which allow the load to run faster and the indexes are not fragmented.
SET ANSI_NULLS ON; GO SET QUOTED_IDENTIFIER ON; GO --*************************************************************************************************' -- La procedura crea lo script di una tabella -- Tabella : xxxxx -- Creata da : E.Mantovanelli -- Data creazione : 28-06-2012 -- Data modifica: 28-06-2012 --*************************************************************************************************' /* -- ID----|-----Data-----|-- User --------- | ---- Note 20-11-2013 E.Mantovanelli distinzione schema delle tabelle estrazione da db selezionato aggiunta estrazione partizione */ --*************************************************************************************************' -- Creates a copy of a table with optionally all indexes and constraints depending on parameters -- Updated by : Brent Willis -- Date Updated : 09-11-2019 -- Changes: Added the ability to create a table with clustered index and constraints only or to -- CREATE just the non-clustered indexes. This is helpful so a table can be created, -- loaded then non-clusted indexes added so it can them be swapped into a partitioned -- table with freshly created indexes to lower fragmentation. -- -- Also fixed an issue when a unique index was defined the index resulting table was not unique. --*************************************************************************************************' ALTER PROCEDURE dbo.util_ScriptTable @DBName sysname , @schema sysname , @TableName sysname , @includeTable Bit = 1 , @IncludeConstraints Bit = 1 , @IncludeClusteredIndex Bit = 1 , @IncludeIndexes Bit = 1 , @NewTableSchema sysname , @NewTableName sysname = NULL , @UseSystemDataTypes Bit = 0 , @script Varchar(MAX) OUTPUT AS BEGIN TRY IF NOT EXISTS (SELECT * FROM sys.types WHERE name = 'TestTableType') CREATE TYPE TestTableType AS TABLE (ObjectID Int); --drop type TestTableType DECLARE @sql NVarchar(MAX); 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 sys.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 Varchar(10) , NumericPrecision Int , NumericScale Int , DomainName Varchar(100) , FieldListingName Varchar(110) , FieldDefinition Char(1) , IdentityColumn Bit , IdentitySeed Int , IdentityIncrement Int , IsCharColumn Bit , IsComputed Varchar(255)); 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 @Definition Table (DefinitionID SmallInt IDENTITY(1, 1), FieldValue Varchar(2000)); SET @sql = N' use ' + @DBName + N' SELECT distinct DB_NAME() ,TABLE_SCHEMA ,TABLE_NAME ,''[''+COLUMN_NAME+'']'' as 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 ,case CHARACTER_MAXIMUM_LENGTH when -1 then ''max'' else CAST(CHARACTER_MAXIMUM_LENGTH AS varchar) end--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 ,cc.definition 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 join sys.computed_columns cc on c.TABLE_NAME=OBJECT_NAME(cc.object_id) and sc.column_id=cc.column_id WHERE c.TABLE_NAME = @TableName and [email protected] ORDER BY c.TABLE_NAME, c.ORDINAL_POSITION ' ; --PRINT @sql; INSERT INTO @ShowFields (DatabaseName , TableOwner , TableName , FieldName , ColumnPosition , ColumnDefaultValue , ColumnDefaultName , IsNullable , DataType , MaxLength , NumericPrecision , NumericScale , DomainName , FieldListingName , FieldDefinition , IdentityColumn , IdentitySeed , IdentityIncrement , IsCharColumn , IsComputed) EXEC sys.sp_executesql @sql , N'@TableName varchar(50),@schema varchar(50)' , @TableName = @TableName , @schema = @schema; SELECT TOP 1 @TableSchema = TableOwner FROM @ShowFields; INSERT INTO @HoldingArea (Flds) VALUES ('('); IF @includeTable = 1 BEGIN INSERT INTO @Definition (FieldValue) VALUES ( 'CREATE TABLE ' + CASE WHEN @NewTableName IS NOT NULL THEN @DBName + '.' + @NewTableSchema + '.' + @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 CASE WHEN IsComputed IS NULL THEN Upper(DataType) + CASE WHEN IsCharColumn = 1 THEN '(' + Cast(MaxLength AS Varchar(10)) + ')' ELSE CASE WHEN DataType = 'numeric' THEN '(' + Cast(NumericPrecision AS Varchar(10)) + ',' + Cast(NumericScale AS Varchar(10)) + ')' ELSE CASE WHEN DataType = 'decimal' THEN '(' + Cast(NumericPrecision AS Varchar(10)) + ',' + Cast(NumericScale AS Varchar(10)) + ')' ELSE '' END END 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 [' + Replace(ColumnDefaultName, @TableName, @NewTableName) + '] DEFAULT' + Upper(ColumnDefaultValue) ELSE '' END ELSE ' as ' + IsComputed + ' ' END END + CASE WHEN FieldID = (SELECT Max(FieldID)FROM @ShowFields) THEN '' ELSE ',' END FROM @ShowFields; -------------------------------------------------- IF @IncludeConstraints = 1 BEGIN SET @sql = N' use ' + @DBName + N' SELECT distinct '',CONSTRAINT ['' + replace(name,@TableName,@NewTableName) + ''] 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 inner join sys.schemas s on fk.schema_id=s.schema_id and [email protected]) a WHERE ParentObject = @TableName ' ; --PRINT @sql; INSERT INTO @Definition (FieldValue) EXEC sys.sp_executesql @sql , N'@TableName varchar(50),@NewTableName varchar(50),@schema varchar(50)' , @TableName = @TableName , @NewTableName = @NewTableName , @schema = @schema; SET @sql = N' use ' + @DBName + N' SELECT distinct '',CONSTRAINT ['' + replace(c.name,@TableName,@NewTableName) + ''] CHECK '' + definition FROM sys.check_constraints c join sys.schemas s on c.schema_id=s.schema_id and [email protected] WHERE OBJECT_NAME(parent_object_id) = @TableName ' ; --PRINT @sql; INSERT INTO @Definition (FieldValue) EXEC sys.sp_executesql @sql , N'@TableName varchar(50),@NewTableName varchar(50),@schema varchar(50)' , @TableName = @TableName , @NewTableName = @NewTableName , @schema = @schema; SET @sql = N' use ' + @DBName + N' 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 join sys.schemas s on cco.schema_id=s.schema_id and [email protected] WHERE OBJECT_NAME(parent_object_id) = @TableName AND i.type = 1 AND is_primary_key = 1 ' ; --PRINT @sql; INSERT INTO @PKObjectID (ObjectID) EXEC sys.sp_executesql @sql , N'@TableName varchar(50),@schema varchar(50)' , @TableName = @TableName , @schema = @schema; SET @sql = N' use ' + @DBName + N' 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 join sys.schemas s on cco.schema_id=s.schema_id and [email protected] WHERE OBJECT_NAME(parent_object_id) = @TableName AND i.type = 2 AND is_primary_key = 0 AND is_unique_constraint = 1 ' ; --PRINT @sql; INSERT INTO @Uniques (ObjectID) EXEC sys.sp_executesql @sql , N'@TableName varchar(50),@schema varchar(50)' , @TableName = @TableName , @schema = @schema; SET @ClusteredPK = CASE WHEN @@ROWCOUNT > 0 THEN 1 ELSE 0 END; DECLARE @t TestTableType; INSERT @t SELECT * FROM @PKObjectID; DECLARE @u TestTableType; INSERT @u SELECT * FROM @Uniques; SET @sql = N' use ' + @DBName + N' SELECT distinct '',CONSTRAINT '' + replace(cco.name,@TableName,@NewTableName) + 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 order by key_ordinal FOR XML PATH(''''))), 2, 8000)) + '')'' FROM sys.key_constraints cco inner join sys.schemas s on cco.schema_id=s.schema_id and [email protected] LEFT JOIN @U u ON cco.object_id = u.objectID LEFT JOIN @t pk ON cco.object_id = pk.ObjectID WHERE OBJECT_NAME(cco.parent_object_id) = @TableName ' ; --PRINT @sql; INSERT INTO @Definition (FieldValue) EXEC sys.sp_executesql @sql , N'@TableName varchar(50),@NewTableName varchar(50),@schema varchar(50),@t TestTableType readonly,@u TestTableType readonly' , @TableName = @TableName , @NewTableName = @NewTableName , @schema = @schema , @t = @t , @u = @u; END; INSERT INTO @Definition (FieldValue) VALUES (')'); END; SET @sql = N' use ' + @DBName + N' select '' on '' + d.name + ''([''+c.name+''])'' from sys.tables t join sys.indexes i on(i.object_id = t.object_id and i.index_id < 2) join sys.index_columns ic on(ic.partition_ordinal > 0 and ic.index_id = i.index_id and ic.object_id = t.object_id) join sys.columns c on(c.object_id = ic.object_id and c.column_id = ic.column_id) join sys.schemas s on t.schema_id=s.schema_id join sys.data_spaces d on i.data_space_id=d.data_space_id where [email protected] and [email protected] order by key_ordinal '; PRINT 'x'; --PRINT @sql; INSERT INTO @Definition (FieldValue) EXEC sys.sp_executesql @sql , N'@TableName varchar(50),@schema varchar(50)' , @TableName = @TableName , @schema = @schema; IF @IncludeClusteredIndex = 1 BEGIN SET @sql = N' use ' + @DBName + N' SELECT distinct '' CREATE '' + CASE WHEN i.is_unique = 1 THEN ''UNIQUE '' ELSE '''' end + i.type_desc + '' INDEX ['' + replace(i.name COLLATE SQL_Latin1_General_CP1_CI_AS,@TableName,@NewTableName) + ''] ON ' + @DBName + N'.' + @NewTableSchema + N'.' + @NewTableName + N' ('' + 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 [email protected] AND sc.object_id = i.object_id AND sc.index_id = i.index_id and is_included_column=0 ORDER BY key_ordinal ASC FOR XML PATH('''') )), 2, 8000)) + '')''+ ISNULL( '' include (''+REVERSE(SUBSTRING(REVERSE(( SELECT name + '','' FROM sys.index_columns sc JOIN sys.columns c ON sc.object_id = c.object_id AND sc.column_id = c.column_id WHERE [email protected] AND sc.object_id = i.object_id AND sc.index_id = i.index_id and is_included_column=1 ORDER BY key_ordinal ASC FOR XML PATH('''') )), 2, 8000))+'')'' ,'''')+'''' FROM sys.indexes i join sys.tables t on i.object_id=t.object_id join sys.schemas s on t.schema_id=s.schema_id AND CASE WHEN @ClusteredPK = 1 AND is_primary_key = 1 AND i.type = 1 THEN 0 ELSE 1 END = 1 AND is_unique_constraint = 0 AND is_primary_key = 0 where [email protected] and [email protected] and i.type_desc = ''CLUSTERED'' ' ; --PRINT @sql; INSERT INTO @Definition (FieldValue) EXEC sys.sp_executesql @sql , N'@TableName varchar(50),@NewTableName varchar(50),@schema varchar(50), @ClusteredPK bit' , @TableName = @TableName , @NewTableName = @NewTableName , @schema = @schema , @ClusteredPK = @ClusteredPK; END; IF @IncludeIndexes = 1 BEGIN SET @sql = N' use ' + @DBName + N' SELECT distinct '' CREATE '' + CASE WHEN i.is_unique = 1 THEN ''UNIQUE '' ELSE '''' end + i.type_desc + '' INDEX ['' + replace(i.name COLLATE SQL_Latin1_General_CP1_CI_AS,@TableName,@NewTableName) + ''] ON ' + @DBName + N'.' + @NewTableSchema + N'.' + @NewTableName + N' ('' + 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 [email protected] AND sc.object_id = i.object_id AND sc.index_id = i.index_id and is_included_column=0 ORDER BY key_ordinal ASC FOR XML PATH('''') )), 2, 8000)) + '')''+ ISNULL( '' include (''+REVERSE(SUBSTRING(REVERSE(( SELECT name + '','' FROM sys.index_columns sc JOIN sys.columns c ON sc.object_id = c.object_id AND sc.column_id = c.column_id WHERE [email protected] AND sc.object_id = i.object_id AND sc.index_id = i.index_id and is_included_column=1 ORDER BY key_ordinal ASC FOR XML PATH('''') )), 2, 8000))+'')'' ,'''')+'''' FROM sys.indexes i join sys.tables t on i.object_id=t.object_id join sys.schemas s on t.schema_id=s.schema_id AND CASE WHEN @ClusteredPK = 1 AND is_primary_key = 1 AND i.type = 1 THEN 0 ELSE 1 END = 1 AND is_unique_constraint = 0 AND is_primary_key = 0 where [email protected] and [email protected] and i.type_desc <> ''CLUSTERED'' ' ; PRINT @sql; INSERT INTO @Definition (FieldValue) EXEC sys.sp_executesql @sql , N'@TableName varchar(50),@NewTableName varchar(50),@schema varchar(50), @ClusteredPK bit' , @TableName = @TableName , @NewTableName = @NewTableName , @schema = @schema , @ClusteredPK = @ClusteredPK; END; --SELECT * FROM @Definition; INSERT INTO @MainDefinition (FieldValue) SELECT FieldValue FROM @Definition ORDER BY DefinitionID ASC; --SELECT * FROM @MainDefinition; ---------------------------------- DECLARE @q Varchar(MAX); SET @q = (SELECT Replace((SELECT FieldValue FROM @MainDefinition FOR XML PATH('')), '</FieldValue>', '')); SET @script = (SELECT Replace(@q, '<FieldValue>', '')); END TRY -- ############################################################################################################################################################################## BEGIN CATCH BEGIN -- INIZIO Procedura in errore ========================================================================================================================================================= PRINT '***********************************************************************************************************************************************************'; PRINT 'ErrorNumber : ' + Cast(Error_Number() AS NVarchar(MAX)); PRINT 'ErrorSeverity : ' + Cast(Error_Severity() AS NVarchar(MAX)); PRINT 'ErrorState : ' + Cast(Error_State() AS NVarchar(MAX)); PRINT 'ErrorLine : ' + Cast(Error_Line() AS NVarchar(MAX)); PRINT 'ErrorMessage : ' + Cast(Error_Message() AS NVarchar(MAX)); PRINT '***********************************************************************************************************************************************************'; -- FINE Procedura in errore ========================================================================================================================================================= END; SET @script = ''; RETURN -1; END CATCH; -- ##############################################################################################################################################################################You could use this script to copy a table structure with foreign keys but without indexes. This script handles user defined types and computed columns gracefully.
--\ ---) Author: Hans Michiels ---) Script to copy a sql server table structure with foreign keys but without indexes. ---) This script handles user defined types and computed columns gracefully. --/ /* (c) Copyright 2016 - hansmichiels.com This program is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version. This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details. You should have received a copy of the GNU General Public License along with this program. If not, see <http://www.gnu.org/licenses/>. */ --\ ---) VARIABLES DECLARATIONS --/ DECLARE @CrLf NVARCHAR(2) DECLARE @Indent NVARCHAR(2) DECLARE @nsql NVARCHAR(MAX) DECLARE @SimulationMode CHAR(1) DECLARE @SourceSchemaAndTable NVARCHAR(260) DECLARE @TargetSchemaAndTable NVARCHAR(260) DECLARE @FkNameSuffix NVARCHAR(128) DECLARE @TableOptions NVARCHAR(500) --\ ---) CONFIGURATION: set the source and target schema/tablename here, and some other settings. --/ SELECT @SimulationMode = 'Y' -- Use Y if you only want the SQL statement in the output window without it being executed. , @SourceSchemaAndTable = '[Production].[Product]' , @TargetSchemaAndTable = '[stg].[Product]' , @TableOptions = ' WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]' , @FkNameSuffix = '_' + REPLACE(CAST(NEWID() AS VARCHAR(40)), '-', '') -- A Guid is added to the foreign key name to make it unique. , @CrLf = CHAR(13) + CHAR(10) , @Indent = SPACE(2) -- For 'min' script use this (in case sql is near 4000 characters): -- , @CrLf = ' ' -- , @Indent = '' --\ ---) BUILD SQL FOR CLONING TABLE --/ SELECT @nsql = ISNULL(@nsql, '') + CASE col_sequence WHEN 1 THEN @CrLf + 'IF OBJECT_ID(N''' + @TargetSchemaAndTable + ''', ''U'') IS NOT NULL DROP TABLE ' + @TargetSchemaAndTable + ';' + @CrLf + 'CREATE TABLE ' + @TargetSchemaAndTable + @CrLf + @Indent + '( ' ELSE @CrLf + @Indent + ', ' END + [definition] FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY tb.object_id ORDER BY tb.object_id, col.column_id) AS col_sequence , QUOTENAME(col.name) + ' ' + COALESCE( 'AS ' + cmp.definition + CASE ISNULL(cmp.is_persisted, 0) WHEN 1 THEN ' PERSISTED ' ELSE '' END, CASE WHEN col.system_type_id != col.user_type_id THEN QUOTENAME(usr_tp.schema_name) + '.' + QUOTENAME(usr_tp.name) ELSE QUOTENAME(sys_tp.name) + CASE WHEN sys_tp.name IN ('char', 'varchar', 'binary', 'varbinary') THEN '(' + CONVERT(VARCHAR, CASE col.max_length WHEN -1 THEN 'max' ELSE CAST(col.max_length AS varchar(10)) END) + ')' WHEN sys_tp.name IN ('nchar', 'nvarchar') THEN '(' + CONVERT(VARCHAR, CASE col.max_length WHEN -1 THEN 'max' ELSE CAST(col.max_length/2 AS varchar(10)) END) + ')' WHEN sys_tp.name IN ('decimal', 'numeric') THEN '(' + CAST(col.precision AS VARCHAR) + ',' + CAST(col.scale AS VARCHAR) + ')' WHEN sys_tp.name IN ('datetime2') THEN '(' + CAST(col.scale AS VARCHAR) + ')' ELSE '' END END ) + CASE col.is_nullable WHEN 0 THEN ' NOT' ELSE '' END + ' NULL' AS [definition] FROM sys.tables tb JOIN sys.schemas sch ON sch.schema_id = tb.schema_id JOIN sys.columns col ON col.object_id = tb.object_id JOIN sys.types sys_tp ON col.system_type_id = sys_tp.system_type_id AND col.system_type_id = sys_tp.user_type_id LEFT JOIN ( SELECT tp.*, sch.name AS [schema_name] FROM sys.types tp JOIN sys.schemas sch ON tp.schema_id = sch.schema_id ) usr_tp ON col.system_type_id = usr_tp.system_type_id AND col.user_type_id = usr_tp.user_type_id LEFT JOIN sys.computed_columns cmp ON cmp.object_id = tb.object_id AND cmp.column_id = col.column_id WHERE tb.object_id = OBJECT_ID(@SourceSchemaAndTable, 'U') ) subqry ; SELECT @nsql = ISNULL(@nsql, '') + CASE col_sequence WHEN 1 THEN @CrLf + ', PRIMARY KEY ' + CASE is_clustered_index WHEN 1 THEN 'CLUSTERED' ELSE 'NONCLUSTERED' END + @CrLf + @Indent + '( ' ELSE @CrLf + @Indent + ', ' END + QUOTENAME(pk_cols.column_name) + CASE is_descending_key WHEN 1 THEN ' DESC' ELSE ' ASC' END FROM ( SELECT TOP 2147483647 sch.name as schema_name, tb.name as table_name, col.name as column_name , ROW_NUMBER() OVER (PARTITION BY tb.object_id ORDER BY tb.object_id, col.column_id) AS col_sequence , ic.is_descending_key , CASE WHEN idx.index_id = 1 THEN 1 ELSE 0 END AS [is_clustered_index] FROM sys.tables tb JOIN sys.schemas sch ON sch.schema_id = tb.schema_id JOIN sys.indexes idx ON idx.is_primary_key = 1 AND idx.object_id = tb.object_id JOIN sys.index_columns ic ON is_included_column = 0 AND ic.object_id = tb.object_id AND ic.index_id = idx.index_id JOIN sys.columns col ON col.column_id = ic.column_id AND col.object_id = tb.object_id WHERE tb.object_id = OBJECT_ID(@SourceSchemaAndTable, 'U') ORDER BY col.column_id ) pk_cols SELECT @nsql = @nsql + @CrLf + @indent + ') ' + @TableOptions IF @SimulationMode = 'Y' BEGIN PRINT '-- Simulation mode: script is not executed.' END PRINT @nsql; IF @SimulationMode != 'Y' BEGIN EXEC(@nsql); END --\ ---) Copy foreign key constraints ---) A guid is added to the foreign key name to make it unique within the database. --/ SET @nsql = N''; SELECT @nsql += N' ALTER TABLE ' + @TargetSchemaAndTable + ' ADD CONSTRAINT [' + LEFT(fk.name + @FkNameSuffix, 128) + '] ' + ' FOREIGN KEY (' + STUFF((SELECT ',' + QUOTENAME(col.name) -- get all the columns in the constraint table FROM sys.columns AS col JOIN sys.foreign_key_columns AS fkc ON fkc.parent_column_id = col.column_id AND fkc.parent_object_id = col.[object_id] WHERE fkc.constraint_object_id = fk.[object_id] ORDER BY fkc.constraint_column_id FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'') + ') REFERENCES ' + QUOTENAME(rs.name) + '.' + QUOTENAME(rtb.name) + '(' + STUFF((SELECT ',' + QUOTENAME(col.name) -- get all the referenced columns FROM sys.columns AS col JOIN sys.foreign_key_columns AS fkc ON fkc.referenced_column_id = col.column_id AND fkc.referenced_object_id = col.[object_id] WHERE fkc.constraint_object_id = fk.object_id ORDER BY fkc.constraint_column_id FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'') + ');' FROM sys.foreign_keys AS fk JOIN sys.tables AS rtb -- referenced table ON fk.referenced_object_id = rtb.[object_id] JOIN sys.schemas AS rs ON rtb.[schema_id] = rs.[schema_id] JOIN sys.tables AS ctb -- constraint table ON fk.parent_object_id = ctb.[object_id] WHERE rtb.is_ms_shipped = 0 AND ctb.is_ms_shipped = 0 AND ctb.object_id = OBJECT_ID(@SourceSchemaAndTable, 'U'); IF @SimulationMode = 'Y' BEGIN PRINT '-- Simulation mode: script is not executed.' END PRINT @nsql; IF @SimulationMode != 'Y' BEGIN EXEC(@nsql); ENDIf you are interested you can find it also on my blog: http://www.hansmichiels.com/2016/02/18/how-to-copy-a-database-table-structure-t-sql-scripting-series-s01e01/
Select * into [new table name] from [table to be copied name] where 0=1
License under CC-BY-SA with attribution
Content dated before 6/26/2020 9:53 AM
Alsin 3 years ago
Please note that sys.dm_exec_describe_first_result_set will bring you nvarchar(27) instead of datetime2 if executed over OLEDB.