Hi all
I have a requirment to dynamically script a whole database schema. Scripting tables, constraints defaults etc. This all works well until I come up against compound primary key constraints then using Information_schema or system tables I only seem to get to the first column making up the constraint. Anyone have a snippet of script which can list the primary key and all its columns for a given table?
Currently using SQL 2000 and shortly moving to SQL 2005.
Thanks
The following query may give idea...
CREATEProc TableCreationText
(
@.TableName Nvarchar(2000)
)
as
Create Table #Text
(
Source NVarchar(4000)
)
Insert Into #Text(Source) Values ('Create Table ' + @.TableName + '(');
Insert Into #Text(Source)
Select
'[' + C.Name + '] ' +Ty.name + Case When C.Scale Is NULL Then '(' + Cast(C.Length as Varchar) + ') ' Else '' End +
Case When C.IsNullable =0 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 + ' Check ' + comments.Text ,'')
+ Isnull(' Default ' + defcomments.text ,'') + ','
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
Where
T.Type='U'
And T.Name=@.TableName
Order By
T.Name,
C.Colorder
Insert Into #Text(Source)
Select
'Constraint [' + ind.name + '] ' + case when xtype='PK' Then ' Primary Key ' Else ' Unique ' End+ Case when ind.status & 16=16 Then ' clustered ' Else ' nonclustered' End+'(' +dbo.GetAllIndexedColumns(@.TableName, 2)+ '),'
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 + '] 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 (')');
Select Source From #Text
|||Hi,
Could you also provide script for dbo.GetAllIndexedColumns this function.
Zafar|||
Thanks for that
dbo.GetAllIndexedCollumns is this a hellper stored proc ? The script fails at this point when I run it on my system.
|||oops.. I missed that.. here it is..
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
|||
For some reason the script only reports the second of the columns involved in the compound key. I figure that the clues are in there I just need to keep plugging away at it...
Thanks
|||Yes.. I didn't tested properly in SQL Server 2005..
here the updated query..
Code Snippet
Declare @.tablename as varchar(100)
Set @.tablename = 'YourTablename'
Select
'Constraint [' + ind.name + '] ' + case when xtype='PK' Then ' Primary Key ' Else ' Unique ' End + Case when ind.status & 16=16 Then ' clustered ' Else ' nonclustered' End + '(' + dbo.GetAllIndexedColumns(@.TableName, ind.indid) + '),'
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')
