The below script generates primary key across all the tables in the database.
;with CTE as (select distinct COLUMN_NAME,ORDINAL_POSITION,TABLE_NAME,b.name,c.index_id,
b.[object_id] as ObjectID,b.parent_object_id as [TableObjectID]
from INFORMATION_SCHEMA.KEY_COLUMN_USAGE A Inner Join sys.key_constraints B on
A.TABLE_NAME=Object_name(B.parent_object_id) and SCHEMA_NAME(SCHEMA_ID)=TABLE_SCHEMA
and A.Constraint_Name=B.Name
left outer join sys.indexes C on C.object_id=B.parent_object_id and is_primary_key=1)
,CTE2 as (select distinct T2.[objectid],T2.TableObjectID,T2.index_id,
STUFF((Select ','+Column_Name
from CTE T1
where T1.[objectid]=T2.[Objectid] and T1.[Tableobjectid]=T2.[TableObjectid]
and T1.[index_id]=T2.[index_id] order by ORDINAL_POSITION asc
FOR XML PATH('')),1,1,'') as [keycolumns] from CTE T2 )
Select distinct Table_Schema+'.'+Table_Name as [TableName],CONSTRAINT_NAME,'ALTER TABLE '+Table_Name +' ADD CONSTRAINT '+Name+ ' PRIMARY KEY '+
case when index_id=1 then ' CLUSTERED '
when index_id>1 then ' NONCLUSTERED '
when index_id=0 then '' End
+'('+keycolumns +');' as [Create Script]
from INFORMATION_SCHEMA.KEY_COLUMN_USAGE A Inner Join sys.key_constraints B on
A.TABLE_NAME=Object_name(B.parent_object_id) and A.CONSTRAINT_NAME=B.Name
Inner join CTE2 C on C.ObjectID=B.object_id and C.TableObjectID=B.parent_object_id
SQL Server Repository 