Want to remove all trailng -- comments from my view definitions. I cannot get it to work. I get very different results depending on how the comment is formatted. Can someone please help me with the cte. I want to remove starting at -- up until the char(13) + char(10)

------comment

---      comment

CREATE VIEW [dbo].[v_test_columns]
AS
SELECT top 2 name AS [column name], --- comment
object_name(object_id) as [table name], --comment2   
max_length as [max length]
 FROM sys.columns

declare @definition varchar(max)
declare @select int
declare @from int
declare @columns varchar(max)

SELECT @definition = m.definition
    FROM sys.sql_modules m
    WHERE m.object_id = OBJECT_ID('v_test_columns', 'V');
		
--DECLARE @definition2 varchar(max) =  RTRIM(LTRIM(REPLACE(REPLACE(@Definition, CHAR(9), 'X'), CHAR(13) + CHAR(10), 'Y')))
--print @definition2

SET @select = CHARINDEX('select ', @definition);
SET @from = CHARINDEX('from ', @definition);
set @columns  = SUBSTRING(@definition, @select + 7, @from - @select - 7);	
	   
print @columns   

-- remove the first trailing dash comments
SET @columns = Left(@columns, CHARINDEX('--', @columns ) -1) + Right(@columns, len(@columns) - CHARINDEX(CHAR(13) + CHAR(10), @columns ) + 1)
--print @columns

-- Now use a recursive cte to find each of the matches within the ReportDefinition report definition and give a preview
;with T(columns1, start, pos) as (
select @columns,  cast(1 as int), charindex('--', @Columns)
union all
select @columns, cast(pos + 1 as int), charindex('--', @Columns, pos + 1)
from t
where pos > 0
)

SELECT  @columns = Left(@columns, CHARINDEX('--', @columns, pos)) + Right(@columns, len(@columns) - CHARINDEX(CHAR(13) + CHAR(10), @columns, pos ) ) 
 FROM T
 WHERE Pos > 0
 
print 'columns: ' + @columns 

• Moved by Lydia ZhangMicrosoft contingent staff, Moderator 5 hours 35 minutes ago more appropriate

Give this a go:

DECLARE @table TABLE (bigLongOlString VARCHAR(MAX))
INSERT INTO @table (bigLongOlString) VALUES
('CREATE VIEW someSuch
AS 
SELECT *
  FROM thatTableOverYonder --this is where we keep our stuff
 WHERE something = 1 --only get stuff were something is one
   AND smaller < bigger'),
('CREATE VIEW anotherOne
AS 
SELECT *
  FROM oerTheHills --and oer the main
 WHERE through = ''flanders'' --portugal and spain
   AND king_george_commands = ''obey''
   AND over = ''the hills'' --and far away')

;WITH base AS (
SELECT LEFT(value,COALESCE(NULLIF(CHARINDEX('--',value),0)-1,LEN(value))) AS cleanString, ID, bigLongOlString
  FROM @table
    CROSS APPLY splitterMkII(bigLongOlString,CHAR(13))
), rCTE AS (
SELECT cleanString, ID, bigLongOlString
  FROM base
 WHERE ID = 1
UNION ALL
SELECT r.cleanString + CHAR(13) + a.cleanString, a.ID, a.bigLongOlString
  FROM rCTE r
    INNER JOIN base a
	  ON r.ID + 1 = a.ID
	  AND r.bigLongOlString = a.bigLongOlString
)

SELECT LEFT(cleanString,COALESCE(NULLIF(CHARINDEX('--',cleanString),0)-1,LEN(cleanString))), bigLongOlString
  FROM rCTE r
 WHERE ID = (SELECT MAX(ID) FROM rCTE WHERE bigLongOlString = r.bigLongOlString)

Hi TheBrenda,

According to the code, you're trying to find out the select statement that defines a view, right?

Have no idea on why you have remove the comments, they do not affect when running the select statement.

declare @definition nvarchar(max) 

SELECT  @definition=SUBSTRING(m.definition,CHARINDEX('SELECT',m.definition),LEN(m.definition))
    FROM sys.sql_modules m
    WHERE m.object_id = OBJECT_ID('v_test_columns', 'V');
	select @definition

EXEC sp_executesql @definition

If the above is not what you want, you can find the recursive way to get the select statement with comments removed.

declare @definition nvarchar(max) 
declare @query nvarchar(max)

SELECT  @definition=SUBSTRING(m.definition,CHARINDEX('SELECT ',m.definition),LEN(m.definition))
    FROM sys.sql_modules m
    WHERE m.object_id = OBJECT_ID('v_test_columns', 'V'); 
	 

;WITH Cte AS
(
SELECT CHARINDEX('SELECT ',@definition) startPos, 
CASE WHEN CHARINDEX(CHAR(13),@definition)<>0 THEN CHARINDEX(CHAR(13),@definition) ELSE LEN(@definition) END endPos
UNION ALL
SELECT endPos+1,
CASE WHEN CHARINDEX(CHAR(13),@definition,endPos+1)<>0 THEN CHARINDEX(CHAR(13),@definition,endPos+1) ELSE LEN(@definition)+1 END endPos
FROM Cte
WHERE CHARINDEX(CHAR(13),@definition,endPos)<>0
)  
SELECT @query=(SELECT SUBSTRING(SUBSTRING(@definition,startPos,endPos-startPos),1,CASE WHEN CHARINDEX('-',SUBSTRING(@definition,startPos,endPos-startPos))<>0 THEN CHARINDEX('-',SUBSTRING(@definition,startPos,endPos-startPos))-1 ELSE LEN(@definition) END) FROM Cte
FOR XML PATH(''))
SELECT @query

EXEC sp_executesql @query
 

If you have any question, feel free to let me know.