<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by James Rea
»
Finding and Listing ALL Procedures,...
35 posts, Page 2 of 4
««
1
2
3
4
»
»»
Finding and Listing ALL Procedures, Functions, and Views in SQL Server 2005
Rate Topic
Display Mode
Topic Options
Author
Message
sbateman-803743
sbateman-803743
Posted Monday, July 28, 2008 9:03 AM
Forum 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
James_DBA
James_DBA
Posted Monday, July 28, 2008 9:53 AM
SSC-Addicted
Group: General Forum Members
Last Login: 2 days ago @ 2:20 PM
Points: 464,
Visits: 8,717
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
fuller.artful
fuller.artful
Posted Monday, July 28, 2008 12:05 PM
SSC Rookie
Group: General Forum Members
Last Login: Friday, March 08, 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
wchaster
wchaster
Posted Monday, July 28, 2008 12:50 PM
Valued 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
vinsat
vinsat
Posted Monday, July 28, 2008 2:41 PM
Forum 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
James_DBA
James_DBA
Posted Monday, July 28, 2008 3:23 PM
SSC-Addicted
Group: General Forum Members
Last Login: 2 days ago @ 2:20 PM
Points: 464,
Visits: 8,717
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
fuller.artful
fuller.artful
Posted Monday, July 28, 2008 3:47 PM
SSC Rookie
Group: General Forum Members
Last Login: Friday, March 08, 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
Anam Verma
Anam Verma
Posted Tuesday, July 29, 2008 12:01 AM
Mr or Mrs. 500
Group: General Forum Members
Last Login: 2 days ago @ 11:53 PM
Points: 524,
Visits: 1,181
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
James_DBA
James_DBA
Posted Tuesday, July 29, 2008 12:32 AM
SSC-Addicted
Group: General Forum Members
Last Login: 2 days ago @ 2:20 PM
Points: 464,
Visits: 8,717
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
abmore
abmore
Posted Tuesday, July 29, 2008 1:22 AM
Ten Centuries
Group: General Forum Members
Last Login: Thursday, May 16, 2013 8:06 AM
Points: 1,134,
Visits: 818
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 »
35 posts, Page 2 of 4
««
1
2
3
4
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
