/*-- ===================================================================

版本: SQL Server 2000, 2005

功能: 查询数据库中表空间使用状况

=================================================================== --*/

USE [要查询空间信息的库名]

GO

SELECT

    ServerName = CONVERT(sysname, SERVERPROPERTY('ServerName')),

    DatabaseID = DB_ID(),

    DatabaseName = DB_NAME(),

    ObjectID = DATA.id,

    Owner = USER_NAME(DATA.uid),

    ObjectName = DATA.Name,

    Reserved = CONVERT(decimal(15, 2), DATA.Reserved / SPER.PageSperMB),

    DataSize = CONVERT(decimal(15, 2), (DATA.DataSize + DATA.[TextSize]) / SPER.PageSperMB),

    IndexSize = CONVERT(decimal(15, 2), (DATA.IndexSize - DATA.DataSize - DATA.[TextSize]) / SPER.PageSperMB),

    UnUsedSize = CONVERT(decimal(15, 2), (DATA.Reserved - DATA.IndexSize) / SPER.PageSperMB),

    DATA.Rows

FROM(

    SELECT

        O.id, O.uid, O.name, O.xtype,

        Reserved = ISNULL(SUM(CASE WHEN IX.indid IN (0, 1, 255) THEN CONVERT(dec(15), IX.reserved) ELSE 0 END), 0),

        DataSize = ISNULL(SUM(CASE WHEN IX.indid < 2 THEN CONVERT(dec(15), IX.dpages) ELSE 0 END), 0),

        [TextSize] = ISNULL(SUM(CASE WHEN IX.indid = 255 THEN CONVERT(dec(15), IX.used) ELSE 0 END), 0),

        IndexSize = ISNULL(SUM(CASE WHEN IX.indid IN (0, 1, 255) THEN CONVERT(dec(15), IX.used) ELSE 0 END), 0),

        Rows = ISNULL(MAX(CASE WHEN IX.indid < 2 THEN IX.rows ELSE 0 END), 0)

    FROM dbo.sysindexes IX WITH(NOLOCK)

        INNER JOIN dbo.sysobjects O WITH(NOLOCK)

            ON IX.id = O.id

    WHERE O.xtype IN ( 'U')--, 'S')

        AND O.status > =0

        AND (IX.indid < 2 OR IX.indid = 255)

    GROUP BY O.id, O.uid, O.name, O.xtype

)DATA

    CROSS JOIN(

        SELECT PageSperMB = 1048576. / low, low

        FROM master.dbo.spt_values

        WHERE number = 1

                AND type = 'E'

    )SPER

ORDER BY ObjectName, Owner

GO 

/*-- ===================================================================

版本: SQL Server 2000, 2005

功能: 查询数据库空间使用状况

=================================================================== --*/

USE [要查询空间信息的库名]

GO

SELECT

    ServerName = CONVERT(sysname, SERVERPROPERTY('ServerName')),

    DatabaseID = DB_ID(),

    DatabaseName = DB_NAME(),

    DatabaseSize = CONVERT(decimal(15, 2), DB.DbSize / SPER.PageSperMB),

    DataFileSize = CONVERT(decimal(15, 2), DB.DataFileSize / SPER.PageSperMB),

    LogFileSize = CONVERT(decimal(15, 2), DB.LogFileSize / SPER.PageSperMB),

    UnAllocated = CONVERT(decimal(15, 2), (DB.DataFileSize - DATA.Reserved) / SPER.PageSperMB),

    DataReserved = CONVERT(decimal(15, 2), DATA.Reserved / SPER.PageSperMB),

    DataSize = CONVERT(decimal(15, 2), (DATA.DataSize + DATA.[TextSize]) / SPER.PageSperMB),

    IndexSize = CONVERT(decimal(15, 2), (DATA.IndexSize - DATA.DataSize - DATA.[TextSize]) / SPER.PageSperMB),

    UnUsedSize = CONVERT(decimal(15, 2), (DATA.Reserved - DATA.IndexSize) / SPER.PageSperMB),

    CountProcedure = OBJS.ProcudureS,

    CountFunction = OBJS.FunctionS,

    CountTrigger = OBJS.TriggerS,

    CountUserTable = OBJS.TableS,

    CountView = OBJS.ViewS,

    RecoveryMode = CONVERT(varchar(12), DATABASEPROPERTYEX(DB_NAME(), N'Recovery')),

    PrimaryDrive = CONVERT(char(1), (

            SELECT TOP 1 UPPER(LEFT(FileName, 1))

            FROM master.dbo.sysdatabases WITH(NOLOCK)

            WHERE dbid = DB_ID()))

FROM(

    SELECT

        DbSize = ISNULL(SUM(CONVERT(dec(15), size)), 0),

        DataFileSize = ISNULL(SUM(CASE WHEN status & 0x40 =0 THEN CONVERT(dec(15), size) ELSE 0 END), 0),

        LogFileSize = ISNULL(SUM(CASE WHEN status & 0x40 =0 THEN 0 ELSE CONVERT(dec(15), size) END), 0)

    FROM dbo.sysfiles WITH (NOLOCK)

)DB

    CROSS JOIN(

        SELECT

            Reserved = ISNULL(SUM(CASE WHEN indid IN (0, 1, 255) THEN CONVERT(dec(15), reserved) ELSE 0 END), 0),

            DataSize = ISNULL(SUM(CASE WHEN indid < 2 THEN CONVERT(dec(15), dpages) ELSE 0 END), 0),

            [TextSize] = ISNULL(SUM(CASE WHEN indid = 255 THEN CONVERT(dec(15), used) ELSE 0 END), 0),

            IndexSize = ISNULL(SUM(CASE WHEN indid IN (0, 1, 255) THEN CONVERT(dec(15), used) ELSE 0 END), 0)

        FROM dbo.sysindexes WITH(NOLOCK)

        WHERE (indid < 2 OR indid = 255)

    )DATA

    CROSS JOIN(

        SELECT PageSperMB = 1048576. / low

        FROM master.dbo.spt_values

        WHERE number = 1

                AND type = 'E'

    )SPER

    CROSS JOIN(

        SELECT

            TableS = SUM(CASE xtype WHEN 'U' THEN 1 ELSE 0 END),

            ProcudureS = SUM(CASE xtype WHEN 'P' THEN 1 ELSE 0 END),

            ViewS = SUM(CASE xtype WHEN 'V' THEN 1 ELSE 0 END),

            TriggerS = SUM(CASE xtype WHEN 'TR' THEN 1 ELSE 0 END),

            FunctionS = SUM(CASE WHEN xtype IN('FN', 'IF', 'TF') THEN 1 ELSE 0 END)

        FROM dbo.sysobjects

    )OBJS

GO