This is my query to find out primary key, foreign key in SQL Server 2008 R2:
select * from information_schema.table_constraints where constraint_type = 'PRIMARY KEY'
Know I need to select without primary key table in my database.
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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'
Edit: As per the comment from @MarkSinkinson this query will be wrong in case of Null value
information_schema.table_constraints.table_name, so I would advice the OP to follow with link added in comment which is
SELECT OBJECT_SCHEMA_NAME( object_id ) as SchemaName, name AS TableName FROM sys.tables WHERE OBJECTPROPERTY(object_id,'tablehasprimaryKey') = 0 ORDER BY SchemaName, TableName ;
to get the list of table not having Primary Key
Know more about this OBJECTPROPERTY which is a better solution here.