筆者工作的公司采用的是 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 下通過。

實用的存儲過程