We got 500+ tables and want to identify which tables doesn't have primary keys. Because creating index on large table will help to improve performance.
我们有500多个表,想要确定哪些表没有主键。因为在大表上创建索引将有助于提高性能。
Required Command - to identify table which are HEAPS (as they dont have clustered index)
必需命令 - 识别HEAPS表(因为它们没有聚簇索引)
Regards
1 个解决方案
#1
4
SELECT OBJECT_NAME(object_id)
FROM sys.indexes
WHERE index_id=0
AND OBJECTPROPERTY(object_id, 'IsUserTable') = 1
Finds all heaps. This issue is orthogonal to whether or not a PK exists though. A heap can have a non clustered PK and a clustered index isn't necessarily the PK. To find tables without a PK you can use.
找到所有堆。这个问题与PK是否存在正交。堆可以具有非群集PK,聚簇索引不一定是PK。要查找没有PK的表,您可以使用。
SELECT *
FROM sys.tables t
WHERE NOT EXISTS
(
SELECT *
FROM sys.indexes i
WHERE is_primary_key=1 AND i.object_id = t.object_id
)
#1
4
SELECT OBJECT_NAME(object_id)
FROM sys.indexes
WHERE index_id=0
AND OBJECTPROPERTY(object_id, 'IsUserTable') = 1
Finds all heaps. This issue is orthogonal to whether or not a PK exists though. A heap can have a non clustered PK and a clustered index isn't necessarily the PK. To find tables without a PK you can use.
找到所有堆。这个问题与PK是否存在正交。堆可以具有非群集PK,聚簇索引不一定是PK。要查找没有PK的表,您可以使用。
SELECT *
FROM sys.tables t
WHERE NOT EXISTS
(
SELECT *
FROM sys.indexes i
WHERE is_primary_key=1 AND i.object_id = t.object_id
)