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 Tuesday, July 29, 2008 3:33 AM


SSC-Addicted

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

Group: General Forum Members
Last Login: Tuesday, July 3, 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
Posted Tuesday, July 29, 2008 9:16 AM


SSC-Addicted

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

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:00 AM
Points: 473, Visits: 8,737
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
Posted Tuesday, July 29, 2008 9:39 AM


SSC-Addicted

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

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:00 AM
Points: 473, Visits: 8,737
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
Posted Tuesday, July 29, 2008 9:59 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 5, 2014 6:19 AM
Points: 198, Visits: 2,492
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
Posted Tuesday, July 29, 2008 10:31 AM


SSC-Addicted

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

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:00 AM
Points: 473, Visits: 8,737
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
Posted Tuesday, July 29, 2008 12:41 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, November 26, 2014 9:53 AM
Points: 3,475, Visits: 584
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
Posted Wednesday, July 30, 2008 1:58 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 5, 2014 6:19 AM
Points: 198, Visits: 2,492
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



Post #543227
Posted Friday, August 8, 2008 3:06 AM


SSC-Addicted

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

Group: General Forum Members
Last Login: Tuesday, July 3, 2012 5:49 AM
Points: 418, Visits: 365
Thanks James, it is really helpful to me.
Post #548952
Posted Friday, November 7, 2008 9:14 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-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


--
Post #599055
Posted Friday, November 7, 2008 9:29 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, November 26, 2014 9:53 AM
Points: 3,475, Visits: 584
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 »

Add to briefcase «««1234»»

Permissions Expand / Collapse