stored procedure library
-
Hi all,
I'm about to embark on a project that could have quite a few stored procedures. I can probably organize them pretty well using a good naming scheme. However, I'm wondering if there's a user interface that would allow me to visually store them into separate libraries. I know Oracle has something like this, but I'm not sure how that could be handled in SQL Server. I'm a long-time user of MSSMS and working with hundreds of stored procs is going to be a pain if I have to keep scrolling up and down the programmability section of a database.
Maybe I should just ask folks how they handle any project that requires hundreds of stored procs.
Thanks in advance.
David
-
The database at my current job has 1600 stored procedures, 200 views, and 140 functions. With those kind of numbers, SSMS becomes a time sink as you scroll and scroll looking for a stored procedure.
What I've found to be most helpful is a stored procedure I keep in the master database which accepts an object name and then prints out the definition. I map this stored procedure to a hot key so all I have to do is highlight the stored procedure or function name and then hit ctrl-5 in order to see the code.
You can also write a view with the stored procedure names and XML-links to their definitions. You can query the view by name (or with a LIKE clause) to quickly find the code you need. It might sound extreme but the 5-10 seconds it takes scrolling through SSMS adds up quickly, especially when you're doing debugging.
And obviously schemas are helpful for organization...
-
sp_helptext is a built in procedure which will do just that: given an object name (3 part name allowed) it spits out the definition.
Alternatlively, Redgate SQLPrompt is nice for a full intellisense style definition.
-
Also, just in the spirit of enhancing productivity, consider downloading SSMS Tools Pack (http://www.ssmstoolspack.com/) from Mladen Prajdic. It's a phenomenal product and it's free.
-
I would recommend SQL Server Data Tools.
It's the new development IDE for database projects that Microsoft released with SQL Server 2012 and it works with databases from 2005 on.
See here for more details:
http://msdn.microsoft.com/en-us/data/tools.aspx
-- Gianluca Sartori
-
another option:
if you use a common naming convention across related procs/tables etc, SSMS has the little used option to filter the results in the Object Browser to limit the items you might want to review.
for example, if all the procs related to "accounting" had the substring "account" in it:
you could make it easier to limit the thousands of procs to a more managable subset you are interested in.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Hey all, this is wonderful information.
Many many thanks.
David
-
You definitely need SSMSBoost add-in. I have also several databases with 1000+ stored procedures and same amount ot tables and views and it helps me to navigate through the source code. Add-in has a lot of features, but you will like "find object in object explorer", which locates object placed under cursor in SQL editor in Object Explorer. You can also open source code of object (=go to definition) directly from SQL Editor. Object search is also present (wildcard search).
Have a try: ssmsboost.com
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply