论坛交流
首页办公自动化| 网页制作| 平面设计| 动画制作| 数据库开发| 程序设计| 全部视频教程
应用视频: Windows | Word2007 | Excel2007 | PowerPoint2007 | Dreamweaver 8 | Fireworks 8 | Flash 8 | Photoshop cs | CorelDraw 12
编程视频: C语言视频教程 | HTML | Div+Css布局 | Javascript | Access数据库 | Asp | Sql Server数据库Asp.net  | Flash AS
当前位置 > 文字教程 > Sql Server教程
Tag:注入,存储过程,分页,安全,优化,加密,索引,日志,压缩,base64,函数,内存,PDF,迁移,结构,破解,编译,配置,进程,分词,触发器,socket,安装,sqlserver2000,sqlserver2005,sqlserver2008,视频教程

实用的存储过程之一

文章类别:Sql Server | 发表日期:2008-10-5 21:36:19

 笔者工作的公司采用的是SQLServer数据库,每天都要处理大量的数据,由于笔者进公司的时间比较晚,公司现有的大部分的程序都是以前的程序员留下的,因为他们没有相关的文档,笔者对于后台数据库的很多表的结构和数据都不甚了解,给日常的维护造成了很大的麻烦。

在对后台数据库进行研究的过程中,我需要得到数据库的某些相关信息,比如,我希望知道各个用户表占用多少磁盘空间,并且排列出来,可以让我知道哪些表比较大,数据比较多等等——我相信,这可能也是不少数据库管理员所关心的问题,所以我决心做一个通用的存储过程。我对系统的存储过程sp_spaceused加了一些改动,以适合我的要求。希望这个存储过程能对大家有些帮助。存储过程如下:chin a i t p oe er . co mnsFz4

if exists(select name from sysobjects where name='spaceused' and type='p')chin a i t p oe er . co mnsFz4

Drop procedure spaceusedchin a i t p oe er . co mnsFz4

GOchin a i t p oe er . co mnsFz4

create procedure spaceused chin a i t p oe er . co mnsFz4

aschin a i t p oe er . co mnsFz4

beginchin a i t p oe er . co mnsFz4

 chin a i t p oe er . co mnsFz4

declare @id       int                  -- The object id of @objname.chin a i t p oe er . co mnsFz4

declare @type       character(2) -- The object type.chin a i t p oe er . co mnsFz4

declare       @pages       int                  -- Working variable for size calc.chin a i t p oe er . co mnsFz4

declare @dbname sysnamechin a i t p oe er . co mnsFz4

declare @dbsize dec(15,0)chin a i t p oe er . co mnsFz4

declare @logsize dec(15)chin a i t p oe er . co mnsFz4

declare @bytesperpage       dec(15,0)chin a i t p oe er . co mnsFz4

declare @pagesperMB              dec(15,0)chin a i t p oe er . co mnsFz4

declare @objname nvarchar(776)        -- The object we want size on.chin a i t p oe er . co mnsFz4

declare @updateusage varchar(5)             -- Param. for specifying thatchin a i t p oe er . co mnsFz4

 chin a i t p oe er . co mnsFz4

create table #temp1chin a i t p oe er . co mnsFz4

(chin a i t p oe er . co mnsFz4

       表名              varchar(200) null,chin a i t p oe er . co mnsFz4

       行数               char(11) null,chin a i t p oe er . co mnsFz4

       保留空间        varchar(15) null,chin a i t p oe er . co mnsFz4

       数据使用空间       varchar(15) null,chin a i t p oe er . co mnsFz4

       索引使用空间       varchar(15) null,chin a i t p oe er . co mnsFz4

        未用空间          varchar(15) nullchin a i t p oe er . co mnsFz4

)chin a i t p oe er . co mnsFz4

--select @objname='N_dep'                               -- usage info. should be updated.chin a i t p oe er . co mnsFz4

select @updateusage='false'chin a i t p oe er . co mnsFz4

/*Create temp tables before any DML to ensure dynamicchin a i t p oe er . co mnsFz4

**  We need to create a temp table to do the calculation.chin a i t p oe er . co mnsFz4

**  reserved: sum(reserved) where indid in (0, 1, 255)chin a i t p oe er . co mnsFz4

**  data: sum(dpages) where indid < 2 + sum(used) where indid = 255 (text)chin a i t p oe er . co mnsFz4

**  indexp: sum(used) where indid in (0, 1, 255) - datachin a i t p oe er . co mnsFz4

**  unused: sum(reserved) - sum(used) where indid in (0, 1, 255)chin a i t p oe er . co mnsFz4

*/chin a i t p oe er . co mnsFz4

declare cur_table cursor forchin a i t p oe er . co mnsFz4

  select name from sysobjects where type='u'chin a i t p oe er . co mnsFz4

 chin a i t p oe er . co mnsFz4

Open cur_tablechin a i t p oe er . co mnsFz4

fetch next from cur_table into @objnamechin a i t p oe er . co mnsFz4

 chin a i t p oe er . co mnsFz4

While @@FETCH_STATUS=0chin a i t p oe er . co mnsFz4

beginchin a i t p oe er . co mnsFz4

create table #spt_spacechin a i t p oe er . co mnsFz4

(chin a i t p oe er . co mnsFz4

       rows              int null,chin a i t p oe er . co mnsFz4

       reserved    dec(15) null,chin a i t p oe er . co mnsFz4

       data        dec(15) null,chin a i t p oe er . co mnsFz4

       indexp             dec(15) null,chin a i t p oe er . co mnsFz4

       unused             dec(15) nullchin a i t p oe er . co mnsFz4

)chin a i t p oe er . co mnsFz4

 chin a i t p oe er . co mnsFz4

/*chin a i t p oe er . co mnsFz4

**  Check to see if user wants usages updated.chin a i t p oe er . co mnsFz4

*/chin a i t p oe er . co mnsFz4

 chin a i t p oe er . co mnsFz4

if @updateusage is not nullchin a i t p oe er . co mnsFz4

       beginchin a i t p oe er . co mnsFz4

              select @updateusage=lower(@updateusage)chin a i t p oe er . co mnsFz4

 chin a i t p oe er . co mnsFz4

              if @updateusage not in ('true','false')chin a i t p oe er . co mnsFz4

                     beginchin a i t p oe er . co mnsFz4

                            raiserror(15143,-1,-1,@updateusage)chin a i t p oe er . co mnsFz4

                            return(1)chin a i t p oe er . co mnsFz4

                     endchin a i t p oe er . co mnsFz4

       endchin a i t p oe er . co mnsFz4

/*chin a i t p oe er . co mnsFz4

**  Check to see that the objname is local.chin a i t p oe er . co mnsFz4

*/chin a i t p oe er . co mnsFz4

if @objname IS NOT NULLchin a i t p oe er . co mnsFz4

