| Author |
 Topic  |
|
|
Vinnie881
Flowing Fount of Yak Knowledge
USA
1202 Posts |
 Posted - 08/11/2006 : 18:11:54
|
I need to retrieve all non-system stored procedures that I created for a table.
I tried using
select * from Sysobjects
where xType = 'P' and category = 0
but I still get a few that were not created by me.
for example
sp_upgradediagram
sp_alterdiagram
sp_creatediagram
etc. |
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 08/11/2006 : 18:20:44
|
You should be using the INFORMATION_SCHEMA views instead of the system tables.
SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
We 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_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME NOT LIKE 'sp[_]%'
Tara Kizer |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 08/11/2006 : 18:54:03
|
This will exclude procedures that were shipped by Microsoft.
SELECT
ROUTINE_NAME
FROM
INFORMATION_SCHEMA.ROUTINES
where
objectproperty(object_id(ROUTINE_NAME),'IsMSShipped') = 0 and
objectproperty(object_id(ROUTINE_NAME),'IsProcedure') = 1
order by
ROUTINE_NAME
CODO ERGO SUM |
|
|
|
Vinnie881
Flowing Fount of Yak Knowledge
USA
1202 Posts |
Posted - 08/11/2006 : 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 work
by adding the
WHERE ROUTINE_NAME NOT LIKE 'sp[_]%'
, but shouldn't they not show up? |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 08/11/2006 : 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 work
by 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
India
22460 Posts |
Posted - 08/14/2006 : 12:43:34
|
Also note that dtproperties is available as user table though not created by the user
Madhivanan
Failing to plan is Planning to fail |
|
|
|
fiach
Starting Member
United Kingdom
2 Posts |
Posted - 12/13/2010 : 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.
|
Edited by - tkizer on 12/13/2010 12:55:40 |
|
|
| |
Topic |
|
Getting all non system Stored Procdedures
