IF EXISTS ( SELECT * 
            FROM   sysobjects 
            WHERE  id = object_id(N'dbo.CP_Serial') 
                   and OBJECTPROPERTY(id, N'IsProcedure') = 1 )
BEGIN
    DROP PROCEDURE [dbo].[CP_Serial]
END
GO




CREATE PROCEDURE [dbo].[CP_Serial]
AS
BEGIN	


IF (SELECT ISNULL(CHARINDEX('linux', @@VERSION),0)  ) = 0    -- en linux no anda
	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
ELSE
	BEGIN
		SELECT @@SERVERNAME
	END

END

GO


