Extended Stored Procedures

Stored procedures to retrieve directory listing

You can use xp_subdirs and xp_dirtree undocumented stored procedures to retrieve a list of child directories under a specified parent directory from file system.

EXEC master..xp_subdirs 'C:\\Inetpub'
xp_subdirs – lists only directories which are direct children of the specified parent. In this case it returns directories which are directly under 'C:\Inetpub' as below:

subdirectory

—————————

AdminScripts

custerr

history

logs

temp

wwwroot

(6 row(s) affected)
If you need to retrieve the children recursively, you will need to use xp_dirtree.

EXEC master..xp_dirtree 'C:\\Program Files\\Microsoft.NET'
This returns all the children of 'C:\Program Files\Microsoft.NET' recursively, and their level from parent:

subdirectory                depth

——————–          ———–

ADOMD.NET                         1

100                               2

Resources                         3

1033                              4

110                               2

Resources                         3

1033                              4

Primary Interop Assemblies        1

RedistList                        1

(9 row(s) affected)
You can also restrict the number of level retrieved using:

EXEC master..xp_dirtree 'C:\\Program Files\\Microsoft.NET', 2
This will only retrieve children of 'C:\Program Files\Microsoft.NET' whose level <= 2
subdirectory               depth
——————–             ———–
ADOMD.NET                         1
100                               2
110                               2
Primary Interop Assemblies        1
RedistList                        1
(5 row(s) affected)
Note: These procedures are undocumented and unsupported by Microsoft and they may *disappear* from newer SQL Server versions!


How To Check SQL Server Version:

The most common way to check SQL Server is to use http://msdn.microsoft.com/en-us/library/ms177512.aspx configuration function. It returns version, architecture, OS version and build date for current instance.

SELECT @@VERSION AS [Version]
Version
—————————————————————————————————————————————————————————————————————————————————————-
Microsoft SQL Server 2008 R2 (RTM) – 10.50.1600.1 (Intel X86)
       Apr  2 2010 15:53:02
       Copyright (c) Microsoft Corporation
       Enterprise Edition on Windows NT 6.0 <X86> (Build 6002: Service Pack 2)
(1 row(s) affected)
Another way is to use SERVERPROPERTY() metadata function. For a full list of properties that can be returned, check BOL.

SELECT SERVERPROPERTY('ProductVersion') AS [Version]
Version
—————————————————————————————————————————————————————————————————————————————————————-
10.50.1600.1
(1 row(s) affected)

You can also use extended stored procedures to check SQL Server version:

sp_MSgetversion:

EXEC master..sp_MSgetversion
Character_Value
——————– ———– ———–
10.50.1600.1         1           3
(1 row(s) affected)

xp_msver:

EXEC master..xp_msver 'ProductVersion'
Index  Name                             Internal_Value Character_Value
—— ——————————– ————– ————————————————————————————————————————————————————————————————
2      ProductVersion                   655410         10.50.1600.1
(1 row(s) affected)
To see the full list of properties returned by xp_msver, execute it without any arguments.

EXEC master..xp_msver

Index  Name                             Internal_Value Character_Value
—— ——————————– ————– ————————————————————————————————————————————————————————————————
1      ProductName                      NULL           Microsoft SQL Server

2      ProductVersion                   655410         10.50.1600.1

3      Language                         1033           English (United States)

4      Platform                         NULL           NT INTEL X86

5      Comments                         NULL           SQL

6      CompanyName                      NULL           Microsoft Corporation

7      FileDescription                  NULL           SQL Server Windows NT

8      FileVersion                      NULL           2009.0100.1600.01 ((KJ_RTM).100402-1540 )

9      InternalName                     NULL           SQLSERVR

10     LegalCopyright                   NULL           Microsoft Corp. All rights reserved.

11     LegalTrademarks                  NULL           Microsoft SQL Server is a registered trademark of Microsoft Corporation.

12     OriginalFilename                 NULL           SQLSERVR.EXE

