一个查看MSSQLServer数据库空间使用情况的存储过程 SpaceUsed

时间:2022-06-01 22:11:43

运行下面存储过程 

然后直接使用 SpaceUsed 就可以查看了. 

存储过程代码 

 程序代码 

复制代码代码如下:


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,保留空间 desc  

Drop table #temp1  

return (0)  

end  

GO