--111------------------------------------------------------------------------------------------------------------------------------------
--查看数据库所申请的空间以及空间在数据和索引的分布
--1.修改SP_SPACEUSED过程--必须以SP_开头.建立在MASTER底下
--EXEC SP_DBSPACEUSED 'MASTER','FALSE'
ALTER PROCEDURE SP_DBSPACEUSED
@DBNAME SYSNAME,
@UPDATEUSAGE VARCHAR(5) = FALSE
AS
DECLARE @SQL NVARCHAR(4000)
SET @SQL=
'USE ['+@DBNAME+'];
DECLARE @IDINT
DECLARE @TYPECHARACTER(2)
DECLARE@PAGESINT
DECLARE @DBNAME SYSNAME
DECLARE @DBSIZE DEC(15,0)
DECLARE @LOGSIZE DEC(15)
DECLARE @BYTESPERPAGEDEC(15,0)
DECLARE @PAGESPERMBDEC(15,0)
DECLARE @UPDATEUSAGE_TEMP VARCHAR(5)
CREATE TABLE #SPT_SPACE
(
ROWSINT NULL,
RESERVEDDEC(15) NULL,
DATADEC(15) NULL,
INDEXPDEC(15) NULL,
UNUSEDDEC(15) NULL
)
IF '''+@UPDATEUSAGE+''' IS NOT NULL
BEGIN
SELECT @UPDATEUSAGE_TEMP=LOWER('''+@UPDATEUSAGE+''')
IF @UPDATEUSAGE_TEMP NOT IN (''TRUE'',''FALSE'')
BEGIN
RAISERROR(15143,-1,-1,@UPDATEUSAGE_TEMP)
END
END
IF @UPDATEUSAGE_TEMP = ''TRUE''
BEGIN
DBCC UPDATEUSAGE(0) WITH NO_INFOMSGS
END
SET NOCOUNT ON
BEGIN
SELECT @DBSIZE = SUM(CONVERT(DEC(15),SIZE)) FROM DBO.SYSFILES WHERE (STATUS & 64 = 0)
SELECT @LOGSIZE = SUM(CONVERT(DEC(15),SIZE)) FROM DBO.SYSFILES WHERE (STATUS & 64 <> 0)
SELECT @BYTESPERPAGE = LOW FROM MASTER.DBO.SPT_VALUES WHERE NUMBER = 1 AND TYPE = ''E''
SELECT @PAGESPERMB = 1048576 / @BYTESPERPAGE
INSERT INTO #SPT_SPACE (RESERVED) SELECT SUM(CONVERT(DEC(15),RESERVED)) FROM SYSINDEXES WHERE INDID IN (0, 1, 255)
SELECT @PAGES = SUM(CONVERT(DEC(15),DPAGES)) FROM SYSINDEXES WHERE INDID < 2
SELECT @PAGES = @PAGES + ISNULL(SUM(CONVERT(DEC(15),USED)), 0) FROM SYSINDEXES WHERE INDID = 255
UPDATE #SPT_SPACE SET DATA = @PAGES
UPDATE #SPT_SPACE SET INDEXP = (SELECT SUM(CONVERT(DEC(15),USED)) FROM SYSINDEXES WHERE INDID IN (0, 1, 255))- DATA
UPDATE #SPT_SPACE SET UNUSED = RESERVED-(SELECT SUM(CONVERT(DEC(15),USED)) FROM SYSINDEXES WHERE INDID IN (0, 1, 255))
END
SELECT
DATABASE_NAME = DB_NAME(),
DATABASE_SIZE =LTRIM(STR((@DBSIZE + @LOGSIZE) / @PAGESPERMB,15,2) + '' MB''),
''UNALLOCATED SPACE'' =LTRIM(STR((@DBSIZE -(SELECT SUM(CONVERT(DEC(15),RESERVED))FROM SYSINDEXES WHERE INDID IN (0, 1, 255))) / @PAGESPERMB,15,2)+ '' MB''),
RESERVED = LTRIM(STR(RESERVED * D.LOW / 1024.,15,0) +'' '' + ''KB''),
DATA = LTRIM(STR(DATA * D.LOW / 1024.,15,0) +'' '' + ''KB''),
INDEX_SIZE = LTRIM(STR(INDEXP * D.LOW / 1024.,15,0) +'' '' + ''KB''),
UNUSED = LTRIM(STR(UNUSED * D.LOW / 1024.,15,0) +'' '' + ''KB'') INTO ##'+@DBNAME+'_DBSPACE
FROM
#SPT_SPACE, MASTER.DBO.SPT_VALUES D
WHERE
D.NUMBER = 1 AND D.TYPE =''E''
'
EXEC (@SQL)
GO
--上面本来不想插入全局临时表.在外面库里调用插入另一个临时表时会死锁.所以才替换成全局临时.
--本来以为直接改SP_SPACEUSED 加个列就可以了.结果却发现在外面调用时DATABASESIZE是一个定值.所以得全面改造了,哈哈
--DROP TABLE #SP_DBUSED
IF OBJECT_ID('TEMPDB..[#SP_DBUSED]')IS NOT NULL
DROP TABLE #SP_DBUSED
GO
CREATE TABLE #SP_DBUSED
(
ID INT IDENTITY(1,1),
DATABASE_NAME VARCHAR(18),
DATABASE_SIZE VARCHAR(18),
UNALLOCATED_SPACE VARCHAR(18),
RESERVED VARCHAR(18),
DATA VARCHAR(18),
INDEX_SIZE VARCHAR(18),
UNUSED VARCHAR(18)
)
EXEC SP_MSFOREACHDB 'USE [?]; EXEC SP_DBSPACEUSED "?", @UPDATEUSAGE = ''TRUE'''
INSERT INTO #SP_DBUSED EXEC SP_MSFOREACHDB 'SELECT * FROM ##?_DBSPACE'
SELECT * FROM #SP_DBUSED
--222-----------------------------------------------------------------------------------------------------------------------------------
GO
--查看数据库用户表所申请的空间以及空间在数据和索引的分布
IF OBJECT_ID('TEMPDB..#SP_TBUSED') IS NOT NULL
DROP TABLE #SP_TBUSED
GO
CREATE TABLE #SP_TBUSED
(
NAME NVARCHAR(20),
ROWS CHAR(11),
RESERVED VARCHAR(18),
DATA VARCHAR(18),
INDEX_SIZE VARCHAR(18),
UNUSED VARCHAR(18)
)
INSERT INTO #SP_TBUSED (NAME,ROWS,RESERVED,DATA,INDEX_SIZE,UNUSED) EXEC SP_MSFOREACHTABLE 'SP_SPACEUSED "?",''TRUE'''
SELECT * FROM #SP_TBUSED
--333-----------------------------------------------------------------------------------------------------------------------------------
--查看所有库的总扩展分区以及所使用的扩展分区.
--建立临时表省略....
EXEC SP_MSFOREACHDB 'USE [?];DBCC SHOWFILESTATS'
----------------------------------------------------------------------------------------------------------------------------------------
--查看所有库的日志空间,可以与第一个数据空间通过名字关联.合成一个表.
--建立临时表省略....
DBCC SQLPERF ( LOGSPACE )
--444-----------------------------------------------------------------------------------------------------------------------------------
--查看数据库中所有的表和表索引的页面数,索引的分区数以及索引的碎片信息等.
--具体参考DBCC SHOWCONTIG 相关的帮助--MSDN
--SP_MSFOREACHDB 'USE [?];EXEC SP_MSFOREACHTABLE ''DBCC SHOWCONTIG([?]) WITH TABLERESULTS'''
--因SP_MSFOREACHTABLE过程中游标是局部游标.所以不行.
--建立临时表省略
--EXEC PRINT_DBCCSHOWCONTIG NULL
ALTER PROC PRINT_DBCCSHOWCONTIG(@DBNAME SYSNAME)
AS
IF @DBNAME IS NULL
BEGIN
DECLARE CUR_DBNAME CURSOR
FOR
SELECT NAME FROM MASTER..SYSDATABASES
DECLARE @DB SYSNAME,@SQL NVARCHAR(4000)
OPEN CUR_DBNAME
FETCH NEXT FROM CUR_DBNAME INTO @DB
WHILE @@FETCH_STATUS=0
BEGIN
SET @SQL='USE ['+@DB+'];EXEC SP_MSFOREACHTABLE ''DBCC SHOWCONTIG([?]) WITH TABLERESULTS,ALL_INDEXES'''
--PRINT @SQL
EXEC(@SQL)
SET @SQL=''
FETCH NEXT FROM CUR_DBNAME INTO @DB
END
CLOSE CUR_DBNAME
DEALLOCATE CUR_DBNAME
END
ELSE
BEGIN
SET @SQL='USE ['+@DBNAME+'];EXEC SP_MSFOREACHTABLE ''DBCC SHOWCONTIG([?]) WITH TABLERESULTS,ALL_INDEXES'''
--PRINT @SQL
EXEC(@SQL)
SET @SQL=''
END
---555---------------------------------------------------------------------------------------------------------------
--SQL2K5以上可利用动态管理视图SYS.DM_DB_PARTITION_STATS进行统计,方便啊(参考MSDN)
--写成过程就可以整服务器调用.
USE <数据库>
GO
SELECT
O.NAME ,
SUM (P.RESERVED_PAGE_COUNT) AS RESERVED_PAGE_COUNT,
SUM (P.USED_PAGE_COUNT) AS USED_PAGE_COUNT,
SUM ( CASE WHEN (P.INDEX_ID < 2) THEN (P.IN_ROW_DATA_PAGE_COUNT + P.LOB_USED_PAGE_COUNT + P.ROW_OVERFLOW_USED_PAGE_COUNT)
ELSE P.LOB_USED_PAGE_COUNT + P.ROW_OVERFLOW_USED_PAGE_COUNT END ) AS DATAPAGES,
SUM ( CASE WHEN (P.INDEX_ID < 2) THEN ROW_COUNT ELSE 0 END ) AS ROWCOUNTS
FROM
SYS.DM_DB_PARTITION_STATS P
INNER JOIN SYS.OBJECTS O ON P.OBJECT_ID = O.OBJECT_ID
GROUP
BY O.NAME
GO
-----------
--以上是各种查看数据库空间.表空间.索引空间.日志空间,索引碎片等信息的一些方法.
--欢迎各位大虾指正
--查看碎片可利用DBCC SHOWCONTIG 但有性能上的一些影响.
--如果用SP_SPACEUSED第二个参数不为TRUE的话可能数据并不准确.
--需要看哪种信息再选择适应的方法.
