Please Help me... How to create duplicate table dynamically(In Code). I have written query like this... select * into table2 from table1. But this creates only structure and data. I need to create along with constraints also(Primary key, default,index). Please help out me....
It's amazing what a little searching can find.
Check out this thread from just yesterday. It tells you all you need to know.
|||
I have created one, but not tested 100%.. try this code..
Code Snippet
Create Proc sp_copytable
(
@.TableName Nvarchar(2000),
@.TargetTableName Nvarchar(2000),
@.Copydata as bit =0
)
as
Set NOCOUNT ON;
Create Table #Text
(
Source NVarchar(4000)
)
Insert Into #Text(Source) Values ('Create Table ' + @.TargetTableName + '(');
Insert Into #Text(Source)
Select
'[' + C.Name + '] ' + case when iscomputed=0 then Ty.name + Case When C.xType in (175,239,231,167) Then
'(' + Cast((C.Length/case when left(Ty.name,1) ='N' Then 2 else 1 end) as Varchar) + ') '
When C.Type=108 Then '(' + Cast(C.prec as varchar) + ',' + Cast(C.scale as varchar) + ') '
Else '' End +
Case When C.IsNullable =1 And C.Colstat & 1 <> 1 Then ' NULL ' Else ' NOT NULL ' End
+ Case When C.Colstat & 1 = 1 Then ' Identity(' + Cast(ident_seed(T.name) as varchar) + ',' + Cast(ident_incr(T.name) as Varchar) + ') ' Else '' End
+ Isnull(' Constraint [' + ChkCon.Name + '_' + @.TargetTableName + '] Check ' + comments.Text ,'')
+ Isnull(' Default ' + defcomments.text ,'') + ',' else ' as ' + computed.text + ',' end
From
Sysobjects T
Join Syscolumns C on T.id = C.Id
Join systypes Ty On C.xtype = Ty.xType And Ty.Name <> 'SysName'
Left Outer Join sysobjects ChkCon On ChkCon.parent_obj = T.Id
And ChkCon.xtype= 'C' And ChkCon.Info = C.Colorder
Left Outer Join syscomments comments ON Comments.id = ChkCon.id And Comments.colid =1
Left Outer Join sysobjects def On def.parent_obj = T.Id
And def.xtype= 'D' And def.Info = C.Colorder
Left Outer Join syscomments defcomments ON defcomments.id = def.id
Left outer join syscomments computed ON computed.id = T.id and computed.number = c.colid
Where
T.Type='U'
And T.Name=@.TableName
Order By
T.Name,
C.Colorder
Insert Into #Text(Source)
Select
'Constraint [' + ind.name + '_' + @.TargetTableName + '] ' + case when xtype='PK' Then ' Primary Key ' Else ' Unique ' End + Case when ind.status & 16=16 Then ' clustered ' Else ' nonclustered' End + '(' + dbo.GetAllIndexedColumns(@.TableName, case when xtype = 'PK' Then 1 Else 2 end) + '),'
From
sysindexes ind Join sysobjects tbl On tbl.parent_obj = object_id(@.TableName)
and ind.name = object_name(tbl.id)
and xtype in ('PK', 'UQ')
Insert Into #Text(Source)
select
'Constraint [' + tbl.name + '_' + @.TargetTableName + '] FOREIGN KEY ([' + col_name(fk.fkeyid, fk.fkey) + ']) REFERENCES [' +
object_name(fk.rkeyid) + ']([' + col_name(fk.rkeyid, fk.rkey) + ']),'
from
sysforeignkeys fk Join sysobjects tbl On tbl.parent_obj = object_id(@.TableName)
and fk.constid = tbl.id
and xtype in ('F')
Declare @.Source as Nvarchar(4000);
Select @.Source = Source From #Text;
Update #Text
Set
Source = Substring(Source,1,Len(Source)-1)
Where
Source = @.Source;
Insert Into #Text(Source) values (')');
Declare @.SQL as varchar(8000);
Set @.SQL = ''
Select @.SQL = @.SQL + '
' + Source From #Text;
--Print @.SQL
Exec(@.SQL);
Print 'Table Structered Copied'
If @.Copydata = 1
Begin
Declare @.cols as varchar(8000);
Set @.cols = ''
select @.cols = @.cols + ',[' + name + ']' from syscolumns where id=object_id(@.TargetTableName) and iscomputed =0;
Set @.cols = substring(@.cols,2,8000)
If Exists (Select * from Syscolumns Where id=object_id(@.TargetTableName) and Colstat & 1 = 1)
Begin
Exec('Set IDENTITY_INSERT [' + @.TargetTableName + '] ON;Insert Into ' + @.TargetTableName + '(' + @.cols + ') Select ' + @.cols + ' from [' + @.TableName + '];Set IDENTITY_INSERT [' + @.TargetTableName + '] OFF;')
End
Else
Begin
Exec('Insert Into ' + @.TargetTableName + '(' + @.cols + ') Select ' + @.cols + ' from [' + @.TableName + '];')
End
Print 'Table Data Copied'
End
Go
Create Function dbo.GetAllIndexedColumns(@.ObjectName nvarchar(1000), @.indexid int)
Returns NVarchar(4000)
as
Begin
Declare @.IndexedCols as NVarchar(4000);
Declare @.I as Int;
Select @.I = 1;
Select @.IndexedCols = index_col(@.ObjectName, @.indexid, @.I) + ' ' + Case When indexkey_property(object_id(@.ObjectName),@.indexid, @.i, 'isdescending') = 1 Then ' Desc ' Else '' End
Select @.I = @.I +1;
While index_col(@.ObjectName, @.indexid, @.I) is not null
Begin
Select @.IndexedCols = @.IndexedCols + ',' + index_col(@.ObjectName, @.indexid, @.I) + ' ' + Case When indexkey_property(object_id(@.ObjectName),@.indexid, @.i, 'isdescending') = 1 Then ' Desc ' Else '' End
Select @.I = @.I +1;
End
return @.IndexedCols;
End
Go
exec sp_copytable 'Orders', 'Order1', 1 -- with data; for only structure use 0
|||Hi Manivannan, Thanks for replying me... I execute Query(Stored Procedure, Function) what u have send to me.. It Successfully created. When i tried to create duplicate table it is not creating NULL values and error is showing for text datatype...(Cannot specify a column width on datatype text).. Please help out me Sir...
|||I fixed the bugs.. check now 
Thanxs for immediate reply Manivannan.. Sir i am getting new error(String or binary data would be truncated) when i create duplicate table. For new table varchar size is taking 1.
|||Hi Manivannan.Sorry for late .I tried to execute Query but i am getting new error(String or binary data would be truncated) when i create duplicate table. For new table varchar size is taking 1. Please Help me...
|||Which db you are using SQL Server 2000/2005?|||Hi Manivannan..we are using Sql server 2000.|||
I fixed the bug again.. 
.
Can you check now?
|||
Hi Manivannan... Now i am able to Create. Thank u Very Much...
