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
