Feb
23
Written by:
Steve Gray
2/23/2010 4:29 PM
Huh? Why would anyone do that?
Well, I do. What I’m doing today is searching every piece of code on a server and looking through it for lines that have ‘JOIN’ or ‘FROM’, that don’t have the ‘NOLOCK’ locking hint. In order to do that, I dump the SYSCOMMENTS table to a text file. But SYSCOMMENTS is a funny table, it isn’t broken up the way I need it to be, so I have to go through the fields character by character and look for Carriage Returns (deep breath)
Anyway, here’s code to look at one line of SYSCOMMENTS and dump it out, character by character, so I can look for the CRs
declare @text varchar(8000)
SELECT top 1 @text = [text]
FROM syscomments (nolock)
WHERE id = OBJECT_ID('vw_AppForPayment')
ORDER BY number, colid
print @text
declare @pos int
declare @char varchar(1)
declare @charToPrint varchar(5)
set @pos = 1
while @pos < len(@text) begin
set @char = substring(@text,@pos,1)
select @charToPrint = case when ascii(@char) = 13 then 'rtn'
when ascii(@char) = 9 then 'tab'
when ascii(@char) = 10 then 'LF'
else @char end
print convert(varchar(5),@pos) + char(9) + @charToPrint +
char(9) + convert(varchar(3),ascii(@char))
set @pos = @pos + 1
end