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
arup chakraborty
arup chakraborty
SSC Eights!
SSC Eights! (994 reputation)SSC Eights! (994 reputation)SSC Eights! (994 reputation)SSC Eights! (994 reputation)SSC Eights! (994 reputation)SSC Eights! (994 reputation)SSC Eights! (994 reputation)SSC Eights! (994 reputation)

Group: General Forum Members
Points: 994 Visits: 365
"Which, of course, the 'master' database is the "template" database used whenever you create a new database (thus, the SPs within the 'master' database are automatically created for the databases you create)"

I think this will be model instead of master. Bkz model database works as a template database for all other.

Also, the sysobject view gives us the same results, any difference between sysobjects and all_objects?
James_DBA
James_DBA
SSChasing Mays
SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)

Group: General Forum Members
Points: 654 Visits: 8754
Abhijit,

The problem with using sys.procedures is it ONLY lists out the user created Stored Procedures; this article was in regards to finding ALL procedures (including the MS provided SPs and other programmable objects).

Thank you,
James Rea

~ Without obstacles, you cannot progress ~
http://sqln.blogspot.com/
James_DBA
James_DBA
SSChasing Mays
SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)

Group: General Forum Members
Points: 654 Visits: 8754
arup_kc,

Essentially there is no difference between the two views except for when they were created; however, sysobjects is a SQL 2000 view and was left in SQL 2005 for backward compatability reasons which would make it subject to deprecation before sys.all_objects view.

The proper (or recommended) view in SQL 2005 that replaced sysobjects is sys.objects. Which if you use the sys.objects view you will only see user-defined and schema scoped objects. You'd then have to query the sys.system_objects (then use the two tables together) to obtain a complete listing of the system objects and the user-defined objects. Obviously, using a single table/view would be preferred in most cases.

I chose to use sys.all_objects because it already lists both system and user-defined objects and is a SQL 2005 view.

Thank you,
James Rea

~ Without obstacles, you cannot progress ~
http://sqln.blogspot.com/
twillcomp
twillcomp
Old Hand
Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)

Group: General Forum Members
Points: 326 Visits: 2627
I believe some care is needed if you are relying on the is_ms_shipped flag being set to 1 for all MS shipped procedures. I get a list of stored procedures which I think are diagramming procedures that are not user defined procedures.

For example:
sp_alterdiagram
sp_creatediagram
sp_dropdiagram
sp_helpdiagramdefinition
sp_helpdiagrams
sp_renamediagram
sp_upgraddiagrams

There may be more.

cheers
Tony



James_DBA
James_DBA
SSChasing Mays
SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)

Group: General Forum Members
Points: 654 Visits: 8754
LimeyTone,

Those SPs you listed in regards to not having the "Is_MS_Shipped" flag should, in fact, NOT have the flag. They should not be marked as being shipped from Microsoft because they were not a part of the SQL 2005 distribution stored procedures.

Those SPs are actually created from Visual Studio; typically you will see them in SQL instances where VS was used to create a diagram. I'm sure there are other ways these procedures could become installed on a SQL instance.

Here is a link that specifically states that VS will create these Stored Procedures: http://msdn.microsoft.com/en-us/library/ms171974.aspx. You'll notice this list exactly matches your list.

I think in this instance we can see the value in being able to view a listing of stored procedures; in this case you were unaware that VS created these. Maybe there are more stored procedures you are not aware of that have been created?

Thanks,
James Rea

~ Without obstacles, you cannot progress ~
http://sqln.blogspot.com/
Yelena Varshal
Yelena Varshal
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8788 Visits: 600
Kids,

Try this: it will print all your SPs text. I just modified the original query from the article to include OBJECT_DEFINITION function. You have to set Query Results In Text to use it.

SELECT '--*****************************************'
+Char(13)+Char(10)+ '--'+Char(9) +Name
+Char(13)+Char(10)+ '--'
+Char(13)+Char(10)
+'--*****************************************'
+Char(13)+Char(10)+ '--'
,OBJECT_DEFINITION ( object_id (name))
FROM sys.all_objects
WHERE ([type] = 'P' OR [type] = 'X' OR [type] = 'PC')
ORDER BY [name];
GO


Regards,
Yelena Varshal

twillcomp
twillcomp
Old Hand
Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)

Group: General Forum Members
Points: 326 Visits: 2627
Cheers for this James I didn't know this.

The point I made about being careful if you rely on the flag to identify user defined procs still holds. I'm sure there might be someone out there who tries to list all user procs for some dodgy maintenance process who will end up messing with procs they didn't intend to.

Your warning about knowing what you are running before you run it holds true here!

PS. If I was being picky, I'd ask who ships VS :-)



arup chakraborty
arup chakraborty
SSC Eights!
SSC Eights! (994 reputation)SSC Eights! (994 reputation)SSC Eights! (994 reputation)SSC Eights! (994 reputation)SSC Eights! (994 reputation)SSC Eights! (994 reputation)SSC Eights! (994 reputation)SSC Eights! (994 reputation)

Group: General Forum Members
Points: 994 Visits: 365
Thanks James, it is really helpful to me.
Slick84
Slick84
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1612 Visits: 1163
When I run the query I get the following error:

Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.all_objects'.



What could be the issue?

Thanks,
O

--
Hehe
Yelena Varshal
Yelena Varshal
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8788 Visits: 600
The reason could be that you are running SQL Server 2000 that did not have this table.
Please, confirm


Regards,
Yelena Varshal

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