筆者工作的公司采用的是 SQLServer 數據庫，每天都要處理大量的數據，由于筆者進公司的時間比較晚，公司現有的大部分的程序都是以前的程序員留下的，因為他們沒有相關的文檔，筆者對于后臺數據庫的很多表的結構和數據都不甚了解，給日常的維護造成了很大的麻煩。
在對后臺數據庫進行研究的過程中，我需要得到數據庫的某些相關信息，比如，我希望知道各個用戶表占用多少磁盤空間，并且排列出來，可以讓我知道哪些表比較大，數據比較多等等——我相信，這可能也是不少數據庫管理員所關心的問題，所以我決心做一個通用的存儲過程。我對系統的存儲過程 sp_spaceused 加了一些改動，以適合我的要求。希望這個存儲過程能對大家有些幫助。存儲過程如下：
if exists(select name from sysobjects where name='spaceused' and type='p')
Drop procedure spaceused
GO
create procedure spaceused
as
begin
?
declare @id ?????? int ?????????? ?????? -- The object id of @objname.
declare @type ?????? character(2) -- The object type.
declare ?????? @pages ?????? int ?????????? ?????? -- Working variable for size calc.
declare @dbname sysname
declare @dbsize dec(15,0)
declare @logsize dec(15)
declare @bytesperpage ?????? dec(15,0)
declare @pagesperMB ?????? ?????? dec(15,0)
declare @objname nvarchar(776) ?????? -- The object we want size on.
declare @updateusage varchar(5) ????? ?????? -- Param. for specifying that
?
create table #temp1
(
?????? 表名 ?????? ?????? varchar(200) null,
?????? 行數 ?????? ??????? char(11) null,
?????? 保留空間 ?????? varchar(15) null,
?????? 數據使用空間 ?????? varchar(15) null,
?????? 索引使用空間 ?????? varchar(15) null,
??????? 未用空間 ????????? varchar(15) null
)
--select @objname='N_dep' ???????????????? ????????????? -- usage info. should be updated.
select @updateusage='false'
/*Create temp tables before any DML to ensure dynamic
**?We need to create a temp table to do the calculation.
**?reserved: sum(reserved) where indid in (0, 1, 255)
**?data: sum(dpages) where indid < 2 + sum(used) where indid = 255 (text)
**?indexp: sum(used) where indid in (0, 1, 255) - data
**?unused: sum(reserved) - sum(used) where indid in (0, 1, 255)
*/
declare cur_table cursor for
?select name from sysobjects where type='u'
?
Open cur_table
fetch next from cur_table into @objname
?
While @@FETCH_STATUS=0
begin
create table #spt_space
(
?????? rows ????????????? int null,
?????? reserved ??? dec(15) null,
?????? data ?????? dec(15) null,
?????? indexp ???????????? dec(15) null,
?????? unused ???????????? dec(15) null
)
?
/*
**?Check to see if user wants usages updated.
*/
?
if @updateusage is not null
?????? begin
?????? ?????? select @updateusage=lower(@updateusage)
?
????????????? if @updateusage not in ('true','false')
????????????? ?????? begin
????????????? ?????? ?????? raiserror(15143,-1,-1,@updateusage)
????????????? ?????? ?????? return(1)
????????????? ?????? end
?????? end
/*
**?Check to see that the objname is local.
*/
if @objname IS NOT NULL
begin
?
?????? select @dbname = parsename(@objname, 3)
?
?????? if @dbname is not null and @dbname <> db_name()
?????? ?????? begin
????????????? ?????? raiserror(15250,-1,-1)
????????????? ?????? return (1)
?????? ?????? end
?
?????? if @dbname is null
?????? ?????? select @dbname = db_name()
?
?????? /*
?????? **?Try to find the object.
?????? */
?????? select @id = null
?????? select @id = id, @type = xtype
?????? ?????? from sysobjects
????????????? ?????? where id = object_id(@objname)
?
?????? /*
?????? **?Does the object exist?
?????? */
?????? if @id is null
?????? ?????? begin
????????????? ?????? raiserror(15009,-1,-1,@objname,@dbname)
????????????? ?????? return (1)
?????? ?????? end
?
?
?????? if not exists (select * from sysindexes
????????????? ????????????? where @id = id and indid < 2)
?
?????? ?????? if ????? @type in ('P ','D ','R ','TR','C ','RF') --data stored in sysprocedures
????????????? ????????????? begin
????????????? ????????????? ?????? raiserror(15234,-1,-1)
????????????? ????????????? ?????? return (1)
????????????? ????????????? end
?????? ?????? else if @type = 'V ' -- View => no physical data storage.
????????????? ????????????? begin
????????????? ????????????? ?????? raiserror(15235,-1,-1)
????????????? ????????????? ?????? return (1)
????????????? ????????????? end
?????? ?????? else if @type in ('PK','UQ') -- no physical data storage. --?!?! too many similar messages
????????????? ????????????? begin
????????????? ????????????? ?????? raiserror(15064,-1,-1)
????????????? ????????????? ?????? return (1)
????????????? ????????????? end
?????? ?????? else if @type = 'F ' -- FK => no physical data storage.
????????????? ????????????? begin
????????????? ????????????? ?????? raiserror(15275,-1,-1)
????????????? ????????????? ?????? return (1)
????????????? ????????????? end
end
?
/*
**?Update usages if user specified to do so.
*/
?
if @updateusage = 'true'
?????? begin
????????????? if @objname is null
????????????? ?????? dbcc updateusage(0) with no_infomsgs
?????? ?????? else
????????????? ?????? dbcc updateusage(0,@objname) with no_infomsgs
?????? ?????? print ' '
?????? end
?
?
set nocount on
?
/*
**?If @id is null, then we want summary data.
*/
/* ??? Space used calculated in the following way
** ?????? @dbsize = Pages used
** ?????? @bytesperpage = d.low (where d = master.dbo.spt_values) is
** ??? the # of bytes per page when d.type = 'E' and
** ?????? d.number = 1.
** ??? Size = @dbsize * d.low / (1048576 (OR 1 MB))
*/
if @id is null
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
?
?????? 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')
?
?????? print ' '
?????? /*
?????? **?Now calculate the summary data.
?????? **?reserved: sum(reserved) where indid in (0, 1, 255)
?????? */
?????? insert into #spt_space (reserved)
?????? ?????? select sum(convert(dec(15),reserved))
????????????? ?????? from sysindexes
????????????? ????????????? where indid in (0, 1, 255)
?
?????? /*
????? ** data: sum(dpages) where indid < 2
?????? ** ??? + sum(used) where indid = 255 (text)
?????? */
?????? 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
?
?
?????? /* index: sum(used) where indid in (0, 1, 255) - data */
?????? update #spt_space
?????? ?????? set indexp = (select sum(convert(dec(15),used))
????????????? ????????????? from sysindexes
????????????? ????????????? ?????? where indid in (0, 1, 255))
????????????? ?????? ??? - data
?
?????? /* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
?????? update #spt_space
?????? ?????? set unused = reserved
????????????? ????????????? - (select sum(convert(dec(15),used))
????????????? ????????????? ?????? from sysindexes
????????????? ???????????????????? ?????? where indid in (0, 1, 255))
?
?????? select 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')
?????? ?????? from #spt_space, master.dbo.spt_values d
?????? ?????? where d.number = 1
????????????? ?????? and d.type = 'E'
end
?
/*
**?We want a particular object.
*/
else
begin
?????? /*
?????? **?Now calculate the summary data.
?????? **?reserved: sum(reserved) where indid in (0, 1, 255)
?????? */
?????? insert into #spt_space (reserved)
?????? ?????? select sum(reserved)
????????????? ?????? from sysindexes
????????????? ????????????? where indid in (0, 1, 255)
????????????? ????????????? ?????? and id = @id
?
?????? /*
????? ** data: sum(dpages) where indid < 2
?????? ** ??? + sum(used) where indid = 255 (text)
?????? */
?????? select @pages = sum(dpages)
????????????? ?????? from sysindexes
????????????? ????????????? where indid < 2
????????????? ????????????? ?????? and id = @id
?????? select @pages = @pages + isnull(sum(used), 0)
?????? ?????? from sysindexes
????????????? ?????? where indid = 255
????????????? ????????????? and id = @id
?????? update #spt_space
?????? ?????? set data = @pages
?
?
?????? /* index: sum(used) where indid in (0, 1, 255) - data */
?????? update #spt_space
?????? ?????? set indexp = (select sum(used)
????????????? ????????????? from sysindexes
????????????? ????????????? ?????? where indid in (0, 1, 255)
????????????? ???????????????????? ?????? and id = @id)
????????????? ?????? ??? - data
?
?????? /* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
?????? update #spt_space
?????? ?????? set unused = reserved
????????????? ????????????? - (select sum(used)
????????????? ????????????? ?????? from sysindexes
????????????? ???????????????????? ?????? where indid in (0, 1, 255)
????????????? ???????????????????? ????????????? and id = @id)
?????? update #spt_space
?????? ?????? set rows = i.rows
????????????? ?????? from sysindexes i
????????????? ????????????? where i.indid < 2
????????????? ????????????? ?????? and i.id = @id
??????? insert into #temp1
?????? select name = object_name(@id),
?????? ?????? rows = convert(char(11), rows),
?????? ?????? 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')
?????? from #spt_space, master.dbo.spt_values d
?????? ?????? where d.number = 1
????????????? ?????? and d.type = 'E'
Drop table #spt_space
end
fetch next from cur_table into @objname
end
Close cur_table
DEALLOCATE cur_table
Select * from #temp1 order by len( 保留空間 ) desc, 保留空間 desc
Drop table #temp1
return (0)
end
原理很簡單，相信大家都能看懂， sp_spaceused 幾乎原封不動地保留下來，調用也很簡單，直接執行即可，沒有任何參數，存儲過程執行后，將把當前連接的數據庫中所有數據表按照從大到小排列出來，還有其他的相關信息。如果能對大家有所參考價值，就請大家能給 forgot2000 一點掌聲鼓勵吧，謝謝！
本存儲過程在 SQLServer7.0/2000 下通過。
實用的存儲過程之二
筆者工作的公司采用的是 SQLServer 數據庫，每天都要處理大量的數據，由于筆者進公司的時間比較晚，公司現有的大部分的程序都是以前的程序員留下的，因為他們沒有相關的文檔，筆者對于后臺數據庫的很多表的結構和數據都不甚了解，給日常的維護造成了很大的麻煩。
在對后臺數據庫進行研究的過程中，我需要得到數據庫的某些相關信息，比如，公司的數據庫中有幾個表存放筆者的個人資料 , 像人事表、工資表、部門表等等，但具體是哪些表，就不是很清楚了，如果要一個一個表地找，可能天亮了也找不完，所以我決定做一個通用的存儲過程，能對當前數據庫所有字符型字段進行遍歷，找出精確匹配含有要查找字符串的表和字段，并且羅列出來。比如，人事表的 Name 字段，工資表的 Salary_Name 字段，部門表的 Employe_Name 字段都有筆者的名字，我希望能把這些找出來。存儲過程如下：
IF EXISTS (SELECT name FROM sysobjects
???????? WHERE name = 'searchname' AND type = 'P')
?? DROP PROCEDURE searchname
Go
create procedure searchname @sname varchar(10)
As
begin
create table #TableList(
?tablename?char(200),
?colname char(200)
)
?
declare @table varchar(200)
declare @col varchar(200)
?
set nocount on
declare curTab scroll cursor for select name from sysobjects where xtype='u'
open curTab
fetch next from curTab into @table
while @@FETCH_STATUS=0
begin
?declare curCol scroll cursor for select name from syscolumns where (xtype=175 or xtype=167) and (id in (select id from sysobjects where name=@table))
?open curCol
??fetch next from curCol into @col
?while @@FETCH_STATUS=0
?begin
??? execute('insert into #TableList select '''+@table+''','''+@col+''' from '+@table+' where '+@col+'='''+@sname+'''')
??? fetch next from curCol into @col
?end
?close curCol
?deallocate curCol
?fetch next from curTab into @table
end
close curTab
deallocate curTab
set nocount off
select?distinct * from #TableList
drop table #tablelist?
end
調用很簡單，如想找筆者的名字，調用 SearchName ‘forgot2000’ 即可，查找速度視乎當前數據庫的大小而定。希望這個存儲過程能對大家有所幫助吧。本人 E-mail:coolforgot@sina.com,QQ:33563255, 希望能跟大家共同交流，謝謝 !
本存儲過程在 SQLServer7.0/2000 下通過。
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
