October 7, 2009 at 8:22 am
Perhaps this is a silly question, but is there any TSQL equivalent to right clicking on a stored procedure in Management Studio and saying Script To - Alter To - New Window?
I know I can do sp_helptext sprocName and then copy/paste the output results into SSMS, but is there any other way to do that?
I assume there isn't a magic TSQL command to tell SSMS to open it into a new window in the same way the right click works, but there must be other options besides "sp_helptext sprocName" to display the sproc's definition to allow altering?
Basically I'm trying to slowly wean myself of my dependence on SSMS’ GUI.
October 7, 2009 at 8:27 am
The short answer is no, at least not what it appears you are looking for.
But I have a larger question, based on your question you treat the server as the system-of-record for the code, ie: the source is on the server. I think that methodology is problematic..
How do you do source control (or do you)?
As a discipline, the code on the server is always the second copy, I have a file with the complete code for the stored procedure that was used to create/modify the sproc on the server. I always go back to the file. The server copy is just that a copy, to be replaced by the most current file version.
Thoughts?
CEWII
October 7, 2009 at 8:58 am
You can use code and SMO to replicate what SSMS does, but no, there's not any easy way to do it with TSQL alone. Besides, SSMS is useful, just don't use it for everything.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 7, 2009 at 3:38 pm
You are completely correct, we are using the server as "the copy" of the code.
We use VSS and are migrating to TFS. I am thinking that once we get to TFS we will use source control for our TSQL as well.
October 7, 2009 at 4:49 pm
I would strongly recommend using a source control system for your SQL as well. For most projects the first pull of code takes some work to get it all the way you want it, but once you do it makes problem resolution that much easier because the source controlled version is the master. You will have to change your culture to stop server editting.
CEWII
October 7, 2009 at 6:06 pm
entire definition is stored in sys.sql_modules but there is no formatting ;/
October 9, 2009 at 2:39 pm
If you change your SSMS window to return results as text instead of on a grid (theres a button to the left of the results as grid button to do this) you'll get the proc all nice and formatted like you like. Then you can either sp_helptext or query the info schema.routines view asking for the particular sp that you want. I use the info_schema views alot when i want to actually search the text of my stored procs / views for a particular table or column name. They're nice to have but if you just want the dump of the proc definition sp_helptext it with results to text (less typing). =)
sp_helptext '<enter sp name here>
SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = '<enter sp name here>'
-Michael Abair
Database Administrator
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply