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
函數:求數據表所占容量
最新推荐文章于 2024-08-18 00:35:02 发布