--111--------------------------------------------------------------------------------------------------------------------------------------查看数据库所申请的空间以及空间在数据和索引的分布--1.修改SP_SPACEUSED过程--必须以SP_开头.建立在MASTER底下--EXEC SP_DBSPACEUSED 'MASTER','FALSE'ALTER PROCEDURE SP_DBSPACEUSED@DBNAME SYSNAME,@UPDATEUSAGE VARCHAR(5) = FALSEASDECLARE @SQL NVARCHAR(4000)SET @SQL='USE ['+@DBNAME+'];DECLARE @IDINTDECLARE @TYPECHARACTER(2)DECLARE@PAGESINTDECLARE @DBNAME SYSNAMEDECLARE @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 NULLBEGINSELECT @UPDATEUSAGE_TEMP=LOWER('''+@UPDATEUSAGE+''')IF @UPDATEUSAGE_TEMP NOT IN (''TRUE'',''FALSE'')BEGINRAISERROR(15143,-1,-1,@UPDATEUSAGE_TEMP)ENDENDIF @UPDATEUSAGE_TEMP = ''TRUE''BEGINDBCC UPDATEUSAGE(0) WITH NO_INFOMSGSENDSET NOCOUNT ONBEGINSELECT @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 / @BYTESPERPAGEINSERT 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 < 2SELECT @PAGES = @PAGES + ISNULL(SUM(CONVERT(DEC(15),USED)), 0) FROM SYSINDEXES WHERE INDID = 255UPDATE #SPT_SPACE SET DATA = @PAGESUPDATE #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))ENDSELECT 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+'_DBSPACEFROM #SPT_SPACE, MASTER.DBO.SPT_VALUES DWHERE D.NUMBER = 1 AND D.TYPE =''E'''EXEC (@SQL)GO--上面本来不想插入全局临时表.在外面库里调用插入另一个临时表时会死锁.所以才替换成全局临时.--本来以为直接改SP_SPACEUSED 加个列就可以了.结果却发现在外面调用时DATABASESIZE是一个定值.所以得全面改造了,哈哈--DROP TABLE #SP_DBUSEDIF OBJECT_ID('TEMPDB..[#SP_DBUSED]')IS NOT NULLDROP TABLE #SP_DBUSEDGOCREATE 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 NULLDROP TABLE #SP_TBUSEDGOCREATE 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 NULLALTER PROC PRINT_DBCCSHOWCONTIG(@DBNAME SYSNAME)ASIF @DBNAME IS NULLBEGINDECLARE CUR_DBNAME CURSORFORSELECT NAME FROM MASTER..SYSDATABASESDECLARE @DB SYSNAME,@SQL NVARCHAR(4000)OPEN CUR_DBNAMEFETCH NEXT FROM CUR_DBNAME INTO @DBWHILE @@FETCH_STATUS=0BEGIN 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 @DBENDCLOSE CUR_DBNAMEDEALLOCATE CUR_DBNAMEENDELSEBEGINSET @SQL='USE ['+@DBNAME+'];EXEC SP_MSFOREACHTABLE ''DBCC SHOWCONTIG([?]) WITH TABLERESULTS,ALL_INDEXES'''--PRINT @SQLEXEC(@SQL)SET @SQL=''END---555-----------------------------------------------------------------------------------------------------------------SQL2K5以上可利用动态管理视图SYS.DM_DB_PARTITION_STATS进行统计,方便啊(参考MSDN)--写成过程就可以整服务器调用.USE <数据库>GOSELECT 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 ROWCOUNTSFROM SYS.DM_DB_PARTITION_STATS P INNER JOIN SYS.OBJECTS O ON P.OBJECT_ID = O.OBJECT_IDGROUP BY O.NAME GO-------------以上是各种查看数据库空间.表空间.索引空间.日志空间,索引碎片等信息的一些方法.--欢迎各位大虾指正--查看碎片可利用DBCC SHOWCONTIG 但有性能上的一些影响.--如果用SP_SPACEUSED第二个参数不为TRUE的话可能数据并不准确.--需要看哪种信息再选择适应的方法.
