SQL Stuff

Find Database, Size, Data, Log, And Filepaths

Here’s some simple logic to get you the database name, data, log, size, and file path for all your data files.

use master;
set nocount on

select
	[database]	= db_name(smf.database_id)
,	[file_id]
,	[type]		= (case [type] when 0 then 'data' when 1 then 'log' when 2 then 'filestream' when 4 then 'fulltext' end)
,	[logical_name]	= smf.name
,	[physical_name]
,	[size]	=	( 
				case 
					when smf.size * 8 / 1024 / 1024 < 1 then cast(smf.size * 8 / 1024 as varchar) + ' MB'
					when smf.size * 8 / 2014 / 1024 > 0 then cast(smf.size * 8 / 1024 /1024 as varchar) + ' GB'
				end
				)
from
	sys.master_files smf
order by
	smf.size desc

[end]

Advertisements

Categories: SQL Stuff

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s