following info about all the dbs on the server at one go

  • How can i get all the database size information at one go.I am wondering if any sp or method available to list  these info for all the dbs on the server

    Database Name 

    db Current Size 

    Datafile_maxsize 

    Datafile_initsize 

    data filegrowth

    logfile_maxsize 

    logfile_initsize

    logfile growth

  • You will get the maximum information in these 2 tables.

    select * from sysdatabases

    select * from sysaltfiles

    --Kishore

  • This is how I'd solve it : (be aware of collation conflicts !)

    print 'Fillocations of DB'

    print '------------------'

    declare @SelStmt as varchar(7000)

    declare @DbNaam as varchar(125)

    declare @UnionStmt as varchar(10)

    select @SelStmt = ''

    select @UnionStmt = ''

    -- select 'select '+ name +' as DBname , * from '+name+'.dbo.sysfiles ' from master.dbo.sysdatabases

    declare CsrDB insensitive cursor for  select name as DBNaam from master.dbo.sysdatabases for read only

    OPEN CsrDB     /*open the cursor*/

    FETCH NEXT FROM CsrDB INTO  @DbNaam /*Get the 1st row*/

    WHILE @@fetch_status=0   /*set into loop until no more data can be found*/

     BEGIN

     IF not @@fetch_status = -2

        BEGIN

      select @SelStmt = @SelStmt + @UnionStmt + ' Select ''' + ltrim(@DbNaam) +''' as DbNm, * from ' + ltrim(@DbNaam) +'.dbo.sysfiles'

      select @UnionStmt = ' Union All '

        END

     FETCH NEXT FROM CsrDB INTO @DbNaam  /* get the next row*/

     END

    Close CsrDB

    DEALLOCATE CsrDB

    select @SelStmt = @SelStmt + ' Order by DbNm,groupid,fileid'

    exec (@SelStmt )

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • This link has scripts that can be used to cycle through each database on a server, or each table in a database.

    http://www.sqlservercentral.com/scripts/contributions/900.asp

Viewing 4 posts - 1 through 3 (of 3 total)

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