在使用数据库的时候,我们有时需要知道数据库占用磁盘空间的情况,以前数据库中每个表的磁盘空间使用情况,下面是获取这些信息的方法:
1 数据库的磁盘空间使用信息
2 表的磁盘空间使用信息
3 获取数据库所有表的磁盘空间使用信息
1 数据库的磁盘空间使用信息
sp_spaceused
2 表的磁盘空间使用信息
sp_spaceused
'
表的名称
'
3 获取数据库所有表的磁盘空间使用信息
CREATE
PROC
spaceused_simulator
@database_name varchar ( 128 )
AS
DECLARE @cmd varchar ( 1000 ),
@bytes int
SET NOCOUNT ON
SELECT @bytes = [ low ] / 1024 FROM master..spt_values
WHERE number = 1
AND type = ' E '
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 )
SET @cmd =
' INSERT INTO #tmp_spaceused (id, tablename)
SELECT id, name FROM ' + @database_name + ' ..sysobjects
WHERE xtype = '' U ''
AND name <> '' dtproperties '''
EXEC ( @cmd )
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) '
EXEC ( @cmd )
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 '
EXEC ( @cmd )
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 '
EXEC ( @cmd )
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 '
EXEC ( @cmd )
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 '
EXEC ( @cmd )
UPDATE #tmp_spaceused
SET
reserved = reserved * @bytes ,
data = data * @bytes ,
index_size = index_size * @bytes ,
unused = unused * @bytes
SELECT * FROM #tmp_spaceused ORDER BY tablename
GO
@database_name varchar ( 128 )
AS
DECLARE @cmd varchar ( 1000 ),
@bytes int
SET NOCOUNT ON
SELECT @bytes = [ low ] / 1024 FROM master..spt_values
WHERE number = 1
AND type = ' E '
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 )
SET @cmd =
' INSERT INTO #tmp_spaceused (id, tablename)
SELECT id, name FROM ' + @database_name + ' ..sysobjects
WHERE xtype = '' U ''
AND name <> '' dtproperties '''
EXEC ( @cmd )
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) '
EXEC ( @cmd )
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 '
EXEC ( @cmd )
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 '
EXEC ( @cmd )
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 '
EXEC ( @cmd )
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 '
EXEC ( @cmd )
UPDATE #tmp_spaceused
SET
reserved = reserved * @bytes ,
data = data * @bytes ,
index_size = index_size * @bytes ,
unused = unused * @bytes
SELECT * FROM #tmp_spaceused ORDER BY tablename
GO