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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

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 3 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

arup chakraborty

Posted Tuesday, July 29, 2008 3:33 AM

SSC-Addicted

Group: General Forum Members
Last Login: Tuesday, July 03, 2012 5:49 AM
Points: 418, 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?

Post #542453

James_DBA

Posted Tuesday, July 29, 2008 9:16 AM

SSC-Addicted

Group: General Forum Members
Last Login: Monday, June 10, 2013 8:44 AM
Points: 464, Visits: 8,718

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/

Post #542757

James_DBA

Posted Tuesday, July 29, 2008 9:39 AM

SSC-Addicted

Group: General Forum Members
Last Login: Monday, June 10, 2013 8:44 AM
Points: 464, Visits: 8,718

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/

Post #542787

twillcomp

Posted Tuesday, July 29, 2008 9:59 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 3:46 AM
Points: 198, Visits: 2,376

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

Post #542812

James_DBA

Posted Tuesday, July 29, 2008 10:31 AM

SSC-Addicted

Group: General Forum Members
Last Login: Monday, June 10, 2013 8:44 AM
Points: 464, Visits: 8,718

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/

Post #542855

Yelena Varshal

Posted Tuesday, July 29, 2008 12:41 PM

Hall of Fame

Group: General Forum Members
Last Login: Monday, July 30, 2012 10:42 AM
Points: 3,434, Visits: 519

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

Post #542964

twillcomp

Posted Wednesday, July 30, 2008 1:58 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 3:46 AM
Points: 198, Visits: 2,376

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 Smile

Post #543227

arup chakraborty

Posted Friday, August 08, 2008 3:06 AM

SSC-Addicted

Group: General Forum Members
Last Login: Tuesday, July 03, 2012 5:49 AM
Points: 418, Visits: 365

Thanks James, it is really helpful to me.

Post #548952

Slick84

Posted Friday, November 07, 2008 9:14 AM

SSC-Addicted

Group: General Forum Members
Last Login: Monday, October 31, 2011 1:10 PM
Points: 480, Visits: 1,163

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

Post #599055

Yelena Varshal

Posted Friday, November 07, 2008 9:29 AM

Hall of Fame

Group: General Forum Members
Last Login: Monday, July 30, 2012 10:42 AM
Points: 3,434, Visits: 519

The reason could be that you are running SQL Server 2000 that did not have this table.
Please, confirm

Regards,
Yelena Varshal

Post #599069

« Prev Topic | Next Topic »

35 posts, Page 3 of 4

«««1 2 3 4 »»

Permissions