CREATE OR ALTER FUNCTION dbo.F_META_PURGE_CODE (@SQL NVARCHAR(max)) RETURNS NVARCHAR(max) WITH RETURNS NULL ON NULL INPUT AS /****************************************************************************** * MODULE : META * * NATURE : SCALAR_FUNCTION * * OBJECT : dbo.F_META_PURGE_CODE * * CREATE : 2018-05-28 * * VERSION : 1 * ******************************************************************************* * Frédéric BROUARD - alias SQLpro - SARL SQL SPOT - SQLpro@sqlspot.com * * Architecte de données : expertise, audit, conseil, formation, modélisation * * tuning, sur les SGBD Relationnels, le langage SQL, MS SQL Server/PostGreSQL * * blog: http://mssqlserver.fr website: http://sqlpro.developpez.com * * Most Valuable Professional - MVP - SQL Server (data platform) depuis 2004 * ******************************************************************************* * PURPOSE : purges Transact SQL code of comments and unprintable characters * ******************************************************************************* * INPUTS : * * @SQL NVARCHAR(max) : the Transact SQL code to be purged * ******************************************************************************* * OUPUT : VARCHAR(max) * ******************************************************************************* * RUNNABLE : version 2000 and superior * ******************************************************************************* * SAMPLE : * * SELECT object_id, definition, * * dbo.F_META_PURGE_CODE(definition) * * FROM sys.sql_modules; * ******************************************************************************/ BEGIN IF @SQL = N'' -- on empty string return empty string RETURN N''; DECLARE @NC1 NCHAR(1), -- char to be treated @NC2 NCHAR(2), -- 2 chars to be treated @I INT, -- pos of char to be treated @OUT NVARCHAR(max), -- returned string @TT BIT, -- 1 if comment as "--" @SE INT, -- n if comment as "/* */" (can be nested !) @CC BIT; -- 1 if in as string -- first assignements SELECT @OUT = N'', -- output is empty string to begin @I = 1, -- position of first char @TT = 0, -- on the beginning, no comment "--" @SE = 0, -- on the beginning, no comment "/*" @CC = 0; -- on the beginning, no string" -- loop while there is a char to treat WHILE @I <= LEN(@SQL) BEGIN -- getting the chars SELECT @NC2 = SUBSTRING(@SQL, @I, 2), -- get the 2 chars at position I @NC1 = SUBSTRING(@SQL, @I, 1); -- get the char at poistion I -- this is the beginning or the ending of a string (not in comment) -- so we reverse the position of the bit @CC IF @NC1 = '''' AND @TT = 0 AND @SE = 0 IF @CC = 1 SET @CC = 0 ELSE SET @CC = 1; -- we are in a string, add the char in the output and loop again IF @CC = 1 BEGIN SET @OUT = @OUT + @NC1 SET @I = @I + 1; CONTINUE; END -- we are not in a string neither in a comment, and the next 2 chars are -- not a comment's beginning. Add the char in the output and loop again IF @TT = 0 AND @SE = 0 AND @CC = 0 AND @NC2 NOT IN (N'--', '/*') BEGIN SET @OUT = @OUT + @NC1 SET @I = @I + 1; CONTINUE; END -- we are not in "--" comment and the next 2 chars are a "/*" comment -- increments "/*" counter and pass 2 chars IF @TT = 0 AND @NC2 = N'/*' BEGIN SELECT @SE = @SE + 1, @I = @I + 2; CONTINUE; END; -- we are not in "/*" comment and the next 2 chars are a "--" comment -- set "--" bit to 1 and pass 2 chars IF @TT = 0 AND @SE = 0 AND @NC2 = N'--' BEGIN SELECT @TT = 1, @I = @I + 2; CONTINUE; END; -- we are in "/*" comment and the next 2 chars are a "*/" comment end -- decrease the "/*" counter and pass 2 chars IF @TT = 0 AND @SE > 0 AND @NC2 = N'*/' BEGIN SET @SE = @SE - 1; SET @I = @I + 2; CONTINUE; END; -- we are in "--" comment and the next 1 or 2 chars are a "--" comment end -- set "--" bit to 1 and pass 1 or 2 chars IF @TT = 1 AND @SE = 0 AND @NC1 IN (NCHAR(13), NCHAR(10)) BEGIN SET @TT = 0 IF @NC2 = NCHAR(13) + NCHAR(10) SET @I = @I + 2 ELSE SET @I = @I + 1 CONTINUE; END; -- in all other conditions pass one char SET @I = @I + 1; END; -- clear the string for remaining ends of line or tab SET @OUT = REPLACE(REPLACE(REPLACE(@OUT, NCHAR(9), ' '), NCHAR(10), ' '), NCHAR(13), ' '); -- clear the string of duplicate spaces except in a string (' ') or in a bracketed SQL identifier DECLARE @KK BIT = 0, -- identifies if we are in a SQL identifier with brackets [...] @EE BIT = 0; -- identifies if we are in a string ' ... ' -- initializing SELECT @SQL = @OUT, @I = 0, @CC = 0, @OUT = N''; -- loop over the chars WHILE @I <= LEN(@SQL) BEGIN SET @I = @I + 1; SET @NC1 = SUBSTRING(@SQL, @I, 1); -- beginning of an identifier with brackets IF @NC1 = '[' AND @KK = 0 AND @CC = 0 BEGIN SELECT @KK = 1, @EE = 0; SET @OUT = @OUT + @NC1 CONTINUE; END; -- ending of an identifier with brackets IF @NC1 = ']' AND @KK = 1 BEGIN SELECT @KK = 0, @EE = 0; SET @OUT = @OUT + @NC1 CONTINUE; END; -- beginning of a string with simple quote IF @NC1 = '''' AND @KK = 0 AND @CC = 0 BEGIN SELECT @CC = 1, @EE = 0; SET @OUT = @OUT + @NC1 CONTINUE; END; -- ending of a string with simple quote IF @NC1 = '''' AND @CC = 1 BEGIN SELECT @CC = 0, @EE = 0; SET @OUT = @OUT + @NC1 CONTINUE; END; -- firts space we keep it always IF @NC1 = ' ' AND @EE = 0 BEGIN SET @OUT = @OUT + @NC1 SET @EE = 1; CONTINUE END -- space following a space, in bracketed identifier or string IF @NC1 = ' ' AND @EE = 1 AND (@KK = 1 OR @CC = 1) BEGIN SET @OUT = @OUT + @NC1 CONTINUE END -- space following a space, outside bracketed identifier or string IF @NC1 = ' ' AND @EE = 1 AND @KK = 0 AND @CC = 0 BEGIN CONTINUE END -- no more spaces IF @NC1 <> ' ' AND @EE = 1 SET @EE = 0; SET @OUT = @OUT + @NC1; END; -- trim both the string RETURN RTRIM(LTRIM(@OUT)); END GO