beginchin a i t p oe er . co mnsFz4

 chin a i t p oe er . co mnsFz4

       select @dbname = parsename(@objname, 3)chin a i t p oe er . co mnsFz4

 chin a i t p oe er . co mnsFz4

       if @dbname is not null and @dbname <> db_name()chin a i t p oe er . co mnsFz4

              beginchin a i t p oe er . co mnsFz4

                     raiserror(15250,-1,-1)chin a i t p oe er . co mnsFz4

                     return (1)chin a i t p oe er . co mnsFz4

              endchin a i t p oe er . co mnsFz4

 chin a i t p oe er . co mnsFz4

       if @dbname is nullchin a i t p oe er . co mnsFz4

              select @dbname = db_name()chin a i t p oe er . co mnsFz4

 chin a i t p oe er . co mnsFz4

       /*chin a i t p oe er . co mnsFz4

       **  Try to find the object.chin a i t p oe er . co mnsFz4

       */chin a i t p oe er . co mnsFz4

       select @id = nullchin a i t p oe er . co mnsFz4

       select @id = id, @type = xtypechin a i t p oe er . co mnsFz4

              from sysobjectschin a i t p oe er . co mnsFz4

                     where id = object_id(@objname)chin a i t p oe er . co mnsFz4

 chin a i t p oe er . co mnsFz4

       /*chin a i t p oe er . co mnsFz4

       **  Does the object exist?chin a i t p oe er . co mnsFz4

       */chin a i t p oe er . co mnsFz4

       if @id is nullchin a i t p oe er . co mnsFz4

              beginchin a i t p oe er . co mnsFz4

                     raiserror(15009,-1,-1,@objname,@dbname)chin a i t p oe er . co mnsFz4

                     return (1)chin a i t p oe er . co mnsFz4

              endchin a i t p oe er . co mnsFz4

 chin a i t p oe er . co mnsFz4

 chin a i t p oe er . co mnsFz4

       if not exists (select * from sysindexeschin a i t p oe er . co mnsFz4

                            where @id = id and indid < 2)chin a i t p oe er . co mnsFz4

 chin a i t p oe er . co mnsFz4

              if      @type in ('P ','D ','R ','TR','C ','RF') --data stored in sysprocedureschin a i t p oe er . co mnsFz4

                            beginchin a i t p oe er . co mnsFz4

                                   raiserror(15234,-1,-1)chin a i t p oe er . co mnsFz4

                                   return (1)chin a i t p oe er . co mnsFz4

                            endchin a i t p oe er . co mnsFz4

              else if @type = 'V ' -- View => no physical data storage.chin a i t p oe er . co mnsFz4

                            beginchin a i t p oe er . co mnsFz4

                                   raiserror(15235,-1,-1)chin a i t p oe er . co mnsFz4

                                   return (1)chin a i t p oe er . co mnsFz4

                            endchin a i t p oe er . co mnsFz4

              else if @type in ('PK','UQ') -- no physical data storage. --?!?! too many similar messageschin a i t p oe er . co mnsFz4

                            beginchin a i t p oe er . co mnsFz4

                                   raiserror(15064,-1,-1)chin a i t p oe er . co mnsFz4

                                   return (1)chin a i t p oe er . co mnsFz4

                            endchin a i t p oe er . co mnsFz4

              else if @type = 'F ' -- FK => no physical data storage.chin a i t p oe er . co mnsFz4

                            beginchin a i t p oe er . co mnsFz4

                                   raiserror(15275,-1,-1)chin a i t p oe er . co mnsFz4

                                   return (1)chin a i t p oe er . co mnsFz4

                            endchin a i t p oe er . co mnsFz4

endchin a i t p oe er . co mnsFz4

 chin a i t p oe er . co mnsFz4

/*chin a i t p oe er . co mnsFz4

**  Update usages if user specified to do so.chin a i t p oe er . co mnsFz4

*/chin a i t p oe er . co mnsFz4

 chin a i t p oe er . co mnsFz4

if @updateusage = 'true'chin a i t p oe er . co mnsFz4

       beginchin a i t p oe er . co mnsFz4

              if @objname is nullchin a i t p oe er . co mnsFz4

                     dbcc updateusage(0) with no_infomsgschin a i t p oe er . co mnsFz4

              elsechin a i t p oe er . co mnsFz4

                     dbcc updateusage(0,@objname) with no_infomsgschin a i t p oe er . co mnsFz4

              print ' 'chin a i t p oe er . co mnsFz4

       endchin a i t p oe er . co mnsFz4

 chin a i t p oe er . co mnsFz4

 chin a i t p oe er . co mnsFz4

set nocount onchin a i t p oe er . co mnsFz4

 chin a i t p oe er . co mnsFz4

/*chin a i t p oe er . co mnsFz4

**  If @id is null, then we want summary data.chin a i t p oe er . co mnsFz4

*/chin a i t p oe er . co mnsFz4

/*    Space used calculated in the following waychin a i t p oe er . co mnsFz4

**       @dbsize = Pages usedchin a i t p oe er . co mnsFz4

**       @bytesperpage = d.low (where d = master.dbo.spt_values) ischin a i t p oe er . co mnsFz4

**    the # of bytes per page when d.type = 'E' andchin a i t p oe er . co mnsFz4

**       d.number = 1.chin a i t p oe er . co mnsFz4

**    Size = @dbsize * d.low / (1048576 (OR 1 MB))chin a i t p oe er . co mnsFz4

*/chin a i t p oe er . co mnsFz4

if @id is nullchin a i t p oe er . co mnsFz4

beginchin a i t p oe er . co mnsFz4

       select @dbsize = sum(convert(dec(15),size))chin a i t p oe er . co mnsFz4

              from dbo.sysfileschin a i t p oe er . co mnsFz4

              where (status & 64 = 0)chin a i t p oe er . co mnsFz4

 chin a i t p oe er . co mnsFz4

       select @logsize = sum(convert(dec(15),size))chin a i t p oe er . co mnsFz4

              from dbo.sysfileschin a i t p oe er . co mnsFz4

              where (status & 64 <> 0)chin a i t p oe er . co mnsFz4

 chin a i t p oe er . co mnsFz4

       select @bytesperpage = lowchin a i t p oe er . co mnsFz4

              from master.dbo.spt_valueschin a i t p oe er . co mnsFz4

              where number = 1chin a i t p oe er . co mnsFz4

                     and type = 'E'chin a i t p oe er . co mnsFz4

       select @pagesperMB = 1048576 / @bytesperpagechin a i t p oe er . co mnsFz4

 chin a i t p oe er . co mnsFz4

       select  database_name = db_name(),chin a i t p oe er . co mnsFz4

              database_size =chin a i t p oe er . co mnsFz4

                     ltrim(str((@dbsize + @logsize) / @pagesperMB,15,2) + ' MB'),chin a i t p oe er . co mnsFz4

              'unallocated space' =chin a i t p oe er . co mnsFz4

                     ltrim(str((@dbsize -chin a i t p oe er . co mnsFz4

                            (select sum(convert(dec(15),reserved))chin a i t p oe er . co mnsFz4

                                   from sysindexeschin a i t p oe er . co mnsFz4

                                          where indid in (0, 1, 255)chin a i t p oe er . co mnsFz4

                            )) / @pagesperMB,15,2)+ ' MB')chin a i t p oe er . co mnsFz4

 chin a i t p oe er . co mnsFz4

       print ' 'chin a i t p oe er . co mnsFz4

       /*chin a i t p oe er . co mnsFz4

       **  Now calculate the summary data.chin a i t p oe er . co mnsFz4

       **  reserved: sum(reserved) where indid in (0, 1, 255)chin a i t p oe er . co mnsFz4

       */chin a i t p oe er . co mnsFz4

       insert into #spt_space (reserved)chin a i t p oe er . co mnsFz4

              select sum(convert(dec(15),reserved))chin a i t p oe er . co mnsFz4

                     from sysindexeschin a i t p oe er . co mnsFz4

                            where indid in (0, 1, 255)chin a i t p oe er . co mnsFz4

 chin a i t p oe er . co mnsFz4

       /*chin a i t p oe er . co mnsFz4

      ** data: sum(dpages) where indid < 2chin a i t p oe er . co mnsFz4

       **    + sum(used) where indid = 255 (text)chin a i t p oe er . co mnsFz4

       */chin a i t p oe er . co mnsFz4

       select @pages = sum(convert(dec(15),dpages))chin a i t p oe er . co mnsFz4

                     from sysindexeschin a i t p oe er . co mnsFz4

                            where indid < 2chin a i t p oe er . co mnsFz4

       select @pages = @pages + isnull(sum(convert(dec(15),used)), 0)chin a i t p oe er . co mnsFz4

              from sysindexeschin a i t p oe er . co mnsFz4

                     where indid = 255chin a i t p oe er . co mnsFz4

       update #spt_spacechin a i t p oe er . co mnsFz4

              set data = @pageschin a i t p oe er . co mnsFz4

 chin a i t p oe er . co mnsFz4

 chin a i t p oe er . co mnsFz4

       /* index: sum(used) where indid in (0, 1, 255) - data */chin a i t p oe er . co mnsFz4

       update #spt_spacechin a i t p oe er . co mnsFz4

              set indexp = (select sum(convert(dec(15),used))chin a i t p oe er . co mnsFz4

                            from sysindexeschin a i t p oe er . co mnsFz4

                                   where indid in (0, 1, 255))chin a i t p oe er . co mnsFz4

                         - datachin a i t p oe er . co mnsFz4

 chin a i t p oe er . co mnsFz4

       /* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */chin a i t p oe er . co mnsFz4

       update #spt_spacechin a i t p oe er . co mnsFz4

              set unused = reservedchin a i t p oe er . co mnsFz4

                            - (select sum(convert(dec(15),used))chin a i t p oe er . co mnsFz4

                                   from sysindexeschin a i t p oe er . co mnsFz4

                                          where indid in (0, 1, 255))chin a i t p oe er . co mnsFz4

 chin a i t p oe er . co mnsFz4

       select reserved = ltrim(str(reserved * d.low / 1024.,15,0) +chin a i t p oe er . co mnsFz4

                            ' ' + 'KB'),chin a i t p oe er . co mnsFz4

              data = ltrim(str(data * d.low / 1024.,15,0) +chin a i t p oe er . co mnsFz4

                            ' ' + 'KB'),chin a i t p oe er . co mnsFz4

              index_size = ltrim(str(indexp * d.low / 1024.,15,0) +chin a i t p oe er . co mnsFz4

                            ' ' + 'KB'),chin a i t p oe er . co mnsFz4

              unused = ltrim(str(unused * d.low / 1024.,15,0) +chin a i t p oe er . co mnsFz4

                            ' ' + 'KB')chin a i t p oe er . co mnsFz4

              from #spt_space, master.dbo.spt_values dchin a i t p oe er . co mnsFz4

              where d.number = 1chin a i t p oe er . co mnsFz4

                     and d.type = 'E'chin a i t p oe er . co mnsFz4

endchin a i t p oe er . co mnsFz4

 chin a i t p oe er . co mnsFz4

/*chin a i t p oe er . co mnsFz4

**  We want a particular object.chin a i t p oe er . co mnsFz4

*/chin a i t p oe er . co mnsFz4

elsechin a i t p oe er . co mnsFz4

beginchin a i t p oe er . co mnsFz4

       /*chin a i t p oe er . co mnsFz4

       **  Now calculate the summary data.chin a i t p oe er . co mnsFz4

       **  reserved: sum(reserved) where indid in (0, 1, 255)chin a i t p oe er . co mnsFz4

       */chin a i t p oe er . co mnsFz4

       insert into #spt_space (reserved)chin a i t p oe er . co mnsFz4

              select sum(reserved)chin a i t p oe er . co mnsFz4

                     from sysindexeschin a i t p oe er . co mnsFz4

                            where indid in (0, 1, 255)chin a i t p oe er . co mnsFz4

                                   and id = @idchin a i t p oe er . co mnsFz4

 chin a i t p oe er . co mnsFz4

       /*chin a i t p oe er . co mnsFz4

      ** data: sum(dpages) where indid < 2chin a i t p oe er . co mnsFz4

       **    + sum(used) where indid = 255 (text)chin a i t p oe er . co mnsFz4

       */chin a i t p oe er . co mnsFz4

       select @pages = sum(dpages)chin a i t p oe er . co mnsFz4

                     from sysindexeschin a i t p oe er . co mnsFz4

                            where indid < 2chin a i t p oe er . co mnsFz4

                                   and id = @idchin a i t p oe er . co mnsFz4

       select @pages = @pages + isnull(sum(used), 0)chin a i t p oe er . co mnsFz4

              from sysindexeschin a i t p oe er . co mnsFz4

                     where indid = 255chin a i t p oe er . co mnsFz4

                            and id = @idchin a i t p oe er . co mnsFz4

       update #spt_spacechin a i t p oe er . co mnsFz4

              set data = @pageschin a i t p oe er . co mnsFz4

 chin a i t p oe er . co mnsFz4

 chin a i t p oe er . co mnsFz4

       /* index: sum(used) where indid in (0, 1, 255) - data */chin a i t p oe er . co mnsFz4

       update #spt_spacechin a i t p oe er . co mnsFz4

              set indexp = (select sum(used)chin a i t p oe er . co mnsFz4

                            from sysindexeschin a i t p oe er . co mnsFz4

                                   where indid in (0, 1, 255)chin a i t p oe er . co mnsFz4

                                          and id = @id)chin a i t p oe er . co mnsFz4

                         - datachin a i t p oe er . co mnsFz4

 chin a i t p oe er . co mnsFz4

       /* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */chin a i t p oe er . co mnsFz4

       update #spt_spacechin a i t p oe er . co mnsFz4

              set unused = reservedchin a i t p oe er . co mnsFz4

                            - (select sum(used)chin a i t p oe er . co mnsFz4

                                   from sysindexeschin a i t p oe er . co mnsFz4

                                          where indid in (0, 1, 255)chin a i t p oe er . co mnsFz4

                                                 and id = @id)chin a i t p oe er . co mnsFz4

       update #spt_spacechin a i t p oe er . co mnsFz4

              set rows = i.rowschin a i t p oe er . co mnsFz4

                     from sysindexes ichin a i t p oe er . co mnsFz4

                            where i.indid < 2chin a i t p oe er . co mnsFz4

                                   and i.id = @idchin a i t p oe er . co mnsFz4

        insert into #temp1chin a i t p oe er . co mnsFz4

       select name = object_name(@id),chin a i t p oe er . co mnsFz4

              rows = convert(char(11), rows),chin a i t p oe er . co mnsFz4

              reserved = ltrim(str(reserved * d.low / 1024.,15,0) +chin a i t p oe er . co mnsFz4

                            ' ' + 'KB'),chin a i t p oe er . co mnsFz4

              data = ltrim(str(data * d.low / 1024.,15,0) +chin a i t p oe er . co mnsFz4

                            ' ' + 'KB'),chin a i t p oe er . co mnsFz4

              index_size = ltrim(str(indexp * d.low / 1024.,15,0) +chin a i t p oe er . co mnsFz4

                            ' ' + 'KB'),chin a i t p oe er . co mnsFz4

              unused = ltrim(str(unused * d.low / 1024.,15,0) +chin a i t p oe er . co mnsFz4

                            ' ' + 'KB')chin a i t p oe er . co mnsFz4

       from #spt_space, master.dbo.spt_values dchin a i t p oe er . co mnsFz4

              where d.number = 1chin a i t p oe er . co mnsFz4

                     and d.type = 'E'chin a i t p oe er . co mnsFz4

