0

Is there any way by which i can see how actually my stored procedure is working in SQL server management studio. When i call stored procedure from my business logic layer i want to see how it is executing step by step. It is providing me results from triggers and view can i see how it is functioning. thanks.

RachitSharma
  • 406
  • 4
  • 10
  • 28

2 Answers2

1

If I've understood you correctly, just run sp_helptext mySproc.

It will output the text of the stored procedure, which executes line-by-line (step-by-step).

You can see an example by running it on itself:

sp_helptext sp_helptext

Note: I remember there being some issues with sp_helptext the last time I used it for something advanced. Max line length is one of the issues I can remember off the top of my head.

I ended up rewriting sp_helptext myself, using the original sproc as a guideline. Unfortunately I no longer work for that company, so I don't have access to it any more.

Danny Beckett
  • 18,294
  • 21
  • 100
  • 129
  • Thank you for your reply. I know it. What i want to know is that is it possible that i can see how actually how sequentially my stored procedure is working just as i do in visual studio. I am using sql server management studio 2008 r2. thanks. – RachitSharma Sep 26 '13 at 07:17
  • @R_sharma I'm sorry, I don't understand your question. Can you maybe explain a different way? – Danny Beckett Sep 26 '13 at 07:18
  • ok. How can i use a debugger in sql server management studio? I have listened it somewhere that i can see how a stored procedure is working/executing from visual studio. Am i right? my stored procedure is providing me a result set based on inputs that i have provided to it. It is 500 lines long getting records from various tables it is not possible for me to study it like a novel . Is it any other way by which i can see how actually it is functioning. thanks. Regards :) – RachitSharma Sep 26 '13 at 07:36
  • Ah, I think I understand now. You need to see what changes are made on the database? Do you only need to see how it functions once? If so, backup your database, run your stored procedure, backup again, and compare the backups. There are a few tools on the internet for comparing databases. – Danny Beckett Sep 26 '13 at 07:41
  • yes u have got my question but i have not understand your answer . can you elaborate? – RachitSharma Sep 26 '13 at 07:45
  • @R_sharma Something like [What is best tool to compare two SQL Server databases (schema and data)?](http://stackoverflow.com/q/685053) – Danny Beckett Sep 26 '13 at 07:46
0

we have a debug option in sql server management(ssms), just try it once. But I too never used this debug option.

And we can see the query execution flow and time period.

  1. Estimated execution plan
  2. Actual execution plan

By using the above two tings we can see the execution flow. enter image description here

It may helps you..

Jagadeesh
  • 1,332
  • 5
  • 20
  • 31