0

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.

Thanks

0
5

Try this:

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.

3
  • instead of TABLE_TYPE = 'BASE TABLE' you can also use TABLE_TYPE != 'VIEW'
    – vijayp
    Sep 16 '14 at 13:43
  • 2
    This missed 151 tables in my database without a primary key...If you have NULL values in Information_schema.table_constraints.table_name then you'll end up with no results (which is incorrect). Sep 16 '14 at 13:59
  • @MarkSinkinson, yes you are right, I have verified it, looking at the OP question, I gave that answer, I have edited my answer, Thanks :)
    – vijayp
    Sep 16 '14 at 14:31

Not the answer you're looking for? Browse other questions tagged or ask your own question.