0

Is there a tool, script, utility that can parse sys.sql_modules and return a list of tables and columns referenced in the WHERE, GROUP BY, and JOIN clauses of Stored Procedures? Example:

CREATE PROC myProc  
BEGIN  
    SELECT a.col1, b.col2, count(a.col7) as MyCount  
    FROM dbo.FirstTable as a  
    JOIN dbo.SecondTable as b on (a.col1 = b.col1)  
    WHERE b.col5 = 'xxx'  
    GROUP BY a.col1,b.col2   
END

Return a list such as:

  • FirstTable.col1
  • SecondTable.col2
  • SecondTable.col5
  • FirstTable.col1
  • SecondTable.col2

The goal is to use the result list to build some CREATE STATS commands based on the number of times a table.col pair is referenced.

  • Please tag your dbms. – McNets Apr 20 '17 at 21:13
  • And version please... – Aaron Bertrand Apr 20 '17 at 21:30
  • 1
    Do you have AUTO CREATE STATS turned off? – Erik Darling Apr 20 '17 at 21:37
1

sysdepends & sys.sql_modules have some known limitations... and you probably don't want to perform string-parsing gymnastics if you want to rely on your results with a high level of confidence. Although neither of these options is really something I would recommend as an answer to your problem.

The goal is to use the result list to build some CREATE STATS commands based on the number of times a table.col pair is referenced.

Perhaps you've got a highly targeted need to do exactly this, but my first reaction is that you might want to take a more holistic approach to your stats maintenance than reviewing the text inside stored procedures. You may have an heavy ad-hoc query load that doesn't ever touch a stored procedure. You may have 10 stored procs that reference all the things but are never called and 1 stored proc that touches less but is called all the time. I recommend taking a step back and browsing some literature before taking the path you seem to be steering toward with this question.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.