How to Find Number of Primarykey and Foreignkey columns in Tables

-- No Foreign key Tables

SELECT * FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME NOT IN

(

      SELECT OBJECT_NAME(f.parent_object_id) AS TableName

      FROM sys.foreign_keys AS f

      INNER JOIN sys.foreign_key_columns AS fc

      ON f.OBJECT_ID = fc.constraint_object_id

)

AND TABLE_TYPE = 'BASE TABLE'

ORDER BY TABLE_NAME;

--select * from sys.foreign_keys

--select * from sys.foreign_key_columns

select * from sys.objects

-- No primary key Tables

SELECT SCHEMA_NAME(schema_id) AS SchemaName,name AS TableName

FROM sys.tables

WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasPrimaryKey') = 0

ORDER BY SchemaName, TableName;

SELECT SCHEMA_NAME(schema_id) AS SchemaName,name AS TableName

FROM sys.tables

WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasForeignRef') = 0

ORDER BY SchemaName, TableName;

Kindly Bookmark and Share it: