Database sizes and physical location

[techipa]

Hi All,

Can any one tell me what is the command to see the space used by each database and their physical location on the server?

I use the following to list the databases and the space used by them but it does not show the location of their physical files on the server.

exec sp_spaceused exec sp_databases
exec sp_helpdb

Thanks in advance
-techiPA

 

[gmmastros]

sp_helpfile

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[techipa]

George,

Thanks for the reply.

sp_helpfile shows the information only on the current database. It does not give me the list of all the databases and their size and lfiel location.

-techiPA

 

[gmmastros]

OK. How about this...

sp_msforeachdb '
select 	name,  fileid, filename,
	filegroup = filegroup_name(groupid),
	''size'' = convert(nvarchar(15), size * 8) + N'' KB'',
	''maxsize'' = (case maxsize when -1 then N''Unlimited''
			else
			convert(nvarchar(15), maxsize * 8) + N'' KB'' end),
	''growth'' = (case status & 0x100000 when 0x100000 then
		convert(nvarchar(3), growth) + N''%''
		else
		convert(nvarchar(15), growth * 8) + N'' KB'' end),
	''usage'' = (case status & 0x40 when 0x40 then ''log only'' else ''data only'' end)
	from [?]..sysfiles
	order by fileid'

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Or, maybe this... (it's formatted a little better).

Create Table #Temp(Name sysname, FileId int, FileName varchar(1000), FileGroup VarChar(100), Size Varchar(100), MaxSize Varchar(100), Growth VarChar(100), usage varchar(100))

exec sp_msforeachdb 'Insert Into #Temp
select 	name,  fileid, filename,
	filegroup = filegroup_name(groupid),
	''size'' = convert(nvarchar(15), size * 8) + N'' KB'',
	''maxsize'' = (case maxsize when -1 then N''Unlimited''
			else
			convert(nvarchar(15), maxsize * 8) + N'' KB'' end),
	''growth'' = (case status & 0x100000 when 0x100000 then
		convert(nvarchar(3), growth) + N''%''
		else
		convert(nvarchar(15), growth * 8) + N'' KB'' end),
	''usage'' = (case status & 0x40 when 0x40 then ''log only'' else ''data only'' end)
	from [?]..sysfiles
	order by fileid'

Select * From #Temp

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[techipa]

Thanks George for your quick reply.

Looks like this is not the system procedure. I will have to create the SP first right. Am I correct?

-techiPA

 

[gmmastros]

Well... This isn't a stored procedure. You should be able to copy/paste this to a query analyzer window and run it. I tested this on sql 2000 and it ran fine. On 2005, it errors because of an int conversion problem. This one should work on both versions of sql server. I don't have any earlier versions to test this on, so if you are using sql 6.5 or 7, then this query may not work for you.

[COLOR=blue]Create[/color] 
[COLOR=blue]Table[/color]  #Temp
       ([COLOR=blue]Name[/color] [COLOR=blue]sysname[/color], 
       FileId [COLOR=blue]int[/color], 
       FileName [COLOR=blue]varchar[/color](1000), 
       FileGroup [COLOR=blue]VarChar[/color](100), 
       [COLOR=blue]Size[/color] [COLOR=blue]Varchar[/color](100), 
       MaxSize [COLOR=blue]Varchar[/color](100), 
       Growth [COLOR=blue]VarChar[/color](100), 
       [COLOR=blue]usage[/color] [COLOR=blue]varchar[/color](100))

[COLOR=blue]exec[/color] sp_msforeachdb [COLOR=red]'Insert Into #Temp Exec [?]..sp_helpfile'[/color]

[COLOR=blue]Select[/color] * [COLOR=blue]From[/color] #Temp

[COLOR=blue]Drop[/color] [COLOR=blue]Table[/color] #Temp

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[techipa]

George,

I am running the query on SQL 2000.

I get the following error:

Server: Msg 2812, Level 16, State 62, Line 12
Could not find stored procedure 'sp_msforeachdb'.

(0 row(s) affected)