Get Available free space for data file

When I go to Shrink a file, the screen shows me the currently allocated and
the free space available for the selected database. Is there a view or
system stored procedure that I can use to retrieve this information?
I am working with SQL 2005. I have been playing with the sp_spaceused
stored procedure but can't seem to get just the data file info.
Thanks,Hello,
You could use DBCC SHOWFILESTATS. Run the the below TSQL from the database
you need info.
create table #tmpspc (Fileid int, FileGroup int, TotalExtents int,
UsedExtents int, Name sysname, FileName nchar(520))
Insert #tmpspc EXEC ('dbcc showfilestats')
SELECT
s.name AS [Name],
s.physical_name AS [FileName],
s.size * CONVERT(float,8) AS [Size],
CAST(tspc.UsedExtents*convert(float,64) AS float) AS [UsedSpace],
s.file_id AS [ID]
FROM
sys.filegroups AS g
INNER JOIN sys.master_files AS s ON (s.type = 0 and s.database_id = db_id()
and (s.drop_lsn IS NULL)) AND (s.data_space_id=g.data_space_id)
INNER JOIN #tmpspc tspc ON tspc.Fileid = s.file_id
WHERE
(CAST(cast(g.name as varbinary(256)) AS sysname)=N'PRIMARY')
ORDER BY
[ID] ASC
Thanks
Hari
"Tim Kelley" wrote:

> When I go to Shrink a file, the screen shows me the currently allocated an
d
> the free space available for the selected database. Is there a view or
> system stored procedure that I can use to retrieve this information?
> I am working with SQL 2005. I have been playing with the sp_spaceused
> stored procedure but can't seem to get just the data file info.
> Thanks,
>
>|||Will this work with SQL 2000 also?
Thanks,
"Hari Prasad" <HariPrasad@.discussions.microsoft.com> wrote in message
news:7F25723E-2D5C-4C9B-AC1A-16254059521A@.microsoft.com...[vbcol=seagreen]
> Hello,
> You could use DBCC SHOWFILESTATS. Run the the below TSQL from the database
> you need info.
> create table #tmpspc (Fileid int, FileGroup int, TotalExtents int,
> UsedExtents int, Name sysname, FileName nchar(520))
> Insert #tmpspc EXEC ('dbcc showfilestats')
>
> SELECT
> s.name AS [Name],
> s.physical_name AS [FileName],
> s.size * CONVERT(float,8) AS [Size],
> CAST(tspc.UsedExtents*convert(float,64) AS float) AS [UsedSpace],
> s.file_id AS [ID]
> FROM
> sys.filegroups AS g
> INNER JOIN sys.master_files AS s ON (s.type = 0 and s.database_id =
> db_id()
> and (s.drop_lsn IS NULL)) AND (s.data_space_id=g.data_space_id)
> INNER JOIN #tmpspc tspc ON tspc.Fileid = s.file_id
> WHERE
> (CAST(cast(g.name as varbinary(256)) AS sysname)=N'PRIMARY')
> ORDER BY
> [ID] ASC
> Thanks
> Hari
> "Tim Kelley" wrote:
>|||No.
For SQL 2000; just enable the profiler and open the shrink file window from
enterprise manager. This will give the commands which executes
from backend.
Thanks
Hari
"Tim Kelley" <tkelley@.company.com> wrote in message
news:ex6Mn6qLHHA.4992@.TK2MSFTNGP04.phx.gbl...
> Will this work with SQL 2000 also?
> Thanks,
>
> "Hari Prasad" <HariPrasad@.discussions.microsoft.com> wrote in message
> news:7F25723E-2D5C-4C9B-AC1A-16254059521A@.microsoft.com...
>