复制表结构，与源表结构相等，包括主键和索引

--只复制表结构，与源表结构相等，包括主键和索引,
--只可惜只能复制一个主键
--改数据库名，table1 改为旧表，table2改为新表,今天研究了下怎么完全复制一个表，包括表结构，主键和索引。下面是SQL，
--注：两个表在一个库中。这段script可以重复跑
--SELECT * INTO ou_issues_msg_detail FROM Ou_Issues_Msg_Detail_bak
--SELECT * FROM Ou_Issues_Msg_Detail
use DC --DBName
declare @sourceTable as varchar(20)
set  @sourceTable = 'it_user' --source table name
declare @destTable as varchar(20)
set  @destTable = 'it_user_bak' --destination table name
--select * from dbo.sysobjects where id = object_id('it_user_bak') and OBJECTPROPERTY(id, N'IsUserTable') = 1
 if exists (select * from dbo.sysobjects where id = object_id(@destTable) and OBJECTPROPERTY(id, N'IsUserTable') = 1)
exec('drop table '+@destTable)

--Get sql for adding PK 添加主键
declare @sql_PK as varchar(1000)
--Create a temp table to store PK info.创建临时表存储主键信息
create   table   #TempKeys(
dbName   varchar(50),
tableOwner   varchar(50),
tableName   varchar(50),
columnName   varchar(50),
keySeq   int,
pkName   varchar(50)
)
--insert   #TempKeys   exec sp_pkeys   'it_user'
--SELECT * FROM #TempKeys
--select * from it_user_bak
--ALTER TABLE it_user_bak ADD PRIMARY KEY (within_code,code) from #TempKeys
--下面语句查出主键within_code,code
--exec sp_pkeys 'it_user'
insert   #TempKeys   exec sp_pkeys   @sourceTable
select @sql_PK = 'ALTER TABLE '+@destTable+' ADD PRIMARY KEY('+columnName+')' from   #TempKeys
--Get sql for adding Indexes
declare @sql_IDX as varchar(1000)
--Create a temp table to store Indexes info.
create   table   #TempIDXs(
idxName   varchar(50),
idxDesc   varchar(50),
indexKeys   varchar(50),
)
insert   #TempIDXs   exec sp_helpindex  @sourceTable
select @sql_IDX =
(select  'CREATE INDEX [idx_'+@destTable+'_'+replace(replace(indexKeys,',',''),' ','')+'] ON'+@destTable+' ('+indexKeys+');' from  #TempIDXs
where idxName not in (select pkName from #TempKeys) for xml path('')
)
drop table #TempIDXs
drop table #TempKeys
--create a new table with  the same schema as source table 复制结构不复制数据
exec('select top 0 * into '+@destTable +' from '+@sourceTable)
exec (@sql_PK)
exec (@sql_IDX)