SQL SERVER – Enable xp_cmdshell using sp_configure

reference : 

http://sqltidbits.com/scripts/check-if-xpcmdshell-enabled-across-multiple-servers

http://blog.sqlauthority.com/2007/04/26/sql-server-enable-xp_cmdshell-using-sp_configure/

http://www.maxxxie.net/2012/07/08/xp_cmdshell-some-more-evil/

1. How to enable & check enable status of  xp_cmdshell command 

--- To allow advanced options to be changed.

EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO

--To update the currently configured value for this feature.
RECONFIGURE
GO

---to Check the enabled status 
SELECT name AS [Configuration], CONVERT(INT, ISNULL(value, value_in_use)) AS [IsEnabled]
FROM  master.sys.configurations

WHERE  name = 'xp_cmdshell'

2.Sample to use xp_cmdshell  to list down file in local system : 

CREATE TABLE ##SQLFiles ( SQLFileName VARCHAR(2000))
GO

INSERT INTO ##SQLFiles
EXECUTE master.dbo.xp_cmdshell 'dir /b "D:\Working\===onGoing\==Change Request\==Oct 7 release\File Import\DB\==SP\*.sql"'
GO

DECLARE cFiles CURSOR LOCAL FOR
    SELECT DISTINCT [SQLFileName]
    FROM ##SQLFiles
    WHERE [SQLFileName] IS NOT NULL AND
          [SQLFileName] != 'NULL'
    ORDER BY [SQLFileName]

DECLARE @vFileName         VARCHAR(200)

OPEN cFiles
FETCH NEXT FROM cFiles INTO @vFileName
WHILE @@FETCH_STATUS = 0
BEGIN
     PRINT ' @vFileName ' + @vFileName
     FETCH NEXT FROM cFiles INTO @vFileName
END

CLOSE cFiles
DEALLOCATE cFiles
GO


DROP TABLE ##SQLFiles
GO