» Fig Leaf Software Home

We've Got You Covered.

Monday, June 1, 2009

T-SQL - File Exists?

SQL Server 2000 and 2005 both provide a nice extended stored procedure whereby you can check for the existence of a file in your T-SQL. Why would this be useful, you ask yourself.

Earlier today I was tidying up some scripts that are part of a large, multi-database merge project I am working on. I set out to automate this as much as possible as it will need to be performed numerous times as data refreshes are needed and we move closer to transitioning from 6 groups of databases (49 in total) to the new merged set of databases (6 in total).

The very first set of steps in the process consists of restoring databases from full backups that are pushed to SQL Server 2005 Cluster. The database backup files can take some time making their way from the origin server to the file system on the SQL cluster. At some point, the process of running the merge routines will become automated and scheduled, and I now have an easy way (right within T-SQL) to check if the files are present before attempting to use them.

xp_fileExist OUT

The extended stored procedure can be used in one of two ways. If an output parameter is specified (OUT), the value returned will either be 0 (zero) if the file does not exist or 1 (one) if the file does exist.

If no ouput parameter is specified (OUT), the value returned is a table containing three columns, each of type INT: [File Exists] , [File is a Directory] , [Parent Directory Exists].

File Exists - 1 Yes, 0 No
File is a Directory - 1 Yes, 0 No
Parent Directory Exists - 1 Yes, 0 No

I have a temporary table that is populated with various settings and values for each group of databases being merged together. I simply select the names of the database backup files from my temporary table and use a cursor to loop over the recordset. Inside this loop is where I make use of xp_fileExist. I set a flag in my temporary table when the file does exist and proceed with the next step.

I realize that while I may be able to determine if the file exists, directory exists, or parent exists, I am not able to easily obtain a size value for the file. I will address that in a later post. For now, give the example code below a whirl and see if you have a need in your T-SQL code for such a procedure.

example 1:

SET NOCOUNT ON

CREATE TABLE #files
(
file_exists INT
,is_directory INT
,parent_exists INT
)

INSERT INTO #files
EXEC xp_fileExist 'C:\temp'

SELECT
file_exists
,is_directory
,parent_exists
FROM
#files

DROP TABLE #files

SET NOCOUNT OFF

No comments:

Post a Comment

About Us

Fig Leaf Software is an award-winning team of imaginative designers, innovative developers, experienced instructors, and insightful strategists.

For over 20 years, we’ve helped a diverse range of clients...

Read More

Contact Us

202-797-7711

Fig Leaf Software

1400 16th Street NW
Suite 450
Washington, DC 20036

info@figleaf.com