Tuesday, November 29, 2005

SQL Server 2000 Undocumented Procedures For Files, Drives and Directories

Below are some undocumented SQL Server 2000 procedures for information about files, drives and directories.
You should not depend on this because they might me deprecated in the future, but if you want to find out fast if a file exists, what the size is and other things I think that they are good to use since it saves you some scripting or using terminal services

returns the network name of the server

Returns 1 if a file exists, 1 if the file is a directory and 1 if a parent directory exists
master..xp_fileexist 'C:\Program Files\Microsoft SQL Server\MSSQL\readme.txt'

Lists all the subdirecories of a given path including the level of the path
master..xp_dirtree 'C:\Program Files\Microsoft SQL Server\MSSQL\'

Lists a directory's immediate sub directory (1 level down)
master..xp_subdirs 'C:\Program Files\Microsoft SQL Server\MSSQL\'

List the file detals for a file, the following fields are returned
Alternate Name,Size,Creation Date, Creation, Time Last Written, Date Last Written, Time Last Accessed,
Date Last Accessed Time and Attributes
master..xp_getfiledetails 'C:\Program Files\Microsoft SQL Server\MSSQL\readme.txt'

List all the fixed drives and free MB on the machine

Returns the database name, size and space used for the temp db

Lists all the ODBC datasources (name and description) available on the machine

List the current error logs (Archive #, Date and Log File Size) on the server


Tito Maury said...

Great blog. Found your blog while searching for more information at yahoo about this post . Your blog has quite a lot of interesting thoughts. Keep up the good work, SQL Server 2000 Undocumented Procedures For Files, Drives and Directories


sql server programming

SQL said...


I am glad I provided some helpful info to you

Anonymous said...

The undocumented stored procedures are very useful and provide quite valuable functionality. But they come with a cost. As they are undocumented they can be discontinued any time. And this has happened in SQL Server 2005. Many undocumented as well as documented extended storedd procs have been discontinued for security reasons. And the worst part is there are no replacement with a more secure one. So we end up rewriting legacy apps to make them work the same without all those stored procs.