Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Finding and Listing ALL Procedures, Functions, and Views in SQL Server 2005 Expand / Collapse
Author
Message
Posted Monday, July 28, 2008 9:03 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 12, 2008 8:25 AM
Points: 8, Visits: 43
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'
Post #541961
Posted Monday, July 28, 2008 9:53 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, January 20, 2014 3:38 PM
Points: 473, Visits: 8,736
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/
Post #541995
Posted Monday, July 28, 2008 12:05 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, March 8, 2013 10:18 AM
Points: 47, Visits: 165
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
Post #542105
Posted Monday, July 28, 2008 12:50 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, January 25, 2012 7:43 AM
Points: 63, Visits: 109
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 :D

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!
Post #542125
Posted Monday, July 28, 2008 2:41 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 28, 2010 8:22 PM
Points: 3, Visits: 14
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.
Post #542225
Posted Monday, July 28, 2008 3:23 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, January 20, 2014 3:38 PM
Points: 473, Visits: 8,736
Thank you for the nice remarks.

As pointed out in previous posts; the correct location to create stored procedures that are distributed to new databases is to use the 'model' database. This was an error in the article. I apologize for any inconvenience it may have caused.

Thank you,
James Rea



~ Without obstacles, you cannot progress ~
http://sqln.blogspot.com/
Post #542272
Posted Monday, July 28, 2008 3:47 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, March 8, 2013 10:18 AM
Points: 47, Visits: 165
That small error aside, you have led the way. I rewrote your stuff into a table-udf which accepts the object type as a parameter and it works beautifully. I can extract lists of procs, scalar udfs, table udfs, views and so on. Thanks to your initial code, I've got some magic now. I rewrote your code to be a table udf so I can pass parms to it and obtain all or nothing, depending on my interest at the moment.

Thanks for the pointers!

Arthur


Arthur Fuller
cell: 647-710-1314

Only two businesses refer to their clients as users: drug-dealing and software development.
-- Arthur Fuller
Post #542284
Posted Tuesday, July 29, 2008 12:01 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, September 8, 2014 7:06 PM
Points: 528, Visits: 1,260
Legal Disclaimer/Warning
Please remember that when using an SP you are not sure the outcome or effect it has should be done on test systems; never use anything that has not been thoroughly tested in a production environment.


Has anyone read the Legal Disclaimer. I think test sytem and production environment are mistakenly shuffled or it is me who is having a bad frozen sight today. :P
Post #542371
Posted Tuesday, July 29, 2008 12:32 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, January 20, 2014 3:38 PM
Points: 473, Visits: 8,736
Actually, the sentence is correct but can easily be mis-read.

The second part of the sentence is saying that you should not use anything you have not thoroughly tested in a production environment. It probably would've done better being placed into two complete sentences.

Thank you,
James Rea


~ Without obstacles, you cannot progress ~
http://sqln.blogspot.com/
Post #542377
Posted Tuesday, July 29, 2008 1:22 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, June 23, 2014 6:03 AM
Points: 1,154, Visits: 866
in sql 2005 we have so many ways u can list out the requird objects,

u can get the list of procedures using..sys.procedures

Post #542393
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse