-- =============================================
-- Author:
-- Create date:
-- Description:
--Thanks to ,, RBarry Young
-- =============================================
CREATE PROCEDURE [dbo].[spTableInformation]
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    SELECT
        SCHEMA_NAME ( SYSTBL.SCHEMA_ID ) AS [SCHEMA] ,
        SYSTBL.NAME ,
        coalesce
        (
            (
                SELECT NAME FROM sys.database_principals AS SDBP
                WHERE ( PRINCIPAL_ID = SYSTBL.PRINCIPAL_ID )
            ) , SCHEMA_NAME ( SYSTBL.SCHEMA_ID )
        )
        AS OWNER ,
        SYSTBL.MAX_COLUMN_ID_USED AS COLUMNS ,
        cast
        (
            CASE SINDX_1.INDEX_ID
                WHEN 1 THEN 1
                ELSE 0
            END
            AS bit
        )
        AS HASCLUSIDX ,
        coalesce
        (
            (
                SELECT sum ( rows ) FROM sys.partitions AS SPART
                WHERE ( object_id = SYSTBL.OBJECT_ID )
                    AND
                    ( INDEX_ID < 2 )
            ) , 0
        )
        AS [ROWCOUNT] ,
        coalesce
        (
            (
                SELECT cast ( SPTV.low / 1024.0 AS float ) * sum
                (
                    SAU_1.USED_PAGES -
                    CASE
                        WHEN SAU_1.TYPE <> 1 THEN SAU_1.USED_PAGES
                        WHEN SYSP.INDEX_ID < 2 THEN SAU_1.DATA_PAGES
                        ELSE 0
                    END
                )
                FROM sys.indexes AS SINDX_2
                    INNER JOIN sys.partitions AS SYSP ON SYSP.OBJECT_ID = SINDX_2.OBJECT_ID AND SYSP.INDEX_ID = SINDX_2.INDEX_ID
                    INNER JOIN sys.allocation_units AS SAU_1 ON SAU_1.CONTAINER_ID = SYSP.PARTITION_ID
                WHERE ( SINDX_2.OBJECT_ID = SYSTBL.OBJECT_ID )
            ) , 0.0
        )
        AS INDEXKB ,
        coalesce
        (
            (
                SELECT cast ( SPTV.low / 1024.0 AS float ) * sum
                (
                    CASE
                        WHEN SAU_2.TYPE <> 1 THEN SAU_2.USED_PAGES
                        WHEN SYSP.INDEX_ID < 2 THEN SAU_2.DATA_PAGES
                        ELSE 0
                    END
                )
                AS Expr1 FROM sys.indexes AS SINDX_2
                    INNER JOIN sys.partitions AS SYSP ON SYSP.OBJECT_ID = SINDX_2.OBJECT_ID AND SYSP.INDEX_ID = SINDX_2.INDEX_ID
                    INNER JOIN sys.allocation_units AS SAU_2 ON SAU_2.CONTAINER_ID = SYSP.PARTITION_ID
                WHERE ( SINDX_2.OBJECT_ID = SYSTBL.OBJECT_ID )
            ) , 0.0
        )
        AS DATAKB ,
        SYSTBL.CREATE_DATE ,
        SYSTBL.MODIFY_DATE
    FROM sys.tables AS SYSTBL
        INNER JOIN sys.indexes AS SINDX_1 ON SINDX_1.OBJECT_ID = SYSTBL.OBJECT_ID AND SINDX_1.INDEX_ID < 2
        INNER JOIN master.dbo.spt_values AS SPTV ON SPTV.NUMBER = 1 AND SPTV.type = 'E'

    END

    GO

ITPub博客

获取数据库表的信息(大小,索引大小,创建时间,行数)

最新文章