sql 2008中没有索引的表列表 [英] list of tables without indexes in sql 2008

问题描述

如何列出 SQL 2008 数据库中没有索引的表?

How do I list tables without indexes in my SQL 2008 database?

编辑
我想要架构名称和表名称.

Edit
I want the Schema name and the Table name.

推荐答案

这应该涵盖您正在寻找的内容.即堆表(没有聚集索引)并且没有任何非聚集索引.它使用新的系统.2005/2008 年使用的表对象.

This should cover what your looking for. i.e. tables that are heaps (no clustered index) and do not have any non-clustered indexes. It uses the new sys. table objects used in 2005/2008.

此外，您可能想要查找具有聚集索引但没有非聚集索引的表(这是我已注释掉的语句的第二部分.

in addition, you probably want to look for tables that do have a clustered index, but have no nonclustered indexes (this is the 2nd part of the statement which I've left commented out.

SELECT 
     schemaname = OBJECT_SCHEMA_NAME(o.object_id)
    ,tablename = o.NAME
FROM sys.objects o
INNER JOIN sys.indexes i ON i.OBJECT_ID = o.OBJECT_ID
-- tables that are heaps without any nonclustered indexes
WHERE (
        o.type = 'U'
        AND o.OBJECT_ID NOT IN (
            SELECT OBJECT_ID
            FROM sys.indexes
            WHERE index_id > 0
            )
        )
        --    OR
        -- table that have a clustered index without any nonclustered indexes
        --(o.type='U' 
        --        AND o.OBJECT_ID NOT IN (
        --    SELECT OBJECT_ID 
        --        FROM sys.indexes 
        --        WHERE index_id>1))  

这篇关于sql 2008中没有索引的表列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！