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.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Getting all non system Stored Procdedures

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

38200 Posts

Posted - 2006-08-11 : 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
Go to Top of Page

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_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
Go to Top of Page

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 work

by adding the

WHERE ROUTINE_NAME NOT LIKE 'sp[_]%'
, but shouldn't they not show up?
Go to Top of Page

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 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
Go to Top of Page

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 user

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -