|
SQL Server T-SQL User-Defined Function of the WeekFind the Physical Files from sysaltfilesVolume 1 Number 49 October 21, 2003by 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 Sign up for this newsletter at: http://www.novicksoftware.com/UdfOfWeek/UDFofWeekSignup.htm 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 +--------------------------------------------------------------+
|
|
