代码改变世界

数据库信息 (表名 行数 堆 集群 非聚集)的查询

2010-10-07 14:07  曾祥展  阅读(836)  评论(0编辑  收藏

/************************************************************

 * Time: 2010-10-07 13:23:32

 * 找出有索引的表信息(表名行数堆集群非聚集)

 ************************************************************/

 

WITH cte AS

(

    SELECT table_name = o.name,

           o.[object_id],

           i.index_id,

           i.type,

           i.type_desc

    FROM   sys.indexes i

           INNER JOIN sys.objects o

                ON  i.[object_id] = o.[object_id]

    WHERE  o.type IN ('U')

           AND o.is_ms_shipped = 0

           AND i.is_disabled = 0

           AND i.is_hypothetical = 0

           AND i.type <= 2

), cte2 AS

(

    SELECT *

    FROM   cte c

           PIVOT(

               COUNT(TYPE) FOR type_desc IN ([HEAP], [CLUSTERED], [NONCLUSTERED])

           ) pv

)

SELECT c2.table_name,

       [rows] = MAX(p.rows),

       is_heap = SUM([HEAP]),

       is_clustered = SUM([CLUSTERED]),

       num_of_nonclustered = SUM([NONCLUSTERED])

FROM   cte2 c2

       INNER JOIN sys.partitions p

            ON  c2.[object_id] = p.[object_id]

            AND c2.index_id = p.index_id

GROUP BY

       table_name

 

 

 

--没有索引的表

SELECT NAME

FROM   sys.tables

WHERE  OBJECTPROPERTY(OBJECT_ID, 'TableHasIndex') = 0

查询数据库表信息 (表名 行数 堆 集群 非聚集)

 

问题

            'pivot' 附近有语法错误您可能需要将当前数据库的兼容级别设置为更高的值以启用此功能

解决

             EXEC sp_dbcmptlevel stat ,90          --stat 为数据库名称