Alexander Suprun (9/15/2014)
ALTER TABLE <YourTableName> ADD ID int NOT NULL IDENTITY (1, 1)ALTER TABLE <YourTableName> ADD CONSTRAINT PK_<YourTableName> PRIMARY KEY (ID)
Alex,
That seems like an easy patch instead of doing a good analysis. That could generate more problems than it can solve.
mcinvalek,
Taking an idea from Alex's comment, you could try to see if someone did their job correctly by generating PKs or UNIQUE constraints.
WITH cteTally AS(
SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
)
SELECT OBJECT_NAME(o.parent_object_id) AS [table_name]
,o.NAME AS [constraint_name]
,CASE
WHEN o.type = 'PK'
THEN 'PRIMARY KEY'
ELSE 'UNIQUE'
END + CASE
WHEN index_id = 1
THEN ' (clustered)'
ELSE ' (non-clustered)'
END AS [TYPE]
,STUFF((
SELECT ISNULL(', ' + index_col(OBJECT_NAME(o.parent_object_id), i.index_id, n), '')
FROM cteTally
FOR XML PATH('')
), 1, 2, '') AS [Columns]
FROM sys.objects o
JOIN sys.indexes i ON o.parent_object_id = i.object_id
AND o.NAME = i.NAME
WHERE o.type in ('PK','UQ')