I was reading recently Pinal Dave blog asking about a script so we can create all the primary keys in a data base.

Here is my answer

Version:0.9 StartHTML:-1 EndHTML:-1 StartFragment:0000000111 EndFragment:0000010215

DECLARE @NewLinechar CHAR(2)

SET @NewLinechar =   CHAR(13) + CHAR(10) – This one came from one of your articles

SELECT ‘ALTER TABLE [' + s.name + '].[' + t.name + '] ADD CONSTRAINT [' + c.name + ']‘ + @NewLinechar + ‘PRIMARY KEY ’ + i.type_desc collate SQL_Latin1_General_CP1_CI_AS + @NewLinechar + ‘([' + col.name +'] ASC’ + @NewLinechar + ‘) on [' + ds.name + ']‘ + @NewLinechar + ‘GO’

FROM sys.key_constraints c

JOIN sys.tables t

ON c.parent_object_id = t.object_Id

JOIN sys.schemas s

ON t.schema_id = s.schema_id

JOIN sys.indexes i

ON c.unique_index_id = i.index_id

JOIN sys.index_columns ic

ON i.object_id = ic.object_id

AND i.index_id  = ic.index_id

JOIN sys.columns col

ON t.object_id  = col.object_id

AND ic.column_id = col.column_id

JOIN sys.data_spaces ds

ON i.data_space_id = ds.data_space_id

WHERE c.type = ‘PK’