CREATE PROCEDURE CP_Serial
AS
BEGIN	
	
DECLARE @tbl TABLE(dir_lines VARCHAR(200))
DECLARE @Cmd VARCHAR(2000)

SET @cmd = 'dir "' + (SELECT NAME FROM sys.dm_os_loaded_modules WHERE DESCRIPTION like 'SQL Server%' AND NAME LIKE '%sqlservr.exe') + '"'
INSERT INTO @tbl
EXEC xp_cmdshell @cmd
SELECT RIGHT(LTRIM(RTRIM(SUBSTRING(REPLACE(dir_lines,'-',''), CHARINDEX(':',dir_lines)+1,LEN(dir_lines)))),8) AS Serial 
	FROM @tbl WHERE dir_lines like '%volume%serial%' or dir_lines like '%serie%volume%'

END


