Thanks for taking the time to develop and share this code. Very useful.
I have run into an issue that has me stumped. When I run the script against a database that has several large stored procedures the script errors out with errors like these:
Msg 9455, Level 16, State 1, Line 92
XML parsing: line 779, character 109, illegal qualified name character
Msg 9455, Level 16, State 1, Line 1
XML parsing: line 626, character 109, illegal qualified name character
It does not error out on all of them. In fact it executes great against the largest stored procedure. I have looked at and compared the stored procedures that parsed into xml without error and those that failed and nothing jumps out at me.
What I find interesting is that when I found the line referenced in the error it was a '<' in 2 of the stored procs and an ampersand in the other one. But in each case there were other occurrences of '<' and '&' that occurred in previous lines of code in the stored proc.
My workaround was to replace:
CAST(N'<?def --' + NCHAR(13)+NCHAR(10) + [Definition] + NCHAR(13)+NCHAR(10) + N'--?>' AS xml)
TRY_CONVERT(XML,N'<?def --' + NCHAR(13)+NCHAR(10) + [Definition] + NCHAR(13)+NCHAR(10) + N'--?>',1)
This keeps the code from failing but of course the definition is not created.