Posted by: R Manimaran | October 20, 2010

Useful SQL server System Stored Procedures to Access Windows File System

Following are the Extended Stored Procedures in SQL Server used to access the Windows file system.

· sp_MSgetVersion

This extended stored procedure can be used to get the current version of Microsoft SQL Server. To get the current SQL Server version

EXEC Master..sp_MSgetVersion

Output:

We can also get the version of the SQL server more easily using

SELECT @@Version

Output:

· xp_dirtree

This extended stored procedure is used to get the list of folders for the folder named in the xp

EXEC master..xp_dirtree ‘C:Program Files’

Output:

· Xp_subdirs

This extended stored procedure is used to get the list of folders for the folder named in the xp. In comparison with xp_dirtree, xp_subdirs returns only those directories whose depth = 1

EXEC master..xp_subdirs ‘C:Program Files’

Output:

· xp_enum_oledb_providers

This extended stored procedure is used to list of all the available OLE DB providers. It returns Provider Name, Parse Name and Provider Description. To get a list of all OLE DB providers for your SQL Server, run

EXEC master..xp_enum_oledb_providers

Output:

· xp_enumgroups
This extended stored procedure returns the list of Windows NT groups and their description.

EXEC master..xp_enumgroups

Output:

· xp_fileexist

You can use this extended stored procedure to determine whether a particular file exists on the disk or not.

EXEC master..xp_fileexist ‘C:12Hive.bat’

Here I have the file 12Hive.bat. So FileExists is 1. It is not a directory so 0. Here C: drive is exists, so 1.

If I change the file path as ‘D:12Hive1.bat’ here I did not have a drive like d drive. So the Output will be

· xp_fixeddrives

This very useful extended stored procedure returns the list of all hard drives and the amount of free space in Mb for each hard drive.

EXEC master..xp_fixeddrives

Output:

· xp_getnetname

This extended stored procedure returns the WINS name of the SQL Server that you’re connected to

EXEC master..xp_getnetname

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: