WEB开发网
开发学院数据库MSSQL Server 实用的存储过程之一 阅读

实用的存储过程之一

 2007-11-11 07:07:12 来源:WEB开发网   
核心提示: 笔者工作的公司采用的是SQLServer数据库,每天都要处理大量的数据,实用的存储过程之一,由于笔者进公司的时间比较晚,公司现有的大部分的程序都是以前的程序员留下的,如果能对大家有所参考价值,就请大家能给forgot2000一点掌声鼓励吧,因为他们没有相关的文档,笔者对于后台数据库的很多表的结构和数据都不甚了解

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

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

if exists(select name from sysobjects where name='spaceused' and type='p')

Drop procedure spaceused

GO

create procedure spaceused

as

begin

 (标准化越来越近了):namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

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下通过。

1 2 3 4 5 6  下一页

Tags:实用 存储 过程

编辑录入:coldstar [复制链接] [打 印]
赞助商链接