How to return JUST the user defined stored procedures.

[TonyKhela]

Hi,

In SQL 2000 if you ran:
SELECT * FROM SYSOBJECTS WHERE TYPE='P'
It returned the stored procedures.

But in SQL 2005, this will return all the system stored procedures too.

I used the Profiler to capture the query that Mgmt Studio uses. This a cut down version of it:
SELECT sp.name AS [Name], sp.create_date AS [CreateDate]
FROM
sys.all_objects AS sp
LEFT OUTER JOIN sys.sql_modules AS smsp ON smsp.object_id = sp.object_id
LEFT OUTER JOIN sys.system_sql_modules AS ssmsp ON ssmsp.object_id = sp.object_id
WHERE
(sp.type = N'P' OR sp.type = N'RF' OR sp.type='PC')and(CAST(
case
when sp.is_ms_shipped = 1 then 1
when (
select
major_id
from
sys.extended_properties
where
major_id = sp.object_id and
minor_id = 0 and
class = 1 and
name = N'microsoft_database_tools_support')
is not null then 1
else 0
end
AS bit)=0)
ORDER BY [Name] ASC

Does anyone know of a simpler way?

Regards

TK

 

[SQLDenis]

SELECT SPECIFIC_NAME as Name, Created
FROM information_schema.routines
WHERE OBJECTPROPERTY(OBJECT_ID(SPECIFIC_NAME),'IsMSShipped') =0

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[mrdenny]

select *
from sys.procedures
where is_ms_shipped = 0

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com