Following are the Extended Stored Procedures in SQL Server used to access the Windows file system.
This extended stored procedure can be used to get the current version of Microsoft SQL Server. To get the current SQL Server version
We can also get the version of the SQL server more easily using
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’
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’
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
This extended stored procedure returns the list of Windows NT groups and their description.
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
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.
This extended stored procedure returns the WINS name of the SQL Server that you’re connected to