Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2006-08-11 : 18:11:54
|
I need to retrieve all non-system stored procedures that I created for a table.I tried usingselect * from Sysobjectswhere xType = 'P' and category = 0but I still get a few that were not created by me. for examplesp_upgradediagramsp_alterdiagramsp_creatediagrametc. |
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-08-11 : 18:20:44
|
You should be using the INFORMATION_SCHEMA views instead of the system tables.SELECT ROUTINE_NAMEFROM INFORMATION_SCHEMA.ROUTINESWe never use sp_ when using stored procedure names for performance reasons (I can explain this if you need me to. So to exclude MS ones, we just do this:SELECT ROUTINE_NAMEFROM INFORMATION_SCHEMA.ROUTINESWHERE ROUTINE_NAME NOT LIKE 'sp[_]%'Tara Kizer |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-08-11 : 18:54:03
|
This will exclude procedures that were shipped by Microsoft.SELECT ROUTINE_NAMEFROM INFORMATION_SCHEMA.ROUTINESwhere objectproperty(object_id(ROUTINE_NAME),'IsMSShipped') = 0 and objectproperty(object_id(ROUTINE_NAME),'IsProcedure') = 1order by ROUTINE_NAME CODO ERGO SUM |
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2006-08-11 : 20:22:00
|
Thanks to both of you, however why do the sp_alterdiagram,ap_Creatediagram,sp_dropDiagram,etc still show up as "IsMSShipped", I am able to get it to workby adding the WHERE ROUTINE_NAME NOT LIKE 'sp[_]%', but shouldn't they not show up? |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-08-11 : 22:01:38
|
quote: Originally posted by Vinnie881 Thanks to both of you, however why do the sp_alterdiagram,ap_Creatediagram,sp_dropDiagram,etc still show up as "IsMSShipped", I am able to get it to workby adding the WHERE ROUTINE_NAME NOT LIKE 'sp[_]%', but shouldn't they not show up?
Those procs show up as IsMSShipped because they are MS stored procs for maintaining database diagrams.CODO ERGO SUM |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-08-14 : 12:43:34
|
Also note that dtproperties is available as user table though not created by the userMadhivananFailing to plan is Planning to fail |
|
|
fiach
Starting Member
2 Posts |
Posted - 2010-12-13 : 07:38:47
|
I use the ElsaSoft database documentation tool, and it puts these system stored procedures into the list of procedures to document. Does anyone know what they actually do?, it'd be easier to write something than to explain they are not our procs...F. |
|
|
|
|
|
|
|