select * into dbo.table2 from dbo.table1;
这是只是表格的简单副本；缺少所有索引/约束。如何复制表结构（不使用备份）？
我主要是想手动完成此操作，但是如果不可能，我将接受任何解决方案。
#1 楼
您当然可以使用UI编写相对容易的表脚本：名称（并确认不存在使用新名称的对象）。比较麻烦。上面的脚本选项不只是从元数据中的单个位置提取整个CREATE TABLE DDL；它在代码的幕后做了很多魔术工作，以生成最终的CREATE TABLE脚本（您可以使用Profiler来查看从何处获取其数据，但看不到它是如何组装的）。我为此建议了一个选项：http://connect.microsoft.com/SQLServer/feedback/details/273934
但是此提议以很少的票数获得通过，很快被微软击落。您可能会发现，使用第三方工具生成架构会更有价值（我已经在此进行过博客介绍）。
在SQL Server 2012中，有新的元数据功能可以使您与您必须在2005、2008和2008 R2中完成的工作，将元数据中的列信息拼凑在一起（其中有很多警告，例如，如果是小数，则必须添加精度/小数位数，如果[n [var [char ]]您必须添加长度说明，如果n [var] char您必须将max_length切成两半，如果它是MAX，则必须将-1更改为MAX，依此类推）。在SQL Server 2012中，这部分要简单一些：
SELECT name, system_type_name, is_nullable FROM
sys.dm_exec_describe_first_result_set('select * from sys.objects', NULL, 0)
结果：也对此发表了博客。
可以说这比使用
CREATE TABLE的复杂方法更接近您的目标CREATE TABLE语句，但是仍然有很多工作要做。键，约束，行中的文本选项，文件组信息，压缩设置，索引等。这是一个很长的列表，我再次建议您使用第三方工具代替它，否则可能会重复-类比，重新发明了轮子。这就是说，如果您需要通过代码来完成此操作，但是可以在SQL Server之外进行，则可以考虑使用SMO / PowerShell。请参阅本技巧和Scripter.Script（）方法。
#2 楼
我写了这个sp来自动创建包含所有东西，pk，fk，分区，约束的模式。 exec之前create type TestTableType as table (ObjectID int)
此处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 '+@DBName+'
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 c.TABLE_SCHEMA=@schema
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)',
@TableName=@TableName,@schema=@schema
/*
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 '+@DBName+'
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 s.name=@schema) a
WHERE ParentObject = @TableName
'
print @sql
INSERT INTO @Definition(FieldValue)
exec sp_executesql @sql,
N'@TableName varchar(50),@NewTableName varchar(50),@schema varchar(50)',
@TableName=@TableName,@NewTableName=@NewTableName,@schema=@schema
/*
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 '+@DBName+'
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 s.name=@schema
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)',
@TableName=@TableName,@NewTableName=@NewTableName,@schema=@schema
/*
SELECT ',CONSTRAINT [' + name + '] CHECK ' + definition FROM sys.check_constraints
WHERE OBJECT_NAME(parent_object_id) = @TableName
*/
set @sql=
'
use '+@DBName+'
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 s.name=@schema
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)',
@TableName=@TableName,@schema=@schema
/*
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 '+@DBName+'
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 s.name=@schema
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)',
@TableName=@TableName,@schema=@schema
/*
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 '+@DBName+'
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 s.name=@schema
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',
@TableName=@TableName,@NewTableName=@NewTableName,@schema=@schema,@t=@t,@u=@u
/*
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 '+@DBName+'
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 t.name=@TableName and s.name=@schema
order by key_ordinal
'
print 'x'
print @sql
INSERT INTO @Definition(FieldValue)
exec sp_executesql @sql,
N'@TableName varchar(50),@schema varchar(50)',
@TableName=@TableName,@schema=@schema
IF @IncludeIndexes = 1
BEGIN
set @sql=
'
use '+@DBName+'
SELECT distinct '' CREATE '' + i.type_desc + '' INDEX ['' + replace(i.name COLLATE SQL_Latin1_General_CP1_CI_AS,@TableName,@NewTableName) + ''] ON '+@DBName+'.'+@NewTableSchema+'.'+@NewTableName+' (''
+ 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 t.name=@TableName 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 t.name=@TableName 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 t.name=@TableName and s.name=@schema
'
print @sql
INSERT INTO @Definition(FieldValue)
exec 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 '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
-- ##############################################################################################################################################################################
执行它：
declare @s varchar(max)
exec [util_ScriptTable] 'db','schema_source','table_source',1,1,'schema_dest','tab_dest',0,@s output
select @s
评论
它不起作用！
–itzik Paz
16年2月1日在12:21
我更新了脚本：固定的TEXT列支持，并增加了创建表和复制数据的功能。它可以代替复制数据而不是模式的'SELECT * INTO NewTable FROM OldTable'使用。参见gist.github.com/MNF/eb905c7ec215670a3abc62f9c2cf9d7b
– Michael Freidgeim
16年5月28日在11:10
#3 楼
这可能是使用手提钻在墙上钉钉子，但是鉴于问题的广泛性，我认为这是一个有效的选择。如果您使用的是SQL Server 2012 SP4 +，2014 SP2 +或2016 SP1 +，则可以利用
DBCC CLONEDATABASE创建数据库无数据的仅架构副本。这是生成多个表的全面模式副本的理想选择，并且可以减轻“自动”遍历一系列表的过程的需要，但是要警告所有表副本都将在新的只读数据库内部创建。 这些表将包含外键，主键，索引和约束。它们还将包括统计信息和查询存储数据（除非您指定
NO_STATISTICS和NO_QUERYSTORE）。 语法是
DBCC CLONEDATABASE (source_database_name, target_database_name)[WITH [NO_STATISTICS][,NO_QUERYSTORE]]
还有其他一些注意事项，关于Brent Ozar的精彩文章，真正地归结为您要如何以及为什么要创建表副本，以了解是否任何细微之处都会破坏交易。
#4 楼
您可以在SQL Server Management Studio中使用“生成脚本”命令来获取可以创建表的脚本，包括索引，触发器，外键等。在SSMS中
右键单击表中的数据库
选择任务->生成脚本
选择“选择特定的数据库对象”
展开“表”列表，然后检查您要编写脚本的表格旁边的复选框
单击“下一步”转到向导的下一页
根据需要设置保存选项，然后单击“高级”按钮
根据需要设置高级选项。特别是，在“表/视图选项”下，请注意（默认情况下）索引，触发器和全文本索引均未编写脚本。如果需要这些选项，请将其从“假”切换为“真”
完成后，单击“确定”保存高级选项，然后单击“下一步”查看选择。再次单击“下一步”以实际生成脚本。
然后可以进行编辑以将所需的内容包括在目标数据库中。
#5 楼
这是基于此线程中E.Mantovanelli的版本。这解决了一个唯一索引在结果脚本中不包含关键字UNIQUE的问题。它还添加了参数，因此可以在没有非聚集索引的情况下创建表，或者您只能编写非聚集索引的脚本。我用它来创建一个阶段表，加载它以添加非聚集索引，然后进行表切换，这将使负载运行得更快并且索引不会分散。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 c.TABLE_SCHEMA=@schema
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 s.name=@schema) 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 s.name=@schema
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 s.name=@schema
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 s.name=@schema
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 s.name=@schema
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 t.name=@TableName and s.name=@schema
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 t.name=@TableName 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 t.name=@TableName 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 t.name=@TableName and s.name=@schema 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 t.name=@TableName 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 t.name=@TableName 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 t.name=@TableName and s.name=@schema 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;
-- ##############################################################################################################################################################################
#6 楼
您可以使用此脚本复制具有外键但不包含索引的表结构。此脚本可以优雅地处理用户定义的类型和计算的列。您有兴趣也可以在我的博客上找到它：
http://www.hansmichiels.com/2016/02/18/how-to-copy-a-database-table-structure-t-sql- scripting-series-s01e01 /
#7 楼
Select *
into [new table name]
from [table to be copied name]
where 0=1
评论
请注意，如果通过OLEDB执行，则sys.dm_exec_describe_first_result_set将带给您nvarchar（27）而不是datetime2。
–阿辛
18年1月16日在11:33
这一点并不意味着是敌对的或消极的，但这是为什么我在使用SQL 20年后就搬到Postgres的原因。 Postgres解决方案是：创建表tbl2（类似于tbl1，包括所有）； -复制表，包括所有索引注释等。
–达米恩·索耶（Damien Sawyer）
19年2月14日在8:56
@DamienSawyer公平地说，用一堆具有相同索引的相同表以及其他所有东西都是一种设计气味。并且有很多第三方工具（一些免费的，开源的）可以在您确实需要时提供帮助。
–亚伦·伯特兰（Aaron Bertrand）
19-2-14在14:13
当然，@ AaronBertrand在许多情况下都是正确的，尤其是在无所不在的3NF CRUD样式架构中。但是有时我们使用数据库的方式不那么严格，例如，将数据集中在一起并从CSV等非正式来源中清除数据，以将其迁移到生产系统中。相比之下，Excel出色的原因之一是，尽管工作簿可能变得很复杂，但执行非常简单的工作却很少。一张纸既可以用作购买房屋的合同，又可以用作购物清单的草稿。
–达米恩·索耶（Damien Sawyer）
19年2月14日在17:58
@DamienSawyer并且当您有一个中间过程时，该过程本质上是一个废弃的登台表，我认为它不必包含表中存在的所有索引，约束，扩展属性等。它被模仿。即使可能存在其他用例，默认行为也确实具有非常有效的用例。
–亚伦·伯特兰（Aaron Bertrand）
19-2-14在19:01