Find the Physical Files from sysaltfiles

Volume 1 Number 49 October 21, 2003

by Andrew Novick

Check out the UDF Frequently Asked Questions at: http://www.novicksoftware.com/UdfOfWeek/UDF_FAQ.htm

To ask a UDF related question or to contribute a UDF, send e-mail to udf@NovickSoftware.com

Last week I promised to share the results of the e-mail survey and you'll find them near the bottom of this newsletter after the UDF.

I've already started applying some changes to the text format of the newsletter that should make it easier to read in Outlook 2003. I hope they don't make it any harder to read in the e-mail program that you're using. If they do, please drop me an e-mail at udf@novicksoftware.com

Now on to the UDF.....

I get the condensed version of several of the Yahoo groups that discuss SQL Server. I find the condensed version the only reasonable to deal with them at all way because receiving individual messages just floods your inbox. In any case, one of the questions was, "How do I get the list of physical file names for my database?"

It turns out to not be too difficult. The requested information is in both the database's sysfiles table and the instances master..sysaltfiles table. I chose to use master..sysaltfiles as the basis of this function because I wanted to be able to see all the files. Here's the CREATE FUNCTION script:

/------- Start copying below this line -\

SET QUOTED_IDENTIFIER ON --
SET ANSI_NULLS ON --
GO

CREATE  FUNCTION dbo.udf_Instance_FilesTAB (

    @DBID int = NULL -- dbid requested or NULL for all
)   RETURNS TABLE
    -- NO SCHEMABINDING due to use of system tables
/*
* Returns a table of the database files based on information
* from master..sysaltfiles.  It can retrieve the files for a
* single database or for all databases (@DBID=NULL).
*
* Example
select * FROM dbo.udf_Instance_FilesTAB (null)
*
* Related Func: udf_DB_FilesTAB, which uses sysfiles.
*
* History:
* When          Who     Description
* ------------- ------- -----------------------------------------
* 2003-10-14    ASN     Initial Coding
*
* © Copyright 2003 Andrew Novick http://www.NovickSoftware.com
* You may use this function in any of your SQL Server databases
* including databases that you sell, so long as they contain
* other unrelated database objects. You may not publish this
* UDF either in print or electronically.
* Published in the T-SQL UDF of the Week Newsletter Vol 1 #49
http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm
****************************************************************/
AS RETURN

SELECT TOP 100 PERCENT WITH TIES
        DB_NAME(DBID) AS [DB]
      , dbid
      , [name] as [LogicalName]
      , fileid
      , [filename]
      , groupid
      , CAST(CAST([size] as float)*8.0/1024.0 as numeric(18,3))
              as [SizeMB]
    FROM master..sysaltfiles
    WHERE (@DBID IS NULL OR dbid = @DBID)
    ORDER BY dbid -- For when @DBID is null
           , fileid -- Within database



GO

GRANT SELECT on dbo.udf_Instance_FilesTAB to PUBLIC
GO

\-------Stop copying above this line ----/

To test the UDF, All that's necessary is to select the desired columns from the UDF. This first query requests information from all the files in the database:

/------- Start copying below this line --\

-- Request all files
SELECT DB, LogicalName, SizeMB, [Filename]
   FROM udf_Instance_FilesTAB(NULL)
GO

\-------Stop copying above this line ----/
(Results)
DB         LogicalName    SizeMB  Filename
---------- -------------- ------- ------------------------------
master     master          15.375 C:\S2K2\MSSQL\data\master.mdf
master     mastlog          2.250 C:\S2K2\MSSQL\data\mastlog.ldf
tempdb     tempdev        200.000 C:\S2K2\MSSQL\data\tempdb.mdf
tempdb     templog        500.000 C:\S2K2\MSSQL\data\templog.ldf
model      modeldev          .625 C:\S2K2\MSSQL\data\model.mdf
model      modellog          .750 C:\S2K2\MSSQL\data\modellog.ldf
msdb       MSDBData        21.500 C:\S2K2\MSSQL\data\msdbdata.mdf
msdb       MSDBLog          2.250 C:\S2K2\MSSQL\data\msdblog.ldf
pubs       pubs             1.250 C:\S2K2\MSSQL\data\pubs.mdf
pubs       pubs_log          .750 C:\S2K2\MSSQL\data\pubs_log.ldf
Northwind  Northwind        2.938 C:\S2K2\MSSQL\data\northwnd.mdf
Northwind  Northwind_log    1.000 C:\S2K2\MSSQL\data\northwnd.ldf
UDFofWeek  UDFofWeek_Data   1.000 C:\UDFofWeek\UDFofWeek_Data.MDF
UDFofWeek  UDFofWeek_Log    1.000 C:\UDFofWeek\UDFofWeek_Log.LDF
Scratch    Scratch_Data     9.375 C:\S2K2\MSSQL\data\Scratch_Data.MDF
Scratch    Scratch_Log     23.813 C:\S2K2\MSSQL\data\Scratch_Log.LDF
Scratch    Scratch_data2   25.375 C:\S2K2\MSSQL\data\Scratch_data2_Data.NDF

(End of results)

The last database, scratch, has multiple data files.

Requesting the files for a single database requires that you supply the database ID, which can be obtained from the DB_ID() built-in function.


/------- Start copying below this line --\

-- Request files from the current database
DECLARE @DBID int
SET @DBID = DB_ID()
SELECT DB, LogicalName, SizeMB, [Filename]
    FROM udf_Instance_FilesTAB(@DBID)
GO

\-------Stop copying above this line ----/
(Results)
UDFofWeek  UDFofWeek_Data   1.000 C:\UDFofWeek\UDFofWeek_Data.MDF
UDFofWeek  UDFofWeek_Log    1.000 C:\UDFofWeek\UDFofWeek_Log.LDF

(End of results)

A couple of the columns that you'll find in master..sysaltfiles but won't find in udf_Instance_FilesTAB are growth and maxsize. I attempted to put them in based on the documentation in the Books Online, but there seems to be a problem. The documentation doesn't match what happens with sysaltfiles, although it does match what happens with sysfiles. A search of web resources didn't turn up the answer. Rather than including potentially incorrect information, I've left these two columns out of the function.

That's the end of the article on the UDF of the Week.

The rest of the newsletter concerns the e-mail survey. The survey got 85 responses and I'd like to thank everyone who responded. Based on your feedback I'm looking into creating a HTML version.

Here's what you said:

What e-mail program do you use?

- Outlook 98, 2000, or 2002(xp)                  50   59.5%
- Outlook 2003                                   12   14.3%
- Outlook Express                                 4    4.8%
- Eudora                                          0    0.0%
- Netscape Mail                                   1    1.2%
- AOL                                             0    0.0%
- Other                                          17   20.2%

When do you expect to be using Outlook 2003?

- Within a month                                  7    8.5%
- Within 6 months                                15   18.3%
- Within a Year                                  24   29.3%
- Several years from now                         11   13.4%
- Never                                          13   15.9%
- Other                                          12   14.6%

What way would you like to receive the newsletter?

- Text. The way it is now                        39   45.9%
- HTML. Message contents in a web page           36   42.4%
- Text message with just a link to a web page.    9   10.6%
- Other                                           1    1.2%

Are you happy with the format of the newsletter?

- Happy                                          36   42.4%
- It's okay                                      46   54.1%
- Just fair                                       2    2.4%
- Not so happy                                    1    1.2%

What speed is your connection to the internet?

- T1 or higher                                   42   49.4%
- Cable modem or DSL                             34   40.0%
- Dialup                                          6    7.1%
- Other                                           3    3.5%

Do you use an RSS feed reader?

- No                                             72   84.7%
- Yes                                            13   15.3%

Do you have a great UDF that you'd like to share?  Or maybe you
have a T-SQL problem that you think could be solved by a UDF
but you don't know how? Send them to:

UDF@NovickSoftware.com 

and they might be published in this newsletter.  I try and 
respond to every request that I get.  

Thanks,  
Andrew Novick
+--------------------------------------------------------------+

RSS as HTML

Personal Blog

New Tips:

Loading SQL Profiler trace (.trc) files with fn_trace_gettrace

Upcoming
Presentations:

SSIS
SQL Server
Integration
Services
March 4
2010
NEVB

SQL Azure
Code Camp
March 27
2010

Full Schedule

SQL Server T-SQL User-Defined Function of the Week

Find the Physical Files from sysaltfiles

New Tips:

Upcoming Presentations:

Upcoming
Presentations: