Find all the Tables who having a Primary Key

We have a database in that some of the tables have primary key. With the help of below we will get the list of tables.

SELECT T.name as 'Table with Primary Key',s.name as [Primary Key Name] FROM SYS.Tables T Inner Join sysobjects s ON s.parent_obj=t.object_id WHERE OBJECTPROPERTY(object_id,'TableHasPrimaryKey') = 1 AND t.type = 'U' and s.xtype='PK'

See the output

 

In my database [test] having only one table which has Primary key.

  

With the help of below script those do not have primary key

SELECT T.name as 'Table without Primary Key' FROM SYS.Tables T WHERE OBJECTPROPERTY(object_id,'TableHasPrimaryKey') = 0 AND t.type = 'U'

 See the output

 

Use below sql script to find the primary key column name

SELECT OBJECT_NAME(ic.OBJECT_ID) AS [Table Name], COL_NAME(ic.OBJECT_ID,ic.column_id) AS [Column Name],  i.name AS [Primary key 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

See the output