Post reply

adding mb, kb and gb to database file report

  • August 4, 2010 at 7:57 am

    #225788

    I have a job that pulls a space report on all the databases on a server, i know how to convert to mb, however i want to be able to display the data in my report as 10 kb or 10mb or 10gb, depending on the size of the data in that column. any ideas?

    [font="Comic Sans MS"][/font]It is what it is.

  • August 4, 2010 at 9:15 am

    #1202145

    Are you able to post the query you're running?

    You'd probably get more help if you supplied the query in question.

    ______________________________________________________________________________________________
    Forum posting etiquette.[/url] Get your answers faster.

  • August 4, 2010 at 9:17 am

    #1202147

    this should help you visualize; you'll need a case statement:

    /*--results

    KB MB GB TB val val2 formatted

    80416.0 78.531250 0.07669067382 0.0000748932361523 80416.00 80416.00 XB 78.53 MB

    169.0 0.165039 0.00016117089 0.0000001573934472 169.00 169.00 XB 169.00 KB

    */

    select

    size * 1.0 As KB,

    (size * 1.0 /1024) As MB,

    ((size * 1.0 /1024)/1024) As GB,

    (((size * 1.0 /1024)/1024)/1024) As TB,

    convert(decimal(10,2),size * 1.0) As val,

    convert(varchar,convert(decimal(10,2),size * 1.0)) + ' XB' As val2,

    CASE

    WHEN (((size * 1.0 /1024)/1024)/1024) >= 1

    THEN convert(varchar,convert(decimal(10,2),(((size * 1.0 /1024)/1024)/1024))) + + ' TB'

    WHEN ((size * 1.0 /1024)/1024) > = 1

    THEN convert(varchar,convert(decimal(10,2),((size * 1.0 /1024)/1024))) + + ' GB'

    WHEN (size * 1.0 /1024) > = 1

    THEN convert(varchar,convert(decimal(10,2),(size * 1.0 /1024))) + + ' MB'

    ELSE convert(varchar,convert(decimal(10,2),size * 1.0)) + + ' KB'

    END As formatted

    from sys.sysfiles

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 4, 2010 at 9:20 am

    #1202149

    lowell,

    perfect. i just could not wrap my mind around it at first. but your response works for me.

    thanks thanks thanks

    [font="Comic Sans MS"][/font]It is what it is.

  • August 4, 2010 at 9:32 am

    #1202153

    glad i could help; there's a lot to do in there to make it report-presentable..convert to decimal a couple of times along with the division and case statement;

    much easier to visualize with a working example.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

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

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