A very common question I often see users not sure about the answer. This is indeed a tricky question, but a very simple one. If I am the interviewer, I may ask this question to the user, but if they do not know the answer of this question, I would not give any negative remarks to interviewee. I think it is even old school to even ask these kind of questions. It has been long since I have stopped asking this question, once in a while, I still see this question being asked by my fellow colleagues. Well, let us revisit the age old question again about the fastest way to display code of any stored procedure.
Question: What is the fastest way to display code of any stored procedure?
Answer: Well, you may be temped to say that the fastest way is to go to stored procedure in SQL Server Management Studio and click on modify. Honestly, this may be right answer if you have very few stored procedures in your database. However, if you have hundreds of the stored procedure in your application, trust me it is not possible to have located your stored procedure easily (even if you use the search feature of SQL Server Management Studio).
Let us see the fastest way to display code of the SP.
First, go to SQL Server Management Studio and select option Results to Text (shortcut CTRL+T).
Next type following script in Query Editor and hit execute. Do not forget to replace the string ‘NameofYourSP’ with the name of your actual stored procedure.
sp_helptext 'NameofYourSP'
You will see immediate results in the query window in the text format. Trust me, once you get used to this method you will find it very easy to display the code of the SP.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
7 Comments. Leave new
select object_definition(object_id(‘NameofYourSP’)) ?
SELECT text
FROM sys.syscomments
WHERE OBJECT_NAME(id) = ‘spname’
We can add a query shortcut (Tools->Options->Environment->Keyboard->Query Shortcuts) of SP_HELPTEXT and assign to a key. Then, we can get the code by selecting the stored procedure name and hit the short cut key.. :) hope that helps
Great post! I’ve used sp_helptext many times but the ‘Results to Text’ is a game changer. Gives me the stored procedure in proper format rather than all in one line.
heh. as someone who have used sql server 6.0 I immediately thought “sp_helptext”! but I also thought that it couldn’t possibly be the fastest way to display a proc after so many years!
Hi Pinal, This Is Pasha.
I have a problem and learnt a lot from your blogs a lot. Really appreciates your dedication.
I need to create a stored procedure, in which I need to keep track of daily track of each file runs daily and also count of the records track too for each file. Like below:
Table Name: AuditLog
Date No.OfRec. FileName LogMessage Status Record
2017-01-09 1200 \\File1.txt Successful Loaded 1200 Success 1
2017-01-09 1000 \\File2.txt Successful Loaded 1000 Success 2
2017-01-10 1100 \\File3.txt Successful Loaded 1100 Success 3
2017-01-11 80 \\File4.txt Needs to reload, deviation is too loo None 4
For Record # 4, I do not create a text file in SSIS package after this message, else create file. My problem is , how to check this deviation (NoOfRec) in stored procedure by using this table.
I would appreciate, if you reply me.
Thanks
Pasha
You can use query with LIKE operator to choose word deviation.