Search

Saturday, January 21, 2012

List all Tables in Database With / Without Primary Key

USE <DatabaseName>;
GO
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

SELECT i.name AS Index_Name,OBJECT_NAME(ic.OBJECT_ID) AS Table_Name,COL_NAME(ic.OBJECT_ID,ic.column_id) AS Column_Name
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic
ON i.OBJECT_ID = ic.OBJECT_ID
AND i.index_id = ic.index_id
WHERE i.is_primary_key = 1

No comments:

Post a Comment