13     PrivateBuild                     NULL           NULL

14     SpecialBuild                     104857601      NULL

15     WindowsVersion                   393347078      6.0 (6002)

16     ProcessorCount                   2              2

17     ProcessorActiveMask              3              00000003

18     ProcessorType                    586            PROCESSOR_INTEL_PENTIUM

19     PhysicalMemory                   2041           2041 (2140626944)

20     Product ID                       NULL           NULL
(20 row(s) affected)


xp_instance_regread:
DECLARE @returnValue NVARCHAR(100)
EXEC   master..xp_instance_regread
       @rootkey              = N'HKEY_LOCAL_MACHINE',
       @key                     = N'SOFTWARE\Microsoft\MSSQLServer\Setup',
       @value_name       = N'Version',
       @value                  = @returnValue output
SELECT @returnValue AS [Version]

Version
—————————————————————————————————-
10.50.1600.1
(1 row(s) affected)

xp_fixeddrives is a useful extended stored procedure which returns amount of free space available in MB, for all local hard drives.
EXEC master..xp_fixeddrives
drive MB free
—– ———–
C     91884
S     538
(2 row(s) affected)
It can be only used to check for free space on local drives, it won’t check mapped drives.

Another useful extended stored procedure is xp_fileexist, which can be used to check the existence of a file on file system.
EXEC master..xp_fileexist 'C:\Windows\Explorer.exe'
File Exists File is a Directory Parent Directory Exists
———– ——————- ———————–
1           0                   1
(1 row(s) affected)

xp_fileexist can also be used to check existence of a directory:
EXEC master..xp_fileexist 'C:\Windows\System32'
File Exists File is a Directory Parent Directory Exists
———– ——————- ———————–
0           1                   1
(1 row(s) affected)
It also checks if the Parent Directory exists.

Accessing Registry :

The following code will add a new entry in startup programs list in registry:
EXEC master..xp_regwrite     @rootkey     = 'HKEY_LOCAL_MACHINE',     @key         = 'Software\Microsoft\Windows\CurrentVersion\Run',     @value_name  = 'Solitaire',     @type        = 'REG_SZ',     @value       = 'C:\Program Files\Microsoft Games\Solitaire\Solitaire.exe'
Or you can also use xp_regread or xp_instance_regread to verify the entry.

You can delete an registry entry using xp_regdelete.

EXEC master..xp_regdeletevalue     @rootkey     = 'HKEY_LOCAL_MACHINE',     @key         = 'Software\Microsoft\Windows\CurrentVersion\Run',     @value_name  = 'Solitaire'

To read instance specific registry entries from registry you can use xp_instance_regenumvalues and xp_instance_regread.

DECLARE @returnValue NVARCHAR(500)

EXEC   master..xp_instance_regread

       @rootkey      = N'HKEY_LOCAL_MACHINE',

       @key          = N'SOFTWARE\Microsoft\MSSQLServer\Setup',

       @value_name   = N'SQLDataRoot',

       @value        = @returnValue output

PRINT @returnValue

DECLARE @returnValue NVARCHAR(100)
EXEC   master.dbo.xp_regread
       @rootkey      = N'HKEY_LOCAL_MACHINE',
       @key          = N'SOFTWARE\\Microsoft\Windows\\CurrentVersion',
       @value_name   = N'ProgramFilesDir',
       @value        = @returnValue output
SELECT @returnValue

xp_regread ca only be used to retrieve a single value. If you need to retrieve multiple values, you will need to use xp_instance_regenumvalues, which returns all values under a specified key.

For example, To retrieve a list of start-up programs from registry we will use:

EXEC   master..xp_instance_regenumvalues
       @rootkey = N'HKEY_LOCAL_MACHINE',
       @key     = N'SOFTWARE\\Microsoft\\Windows\\CurrentVersion\\Run'

this will return all entries under HKLM\Software\Microsoft\Windows\CurrentVersion\Run: