SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Getting all non system Stored Procdedures
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Vinnie881
Flowing Fount of Yak Knowledge

USA
1222 Posts

Posted - 08/11/2006 :  18:11:54  Show Profile  Reply with Quote
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
36820 Posts

Posted - 08/11/2006 :  18:20:44  Show Profile  Visit tkizer's Homepage  Reply with Quote
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)

USA
7020 Posts

Posted - 08/11/2006 :  18:54:03  Show Profile  Reply with Quote


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
Flowing Fount of Yak Knowledge

USA
1222 Posts

Posted - 08/11/2006 :  20:22:00  Show Profile  Reply with Quote
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)

USA
7020 Posts

Posted - 08/11/2006 :  22:01:38  Show Profile  Reply with Quote
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

India
22754 Posts

Posted - 08/14/2006 :  12:43:34  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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

United Kingdom
2 Posts

Posted - 12/13/2010 :  07:38:47  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000