How to list all table without a primary key within SQL Server 2005 database?

Hi, All,

Would please any expert give me any advice and guidance for how can we list all tables without any primary key within a SQL Server 2005 database?

Thanks a lot in advance for that.

With best regards,

Yours sincerelyd,

Switch to the database you want to analyse and run this script:

select

sys.schemas.nameas [Schema],

sys.tables.nameas [Table]

fromsys.tables

leftouterjoinsys.schemasonsys.schemas.schema_id =sys.tables.schema_id

wheresys.tables.object_idnotin

(

select

sys.indexes.object_id

fromsys.indexes

wheresys.indexes.is_primary_key = 1

)

|||

Hi, Daniel, thanks a lot.

With best regards,

Yours sincerely,

|||

You can also run

USE <database_name>;
GO
SELECT SCHEMA_NAME(schema_id) AS schema_name,name AS table_name
FROM sys.tables
WHERE OBJECTPROPERTY(object_id,'TableHasPrimaryKey') = 0
ORDER BY schema_name, table_name;

Please take a look at the FAQ in msdn2 for catalog views. The FAQ is at http://msdn2.microsoft.com/en-us/library/ms345522.aspx. It answers a lot of catalog view related queries.

|||

Hi, Asvin, Thanks a lot for your kind guidance.

With best regards,

Yours sincerely,