• Lynn Pettis - Monday, April 3, 2017 3:22 PM

    Just curious, why would you want to remove comments from your SQL code/scripts?

    I've done this specifically when i'm searching the text of sys.sql_modules.definitions, looking for various things related to coding practices, but not references to objects.
    specifically, i have a similar procedure that strips comments, and then also all whitespace.

    now that text has no spaces,and i can search for coding patterns.
    if I find "SELECTTOP1" in any cleaned definition, there is an excellent chance that someone is using an inline query to get one column value, which is executed on a per row basis. I use that to replace it with a proper join and group by to fix performance.
    i can then check for exists /not exists for other things like whether "SETXACT_ABORTON" exists in the stripped clean Definition or not, and decide whether it should have been there..."NOCOUNTON" , "ASBEGIN" and so many other "better practices" that should exist but might be missing.

    also it makes it a bit easier to search for whehter @TableVariables where used, and i might want to replace with #temp tables, and index hints like nolock or merge/loop joins, etc.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!