query to see unused space in any data file

Id like to see how much of unused space is there for any database data file
as in EM ? How can i do so using TSQL ?1. Use the taskpad view for the database
2. DBCC SHOWFILESTATS
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23j61al7gDHA.3144@.tk2msftngp13.phx.gbl...
> Id like to see how much of unused space is there for any database data
file
> as in EM ? How can i do so using TSQL ?
>|||Create following stored procedure to get the free space
left in the database.
Usage of the foll. procedure would be as
exec proc_free_space 'northwind' --pass db name as
parameter
--script of the procedure.
create proc proc_free_space @.db sysname
as
create table #temp2
(fileid int,filegroup int,totalextents int,usedextents
int,name sysname,filename sysname)
insert into #temp2 exec ('use ' + @.db + ';dbcc
showfilestats')
select 'Data Files :' [SPACE ALLOCATED] , '' [TOTAL
(M)], '' [USED (M)], '' [FREE M)]
union all
select reverse(substring(reverse(filename),1,
charindex('\', reverse(filename)) - 1)) [FILENAME],
str(cast([used (m)]as decimal(10,2)) + cast([free (m)] as
decimal(10,2)),10,2) 'Total (M)', [Used (M)] , [Free (M)]
from (select filename, str(usedextents * cast(64 as decimal
(8,2))/1024, 10,2) 'Used (M)',str((totalextents * cast(64
as decimal(8,2))/1024) - (usedextents * cast(64 as decimal
(8,2))/1024) , 10,2) 'Free (M)'
from
#temp2)a
go
--procedure ends here
To get the free space for each database use system stored
procedure sp_msforeachdb and pass the name of above stored
procedure.
Ex:
EXEC sp_Msforeachdb "proc_free_space '?'"
- Vishal|||Set up a profiler trace to capture what EM is doing and you will see the
TSQL. You might even see some undocumented commands.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:#j61al7gDHA.3144@.tk2msftngp13.phx.gbl...
> Id like to see how much of unused space is there for any database data
file
> as in EM ? How can i do so using TSQL ?
>