• Toby,

    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)
    With
    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.
    Thanks again. 

    Lee