adding mb, kb and gb to database file report

Question

Post reply

adding mb, kb and gb to database file report

lara31

SSCommitted

Points: 1802
More actions
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.

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

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

calvo SSChampion Points: 12930 More actions · Answer 1

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.

Lowell SSC Guru Points: 323518 More actions · Answer 2

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!

lara31 SSCommitted Points: 1802 More actions · Answer 3

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.

Lowell SSC Guru Points: 323518 More actions · Answer 4

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!