Drop table #spt_spacechin a i t p oe er . co mnsFz4

endchin a i t p oe er . co mnsFz4

fetch next from cur_table into @objnamechin a i t p oe er . co mnsFz4

endchin a i t p oe er . co mnsFz4

Close cur_tablechin a i t p oe er . co mnsFz4

DEALLOCATE cur_tablechin a i t p oe er . co mnsFz4

Select * from #temp1 order by len(保留空间) desc,保留空间 descchin a i t p oe er . co mnsFz4

Drop table #temp1chin a i t p oe er . co mnsFz4

return (0)chin a i t p oe er . co mnsFz4

endchin a i t p oe er . co mnsFz4

原理很简单,相信大家都能看懂,sp_spaceused几乎原封不动地保留下来,调用也很简单,直接执行即可,没有任何参数,存储过程执行后,将把当前连接的数据库中所有数据表按照从大到小排列出来,还有其他的相关信息。如果能对大家有所参考价值,就请大家能给forgot2000一点掌声鼓励吧,谢谢!chin a i t p oe er . co mnsFz4

本存储过程在SQLServer7.0/2000下通过。chin a i t p oe er . co mnsFz4

视频教程列表
文章教程搜索
 
Sql Server推荐教程
Sql Server热门教程
看全部视频教程
购买方式/价格
购买视频教程: 咨询客服
tel:15972130058