Post reply

Listing database sizes

  • May 16, 2005 at 9:07 am

    #93005

    HI. is there a way to quickly list the sized of all the databases on a sql server?

    Juanita

     

  • May 16, 2005 at 9:34 am

    #559077

    exec master.dbo.sp_helpdb

    SQL = Scarcely Qualifies as a Language

  • May 16, 2005 at 9:36 am

    #559078

    THANKS SO MUCH !!! PERFECT !

  • May 16, 2005 at 5:23 pm

    #559154

    Juanita

    Thought this might be useful as well it lists the sizes of all the databses and the free space left in each

    DECLARE @database sysname

    DECLARE databases CURSOR FOR

    SELECT CATALOG_NAME

    FROM master.INFORMATION_SCHEMA.SCHEMATA

    open databases

    FETCH NEXT FROM databases

    INTO @database

    CREATE TABLE #temp(

    DBName varchar(250),

    DBSize varchar(250),

    DBFreeSpace varchar(250)

    )

    WHILE @@FETCH_STATUS = 0

    BEGIN

    declare @sqlstr varchar(4000)

    insert into #temp

    exec ('

    USE ' +@database+ '

    DBCC UPDATEUSAGE ( '+@database+ ')

    declare @dbname sysname

    declare @dbsize dec(15,0)

    declare @logsize dec(15)

    declare @bytesperpagedec(15,0)

    declare @pagesperMBdec(15,0)

    select @dbsize = sum(convert(dec(15),size))

    from ' +@database+'.dbo.sysfiles' +

    ' where (status & 64 = 0) ' +

    ' select @logsize = sum(convert(dec(15),size))

    from ' +@database+ '.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 ' +@database+ '.dbo.sysindexes' +

    ' where indid in (0, 1, 255)

    )) / @pagesperMB,15,2)+ '' MB '') ' )

    FETCH NEXT FROM databases

    INTO @database

    END

    select * from #temp

    drop table #temp

    close databases

    deallocate databases

    regards

    David

  • May 17, 2005 at 7:03 am

    #559240

    try exec master..sp_databses

     

  • May 17, 2005 at 7:23 am

    #559250

    David,

    Thank you for the code. This is great !!.

    Glauco,

    Can you send that stored proc again? I tried to execute it and it says that doesn't exist.

     

     

  • May 17, 2005 at 7:34 am

    #559253

    He jsut misspelled the proc.

    Its exec master..sp_databases

    --Kishore

  • May 17, 2005 at 7:58 am

    #559260

    HI. Thanks so much. That was it!

    Juanita

     

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply