July 26, 2008 at 11:30 am
Comments posted to this topic are about the item Finding and Listing ALL Procedures, Functions, and Views in SQL Server 2005
~ Without obstacles, you cannot progress ~
http://sqln.blogspot.com/
July 28, 2008 at 1:06 am
When I read this it made me think of a procedure we use from Codeplex: http://www.codeplex.com/ScriptDB
We run it nightly to scripts all databases, procedures, triggers, indexes, etc.. Obviously this is very good as a backup alternative, but if you dump this regularly, you can also use this to quickly find back that previous version of a stored procedure you just (accidentally) overwrote 🙂
great for inclusion in SVN.
René
July 28, 2008 at 2:20 am
Nice one ...:)
July 28, 2008 at 2:21 am
nice article
"Keep Trying"
July 28, 2008 at 2:32 am
Sorry, but this part...
One last note, if you want to create an SP and have it distributed (automatically included) with all databases you create then you will want to create the SP for the 'master' database. Also, this only gets used with newly created databases from the point after you created the SP...So, if you already have existing databases you will still have to create the SP for each one of them.
...doesn't make sense.
It would make sense if instead of master you would write model.
Nice article and I am currently testing things...:w00t:
July 28, 2008 at 2:43 am
sometimes when i'm searching for a particular procedure....i use the following:
use [your DB here]
GO
SELECT Distinct SO.Name
FROM sysobjects SO (NOLOCK)
INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID
AND SO.Type = 'P'
AND SC.Text LIKE '%YOUR SEARCH TEXT HERE e.g. TABLENAME%'
ORDER BY SO.Name
July 28, 2008 at 3:45 am
I believe using information_schema.routines is still a better idea, code using this will work across SQL server versions..
July 28, 2008 at 6:33 am
Thanks very much for this informative and helpful article. Just last week we were discussing cleaning up our test/development database and this information is timely and very handy. Great work!
July 28, 2008 at 7:12 am
"SQL Server 2005 only stores the identifying information of a SP."
actually, SQL05 DOES store the code of the object itself as well. it can be found in sys.sql_modules, and a join can be used from sys.all_objects to sys.sql_modules on object_id. there are some catches though, so you may not get to see the code for ALL objects (especially system objects).
July 28, 2008 at 8:55 am
Although I agree that using 'sys.' views is a better way to get information, I have used the text column of the syscomments table directly in the past to get the DDL (data description language) for the script-level definitions of stored procedures, views, default values, etc. I do not use this table (directly) for production level code.
July 28, 2008 at 9:03 am
Look at: GeckoWare SQL Scribe for Microsoft SQL Server.
www.ag-software.com
Main non-standard use is Comparing DB Versions [Tables/SP/Function/Views...]
Upgrade are supplied by our ERP programmers - I can see what they have changed in new release and which parts of database are 'our bespoke'
July 28, 2008 at 9:53 am
Thank you to everyone for the great comments on additional methods to obtain the same data; and especially to those that have methods that could improve on the speed of obtaining this data!
Lenny Garza, you are correct that the definitions are stored within SQL Server 2005 sys.sql_modules view; I had intended for that line to imply that the 'how' to use these SPs still may require BOL to understand their usages. I'm actually already working on an article that touches upon this subject. Thank you for pointing out the confusion of that sentence.
Thank you,
James Rea
~ Without obstacles, you cannot progress ~
http://sqln.blogspot.com/
July 28, 2008 at 12:05 pm
I was just about to hit the same button, when I spotted your reply. If you want a proc to appear in every database you create (subsequent to adding it), place it in model not master. I have experimented with this extensively, to create versions of model that are greared toward specific problems such as order-entry systems or accounting systems or HR systems. I back up model, add the stuff I'm sure I'm going to need, save it and then rename it to something like model_OrderEntry. Then when I have to build one of these, I just rename model to model_virgin and model_OrderEntry to model, then create the database. Not much overhead
Arthur
Arthur Fuller
cell: 647-710-1314
Only two businesses refer to their clients as users: drug-dealing and software development.
-- Arthur Fuller
July 28, 2008 at 12:50 pm
The way I find out what stored procedures are in a database is in Management Studio, to drill into the Programmability | Stored Procedures | System Stored Procedure. Here you can find the sys or user created listing. I like this as it easily allows me to what they are made of, the thing I need to learn is how to use them 😀
What ever happened to programmers documenting there code? 😉
😀
ACN is the world's largest direct seller of telecommunications and essential services.
http://helpu.acndirect.com/
ACN Digital Phone Service customers automatically enjoy unlimited calling to 60 landline
destinations around the world, including India, Mexico and the UK!
:hehe:
July 28, 2008 at 2:41 pm
Information provided helps us. Have a question here:When we create a database, it inherit the properties from MODEL database. In this case if we create a stored procedure in master database how it will be distributed in all the other user databases.
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply