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; GO About the above function OBJECTPROPERY, BOL refers: This function cannot be used for objects that are not schema-scoped, such as data definition language (DDL) triggers and event notifications....(
read more)
Read the complete post at http://sqlserver-qa.net/blogs/t-sql/archive/2007/08/02/1227.aspx