A script to check for primary keys

Question

Trying to figure out an SQL script to check for primary keys in cerain tables. If the table does not have a primary key, then the script should print the name of the table.

Tables to test: TableA TableB TableC 

After running the script (and lets say that TableA and TableC have PK, but not TableB), the output will be lower:

 NoKeys TableB 

Answer 1

 ;WITH tables_with_pk AS ( SELECT t.table_schema, t.table_name FROM INFORMATION_SCHEMA.TABLES t INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc ON t.TABLE_NAME = tc.TABLE_NAME AND t.table_schema = tc.table_schema WHERE tc.constraint_type = 'PRIMARY KEY' ) SELECT t.table_schema, t.table_name FROM INFORMATION_SCHEMA.TABLES t EXCEPT SELECT table_schema, table_name FROM tables_with_pk 

Answer 2

I don't have the exact / complete code for you, but here is the idea:

You will need to view the list of tables in the database:

 SELECT * FROM information_schema.tables` 

The code to check for the primary key for your table will look like this:

 SELECT * FROM information_schema.table_constraints WHERE constraint_type = 'PRIMARY KEY' AND table_name = @Your_Table_Name` 

Answer 3

What about

 USE information_schema; SELECT `TABLE_NAME` FROM `TABLES` LEFT JOIN `TABLE_CONSTRAINTS` USING(`TABLE_SCHEMA`, `TABLE_NAME`) WHERE `TABLE_SCHEMA` = '__PUT_YOUR_DB_NAME_HERE__' AND `CONSTRAINT_NAME` LIKE '%PRIMARY%' AND ISNULL(`CONSTRAINT_CATALOG`) 

Answer 4

I liked Sean's answer and it worked for me. You can add an offer.

WHERE t.TABLE_TYPE = 'BASE TABLE'

before the EXCEPT statement if you do not want to receive views as well.

 ;WITH tables_with_pk AS ( SELECT t.table_schema, t.table_name FROM INFORMATION_SCHEMA.TABLES t INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc ON t.TABLE_NAME = tc.TABLE_NAME AND t.table_schema = tc.table_schema WHERE tc.constraint_type = 'PRIMARY KEY' ) SELECT t.table_schema, t.table_name FROM INFORMATION_SCHEMA.TABLES t WHERE t.TABLE_TYPE = 'BASE TABLE' EXCEPT SELECT table_schema, table_name FROM tables_with_pk