函數：求數據表所占容量

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FUN_TABLES_SPACEINFO]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[FUN_TABLES_SPACEINFO]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO


CREATE FUNCTION FUN_TABLES_SPACEINFO()
RETURNS  @spt_space TABLE
 (
  id   INT ,
  name  NVARCHAR(255),
  rows  INT  null,
  reserved DECIMAL(15) null,
  used  DECIMAL(15) null,
  data  DECIMAL(15) null,
  indexp DECIMAL(15) null,
  unused DECIMAL(15) NULL,
  unit  VARCHAR(10)
 )
AS
BEGIN

 --插入reserved,used,unused
 INSERT INTO @spt_space(id,name,reserved,used,unused,unit)
 SELECT a.id,b.name, reserved=SUM(reserved),used=SUM(used),unused=SUM(ISNULL(reserved,0)-ISNULL(used,0)),'KB'
 FROM sysindexes a JOIN sysobjects b ON a.id=b.id
 WHERE indid IN (0, 1, 255) AND  b.xtype='U'
 GROUP BY a.id ,b.name
 
 
 --更新data
 UPDATE a SET a.data=b.Pages
 FROM @spt_space a JOIN
  (SELECT id, Pages=SUM(dpages)
  FROM sysindexes WHERE indid<2
  GROUP BY id) b ON a.id=b.id
 
 --更新data 
 UPDATE a SET a.data=ISNULL(a.data,0)+ISNULL(b.Pages,0)
 FROM @spt_space a JOIN
  (SELECT id, Pages=SUM(used)
  FROM sysindexes WHERE indid=255
  GROUP BY id) b ON a.id=b.id
 
 --更新rows
 UPDATE a SET a.rows=b.rows
 FROM @spt_space a JOIN sysindexes b ON a.id=b.id
 WHERE b.indid<2

 --更新indexp
 UPDATE @spt_space SET indexp=ISNULL(used,0)-ISNULL(data,0)

 UPDATE a SET
  a.reserved=ISNULL(reserved,0)*b.LOW/1024.,
  a.used=ISNULL(used,0)*b.LOW/1024.,
  a.unused=ISNULL(unused,0)*b.LOW/1024.,
  a.data=ISNULL(data,0)*b.LOW/1024.,
  a.indexp=ISNULL(indexp,0)*b.LOW/1024.
 FROM @spt_space a,master.dbo.spt_values b
 WHERE b.number = 1 AND b.type = 'E'
   
 RETURN
END


/*
--調用舉例
 SELECT * FROM dbo.FUN_TABLES_SPACEINFO()
*/


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO