USE = your_db-name_here
GO
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME NOT IN
(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY')
AND TABLE_TYPE = 'BASE TABLE' and OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)) IN (select object_id from sys.objects where type = 'U' and is_ms_shipped = 0) ORDER BY TABLE_NAME
Reminder: In SQL Server all tables should have a primary key, and all tables must have a clustered index.
But the clustered index and the primary key are not mutual exclusive. They could differ and this depends on your requirements. As a general rule of thumb: clustered indexes perform very when handling ranges (so a date-field could be a good candidate, when a query selects data for a month or so)
also keep in mind that a clustered index is:
- narrow
- increasing
- not modifiable
Because a table only can have one clustered index, you should design with care and not assign an identity column as the 'clustered_index_primary_key' by default. I have seen this too many times. see my post on auto index management and let SQL Server assist you
