如何巧用系統(tǒng)存儲過程批量獲取數(shù)據(jù)庫中的有用信息分析

1、如何巧用系統(tǒng)存儲過程批量獲取數(shù)據(jù)庫中的有用信息分析    摘要：巧妙利用存儲過程可以使得對數(shù)據(jù)庫的管理、以及顯示關(guān)于數(shù)據(jù)庫、基表以及用戶信息的工作容易得多，本文所介紹的就是這方面應(yīng)用的一個實(shí)例。關(guān)鍵詞：系統(tǒng)存儲過程；數(shù)據(jù)庫，SQL SERVER1引言在SQL SERVER數(shù)據(jù)庫維護(hù)中，通常我們可以在查詢分析器（Query analyzer）中輸入SQL的select命令從數(shù)據(jù)庫相關(guān)系統(tǒng)表（例如：sysdatabase、sysobjects、sysfiles等）中或者使用execute命令調(diào)用系統(tǒng)存儲過程（例如sp_helpu

2、ser 、sp_spaceusd、sp_helpdb、sp_helpfile等）來查看注冊用戶的情況并獲取有關(guān)數(shù)據(jù)庫大小、邏輯或物理文件名和存放位置等以及相關(guān)基表中的記錄數(shù)、保留或已用空間等信息。雖然所列出或報告的信息都是各有所長，但都存在著不同的缺陷，主要有：其一，用select命令直接打開的系統(tǒng)表，由于其中某些字段存儲的數(shù)據(jù)多為數(shù)字或者二進(jìn)制代碼，很難被人直觀地理解；其二，由系統(tǒng)存儲過程產(chǎn)生的報告因?yàn)槟承┳侄翁L顯得十分難看；其三，特別是使用sp_spaceused時，每次只能查詢一個基表，假如某個用戶數(shù)據(jù)庫中有幾百個基表，其查詢的難度和效率可想而知。針對以上所述侍猓收吒械接斜匾

3、嘈匆桓鍪滌玫某絳蚶賜瓿燒庋牟僮鰨河沒灰誦興淮尉涂梢苑獎憧旖蕕亟康氖菘饣拘畔約澳掣鍪菘庾嵊沒那榭觥謝淼母鞣矯孀酆閑畔枷允境隼礎(chǔ)比唬飧齔絳蛐枰攬亢屠孟鐘械南低潮硨湍承低炒媧湯賜瓿桑頤侵皇嵌運(yùn)墻幸恍擅畹鬧刈楹透腦歟僂ü嘈蔥碌拇媧倘饔盟牽詈笊晌頤撬枰奈牡底柿稀辛蘇庋氖滌貿(mào)絳潁亟院蟮南低澄鞔醇蟮姆獎恪?lt;B>2改造系統(tǒng)存儲過程基于以上的思路，首先我們主要對sp_spaceused、sp_helpdb和hp_helpfile三個系統(tǒng)存儲過程進(jìn)行改造，在master數(shù)據(jù)庫的storeProcedures中打開它們的源程序，并把源代碼拷貝到查詢分析器中，分別用sp_brow_one.sql

4、、sp_help_db.sql和sp_help_file.sql腳本文件名保存，然后再對它們進(jìn)行編輯和修改。（1）編輯修改sp_brow_one。目的是取消以輸出報告顯示的方式，而改用輸出參數(shù)傳遞給調(diào)用它的程序，改動的地方主要有以下四處：將源程序16行，修改成：use master  /* 保證將sp_brow_one寫入主控數(shù)據(jù)庫中*/if exists(select name from sysobjects where name='sp_brow_one' and&

5、#160;type='p')   Drop procedure sp_brow_oneGOcreate procedure sp_brow_one   objname nvarchar(50) = null, updateusage varchar(5) = false,rows nvarchar(10) output, reserved nvarchar(15)

6、0;output, data nvarchar(15) output,indexp  nvarchar(15) output, unused nvarchar(15) outputasdeclare name nvarchar(30)保留了原來的兩個輸入?yún)?shù)，新增5個輸出參數(shù)和1個name變量。將源程序中的142152行取消，用符號“/*” 和 “*/ ”將其框起來。將源程序中的190200行中的五個字段名全部用變量名替換掉（例如將reserved換成re

7、served等），其它地方不要做任何改動。將源程序中的255267行中六個字段名全部用變量名替換掉（例如將name換成name等），其它地方不要做任何改動。（2）編輯修改sp_help_db。目的是調(diào)整輸出報告的顯示方式，將太長的字段調(diào)整到合適的長度，主要使用substring()和left()字符串函數(shù)。修改的地方有以下三處：將源程序的第1行修改成：use master  /* 保證將sp_help_db寫入主控數(shù)據(jù)庫中*/if exists(select name from sysobjects wh

8、ere name='sp_help_db' and type='p')   Drop procedure sp_help_dbGOcreate procedure sp_help_db 將源程序中的432445修改成：select name = substring(, 1, 20),db_size = str(sum(convert(dec(15),v.size)*

9、0;          (select low from master.dbo.spt_values                    where type = 'E' and number 

10、;= 1)                     / 1048576,10,2)+ ' MB',owner = substring(suser_sname(d.sid), 1, 10),dbid = d.dbid,created = d.crdate,

11、 /*convert(char(11), d.crdate),*/status =substring(s.dbdesc,1,50)from master.dbo.sysdatabases d, #spfiledesc v, #spdbdesc swhere d.dbid = s.dbidand s.dbid = v.dbidgroup by ,d.sid,d.dbid,d.crdate,s.dbdescorder&

12、#160;by d.dbid  /* order by  */將源程序中的450456行取消，用符號“/*” 和 “*/ ”將其框起來。（3）編輯修改sp_help_file。目的與2相同；但源程序不能直接利用，必須在所調(diào)用的sysfiles基表前加上前綴（數(shù)據(jù)庫名和所有者）。由于程序改動較大，在此全部列出：use master   /* 保證將sp_help_file寫入主控數(shù)據(jù)庫中*/if exists(select&#

13、160;name from sysobjects where name='sp_help_file' and type='p')   Drop procedure sp_help_fileGOcreate procedure sp_help_file dbname sysname = NULL        

14、0;             /* file name or all files */asset nocount ondeclare cmd nvarchar(800)if dbname IS not NULLbegin   select cmd = 'use&

15、#160;' + quotename(dbname) +   '  select name=substring(name,1,20),  fileid, ' +       'filename=substring(filename,1,40),       filegroup =

16、0;substring(filegroup_name(groupid),1,10),       "size" = convert(nvarchar(15), size * 8)+ " KB",       "maxsize" = (case maxsize when -1

17、60;then "Unlimited"              else convert(nvarchar(15), maxsize * 8)+ " KB" end),       "growth" = (case s

18、tatus & 0x100000 when 0x100000 then             convert(nvarchar(3), growth) + "%"               else 

19、;convert(nvarchar(15), growth * 8) + " KB" end),       "usage" = (case status & 0x40 when 0x40 then        "log only" else "data only" end)' +   ' from ' + dbname + '.dbo.sy