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,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...
> 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
>> 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,
>>|||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...
>> 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
>> 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,
>>
>