SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Finding and Listing ALL Procedures, Functions, and Views in SQL Server 2005


Finding and Listing ALL Procedures, Functions, and Views in SQL Server 2005

Author
Message
sbateman-803743
sbateman-803743
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 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'
James_DBA
James_DBA
SSChasing Mays
SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)

Group: General Forum Members
Points: 610 Visits: 8754
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/
fuller.artful
fuller.artful
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 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
wchaster
wchaster
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 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 BigGrin

What ever happened to programmers documenting there code? Wink

:-D
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
vinsat
vinsat
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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.
James_DBA
James_DBA
SSChasing Mays
SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)

Group: General Forum Members
Points: 610 Visits: 8754
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/
fuller.artful
fuller.artful
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 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
Anam Verma
Anam Verma
SSC Eights!
SSC Eights! (829 reputation)SSC Eights! (829 reputation)SSC Eights! (829 reputation)SSC Eights! (829 reputation)SSC Eights! (829 reputation)SSC Eights! (829 reputation)SSC Eights! (829 reputation)SSC Eights! (829 reputation)

Group: General Forum Members
Points: 829 Visits: 1318
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. Tongue
James_DBA
James_DBA
SSChasing Mays
SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)

Group: General Forum Members
Points: 610 Visits: 8754
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/
abmore
abmore
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1315 Visits: 931
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search