How do I list all tables WITHOUT a primary key?

Run the following SQL code:

SELECT T.TABLE_NAME AS “Tables without PKs”
FROM INFORMATION_SCHEMA.TABLES AS T
WHERE NOT EXISTS
(SELECT *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
WHERE CONSTRAINT_TYPE = ‘PRIMARY KEY’
AND T.TABLE_NAME = TC.TABLE_NAME)
AND T.TABLE_TYPE = ‘BASE TABLE’

How do I list all Primary Keys in a SQL Server 2000/2005 database?

Select the database you wish to list primary keys from and run the following SQL query:

SELECT
kcu.TABLE_SCHEMA AS ‘Schema’,
kcu.TABLE_NAME AS ‘Table’,
kcu.CONSTRAINT_NAME AS ‘Constraint Name’,
kcu.COLUMN_NAME AS ‘Column’
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tc
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE as kcu
ON kcu.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA
AND kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
AND kcu.TABLE_SCHEMA = tc.TABLE_SCHEMA
AND kcu.TABLE_NAME = tc.TABLE_NAME
WHERE
tc.CONSTRAINT_TYPE = ‘PRIMARY KEY’
ORDER BY
kcu.TABLE_SCHEMA, kcu.TABLE_NAME, kcu.CONSTRAINT_NAME

After running this script you should get a table listing the Schema, Table, Constraint Name and the Column for each Primary Key specified.

The screenshot below shows what results you will get if you run it on the AdventureWorks database: