Find tables which doesn't have Primary Key

The below queries would list down the tables which doesn't have Primary Key in it.

In SQL Server 2000 :

Solution 1:

Select Table_name as "Table name"
From Information_schema.Tables
Where Table_type = 'BASE TABLE' and
Objectproperty (Object_id(Table_name), 'IsMsShipped') = 0 and
Objectproperty (Object_id(Table_name), 'TableHasPrimaryKey') = 0

Solution 2:

SysObjects :: Contains one row for each object that is created within a database, such as a constraint, default, log, rule, and stored procedure. No prizes for guessing 'U' refers to user tables, and 'PK' refers to Primary Keys :)

Select [name] as "Table Name without PK"
from SysObjects where xtype='U' and
id not in
(
Select parent_obj from SysObjects where xtype='PK'
)

SQL Server 2005:

Catalog views return information that is used by the Microsoft SQL Server 2005 Database Engine. We recommend that you use catalog views because they are the most general interface to the catalog metadata and provide the most efficient way to obtain, transform, and present customized forms of this information. All user-available catalog metadata is exposed through catalog views. [sys.tables is one of many catalog views introduced in SQL Server 2005]

Solution 1:

Select [name] AS table_name
from sys.tables
Where Objectproperty(object_id,'TableHasPrimaryKey') = 0

Solution 2; [Display with schema name]

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

Technorati tags:SQL Server, SQL Server 2005