[dba-SQLServer] Viewing and Modifying stored procedures

Ron Allen chizotz at mchsi.com
Wed Jun 27 14:49:23 CDT 2007


Hi Jennifer,

I am still on SQL Server 2000, using Enterprise Manager and Query Analyzer, 
but understand that all of that functionality is built into 2005 just named 
differently.

You are correct that the way to edit stored procedures in SQL server is 
through T-SQL using the CREATE or ALTER syntax. There is no Access-like query 
builder that lets you build a query visually. Whether this is good or bad or 
right or wrong can be debated endlessly, and there are what I consider valid 
arguments on both sides. 

That said, in SQL Server 2000 there are at least two ways to simply see the SP 
code. You can edit the SP in Query Analyzer or you can select "properties" for 
the SP in Enterprise Manager. In either case, you will also see the "ALTER" 
or "CREATE" portion of the syntax because that is a part of the syntax of the 
SP. It isn't a "template", which I'm thinking may be one of the places you 
need to make a conceptual shift.

Don't get discouraged, because a vast number of things that you learned in 
Access will be useful when working with SQL Server, but usually only in that 
it makes things familiar not because there is a direct correlation on the 
nitty gritty details. 

Ron




> That makes complete sense, but it doesn't work :(  I read somewhere that
> the Query Analyzer in previous versions of SQL Server is no longer a
> part of SQL Server 2005.  I am not sure, but have a feeling, that this
> missing Query Analyzer is the reason I can't do what I want to do.
> Apparently, unless you use T-SQL to edit stored procedures, the only
> other way they can be edited is through Visual Studio.  It can't be done
> directly in what is now called SQL Server Management Studio.  It appears
> I need to learn how to navigate Visual Studio 2005 as well as SQL Server
> 2005.
> 

> >
> >Thanks Robert.  It is sinking in that this is the way it works in SQL 
> >Server.  Even if I just want to see the stored procedure so that I can 
> >understand what it does (since I inherited this database), I have to 
> >generate this ALTER PROC template and then close the window without 
> >saving.  Seems strange that I just can't take a look at the stored 
> >procedure code, but I guess that's the way it is.



More information about the dba-SQLServer mailing list