A script to test existence of primary keys
;WITH tables_with_pk AS (
SELECT t.table_schema, t.table_name
FROM INFORMATION_SCHEMA.TABLES t
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
ON t.TABLE_NAME = tc.TABLE_NAME AND t.table_schema = tc.table_schema
WHERE tc.constraint_type = 'PRIMARY KEY'
)
SELECT t.table_schema, t.table_name
FROM INFORMATION_SCHEMA.TABLES t
EXCEPT
SELECT table_schema, table_name
FROM tables_with_pk
I don't have the exact/complete code for you, but here's the idea:
You'll need to loop through your list of tables in the database:
SELECT *
FROM information_schema.tables
The code to check if a Primary Key exists for your table would be something like:
SELECT *
FROM information_schema.table_constraints
WHERE constraint_type = 'PRIMARY KEY'
AND table_name = @Your_Table_Name
Returns 0 if no primary key, Returns 1 if there is a primary key
SELECT OBJECTPROPERTY(OBJECT_ID(N'MyTable'),'TableHasPrimaryKey')