获取SQL Server 2000数据库和表空间使用信息
参考源:http://sqlforums.windowsitpro.com/web/forum/messageview.aspx?catid=74&threadid=47900&enterthread=y
在使用数据库的时候,我们有时需要知道数据库占用磁盘空间的情况,以前数据库中每个表的磁盘空间使用情况,下面是获取这些信息的方法:
1 数据库的磁盘空间使用信息
sp_spaceused
2 表的磁盘空间使用信息
sp_spaceused '表的名称'
3 获取数据库所有表的磁盘空间使用信息
CREATE PROC spaceused_simulator
@database_name varchar(128)
AS
DECLARE @cmd varchar(1000),
@bytes int
![](/Images/OutliningIndicators/None.gif)
SET NOCOUNT ON
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
SELECT @bytes = [low]/1024 FROM master..spt_values
WHERE number = 1
AND type = 'E'
![](/Images/OutliningIndicators/None.gif)
CREATE TABLE #tmp_spaceused (
id int NULL,
tablename varchar(128) NULL,
rows int NULL,
reserved int NULL,
data int NULL,
index_size int NULL,
unused int NULL)
![](/Images/OutliningIndicators/None.gif)
SET @cmd =
'INSERT INTO #tmp_spaceused (id, tablename)
SELECT id, name FROM ' + @database_name + '..sysobjects
WHERE xtype = ''U''
AND name <> ''dtproperties'''
![](/Images/OutliningIndicators/None.gif)
EXEC (@cmd)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
SET @cmd =
'UPDATE #tmp_spaceused
SET rows = A.rows
FROM ' + @database_name + '..sysindexes A
WHERE #tmp_spaceused.id = A.id
AND A.indid IN (0, 1)'
![](/Images/OutliningIndicators/None.gif)
EXEC (@cmd)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
SET @cmd =
'UPDATE #tmp_spaceused
SET reserved = A.SumReserved
FROM (SELECT id, SUM(reserved) AS SumReserved
FROM ' + @database_name + '..sysindexes
WHERE indid IN (0, 1, 255)
GROUP BY id) AS A
JOIN #tmp_spaceused ON A.id = #tmp_spaceused.id'
![](/Images/OutliningIndicators/None.gif)
EXEC (@cmd)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
SET @cmd =
'UPDATE #tmp_spaceused
SET data = C.data
FROM (SELECT A.id, A.SumDpages + ISNULL(B.SumUsed, 0) AS data
FROM (SELECT id, SUM(dpages) AS SumDpages
FROM ' + @database_name + '..sysindexes
WHERE indid IN (0, 1)
GROUP BY id) AS A
LEFT JOIN
(SELECT id, ISNULL(SUM(used), 0) AS SumUsed
FROM ' + @database_name + '..sysindexes
WHERE indid = 255
GROUP BY id) AS B
ON A.id = B.id) AS C
JOIN #tmp_spaceused ON C.id = #tmp_spaceused.id'
![](/Images/OutliningIndicators/None.gif)
EXEC (@cmd)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
SET @cmd =
'UPDATE #tmp_spaceused
SET index_size = A.SumUsed - #tmp_spaceused.data
FROM (SELECT id, SUM(used) AS SumUsed
FROM ' + @database_name + '..sysindexes
WHERE indid IN (0, 1, 255)
GROUP BY id) AS A
JOIN #tmp_spaceused ON A.id = #tmp_spaceused.id'
![](/Images/OutliningIndicators/None.gif)
EXEC (@cmd)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
SET @cmd =
'UPDATE #tmp_spaceused
SET unused = #tmp_spaceused.reserved - A.SumUsed
FROM (SELECT id, SUM(used) AS SumUsed
FROM ' + @database_name + '..sysindexes
WHERE indid IN (0, 1, 255)
GROUP BY id) AS A
JOIN #tmp_spaceused ON A.id = #tmp_spaceused.id'
![](/Images/OutliningIndicators/None.gif)
EXEC (@cmd)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
UPDATE #tmp_spaceused
SET
reserved = reserved * @bytes,
data = data * @bytes,
index_size = index_size * @bytes,
unused = unused * @bytes
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
SELECT * FROM #tmp_spaceused ORDER BY tablename
![](/Images/OutliningIndicators/None.gif)
GO
在使用数据库的时候,我们有时需要知道数据库占用磁盘空间的情况,以前数据库中每个表的磁盘空间使用情况,下面是获取这些信息的方法:
1 数据库的磁盘空间使用信息
![](/Images/OutliningIndicators/None.gif)
2 表的磁盘空间使用信息
![](/Images/OutliningIndicators/None.gif)
3 获取数据库所有表的磁盘空间使用信息
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)