To split the code of a SP or ADHOC query into pieces in order to find out the number and names of temporary tables used, variable tables, dynamic code, calls of the stored procedures, dynamic tsql, usage of the IN, parameters used in the stored procedures, if code is using index hints, cursors, outer joins, set commands.
My way of achieving it
My approach is to split the code, one word on every line and looping to the lines and searching for specific sql keywords. Is not 100 percent accurate but it will give me a 50% or more accurate view of how the code was written and how we can improve it or how we can spot the bottlenecks. Each table has a some comments columns that can be used to add the things that we must be aware when using SP or dynamic code or DDL statements (create) with DML statemetns (insert) in a SP or when using table variables or IN operator and so on.
Below is an example of the output
First result set is the query displayed as one word per row. The second result set is the actual SP or adhoc query that we troubleshoot because here we will check the code that is point to us by the other result sets where we have locationincode. For example the fifth result set shows us that our stored procedure is using parameters and that parameter is used on line 37, select @dbname = parsename(@objname, 3). And this can help us to find out if a conversion will take place or not which is helpful in the overall troubleshooting process. Also we can see some of the tables that are used and if by experience or because we are familiar with the server and database e know that the table has millions of rows then we can start troubleshooting from there.
The script can be downloaded from here