1、复制表(复制结构和指定内容,源表名:A(a,b,c),新表名:B)select * into B from A where ... ...2、拷贝数据(源表名:A(a,b,c),目标表名:B(d,e,f))insert into B(d,e,f) select a,b,c from a where ... ...
Select TableName,-2 ColOrder,1 RowOrder,'If Not Exists(Select * From sysObjects Where xtype=''u'' and Name='''+TableName+''')' From ( select a.Name TableName From dbo.sysObjects a Where a.xtype='u' ) A Union all Select TableName,-1 ColOrder,2 RowOrder,' Create Table '+TableName+'(' From ( select a.Name TableName From dbo.sysObjects a Where a.xtype='u' ) B Union all Select TableName,ColOrder,3 RowOrder,' ' + ColumnName + ' ' + Case When typeName in ('char','varchar') then 'Varchar('+Cast(ColumnLength as Varchar)+') Not Null '+ Case When cdefault<>0 then 'Default '''',' Else ',' End When typeName in ('nchar','nvarchar') then 'nVarchar('+Cast(ColumnLength/2 as varchar)+') Not Null ' + Case When cdefault<>0 then 'Default '''',' Else ',' End When typeName in ('bit','tinyint','smallint','int','bigint','float','real','smallmoney','Money') then typename + ' Not Null '+ Case When cdefault<>0 then 'Default 0,' Else ',' End When typeName in ('numeric','Decimal') Then typename + '('+Cast(Columnxprec as varchar)+','+Cast(Columnxscale as varchar)+') Not Null ' + Case When cdefault<>0 then 'Default 0,' Else ',' End When typeName in ('smalldatetime','datetime') Then typename + ' Not Null ' + Case When cdefault<>0 then 'Default getdate(),' Else ',' End when typename in ('timestamp') then typename + ' Not Null,' when typename in ('binary','varbinary') then typename + ' Not Null ' + Case When cdefault<>0 then 'Default 0,' Else ',' End Else '请注意未处理的类型' end SqlScript From ( select a.Name TableName,b.Name ColumnName,b.Length ColumnLength, b.xprec Columnxprec,b.xscale Columnxscale,b.ColOrder,b.cdefault,c.name typeName From dbo.sysObjects a inner join dbo.sysColumns b on a.ID=b.ID Inner join dbo.systypes c on b.xusertype=c.xusertype Where a.xtype='u' ) c Union all Select TableName,1000 ColOrder,4 RowOrder,')' From ( select a.Name TableName From dbo.sysObjects a Where a.xtype='u' ) d Union all Select TableName,1001 ColOrder,5 RowOrder,'Go' From ( select a.Name TableName From dbo.sysObjects a Where a.xtype='u' ) d Order by TableName,RowOrder,ColOrder 希望能对楼主有所帮助!
http://topic.csdn.net/u/20110802/14/30e4da7b-4391-4fdf-8bf0-3defb05d72d8.html
From (
select a.Name TableName
From dbo.sysObjects a
Where a.xtype='u'
) A
Union all
Select TableName,-1 ColOrder,2 RowOrder,' Create Table '+TableName+'('
From (
select a.Name TableName
From dbo.sysObjects a
Where a.xtype='u'
) B
Union all
Select TableName,ColOrder,3 RowOrder,' ' + ColumnName + ' ' +
Case When typeName in ('char','varchar') then 'Varchar('+Cast(ColumnLength as Varchar)+') Not Null '+ Case When cdefault<>0 then 'Default '''',' Else ',' End
When typeName in ('nchar','nvarchar') then 'nVarchar('+Cast(ColumnLength/2 as varchar)+') Not Null ' + Case When cdefault<>0 then 'Default '''',' Else ',' End
When typeName in ('bit','tinyint','smallint','int','bigint','float','real','smallmoney','Money') then typename + ' Not Null '+ Case When cdefault<>0 then 'Default 0,' Else ',' End
When typeName in ('numeric','Decimal') Then typename + '('+Cast(Columnxprec as varchar)+','+Cast(Columnxscale as varchar)+') Not Null ' + Case When cdefault<>0 then 'Default 0,' Else ',' End
When typeName in ('smalldatetime','datetime') Then typename + ' Not Null ' + Case When cdefault<>0 then 'Default getdate(),' Else ',' End
when typename in ('timestamp') then typename + ' Not Null,'
when typename in ('binary','varbinary') then typename + ' Not Null ' + Case When cdefault<>0 then 'Default 0,' Else ',' End
Else '请注意未处理的类型' end SqlScript
From (
select a.Name TableName,b.Name ColumnName,b.Length ColumnLength,
b.xprec Columnxprec,b.xscale Columnxscale,b.ColOrder,b.cdefault,c.name typeName
From dbo.sysObjects a
inner join dbo.sysColumns b on a.ID=b.ID
Inner join dbo.systypes c on b.xusertype=c.xusertype
Where a.xtype='u'
) c
Union all
Select TableName,1000 ColOrder,4 RowOrder,')'
From (
select a.Name TableName
From dbo.sysObjects a
Where a.xtype='u'
) d
Union all
Select TableName,1001 ColOrder,5 RowOrder,'Go'
From (
select a.Name TableName
From dbo.sysObjects a
Where a.xtype='u'
) d
Order by TableName,RowOrder,ColOrder
希望能对楼主有所帮助!