Scripting Compound Primary Keys

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')