Home All Groups Group Topic Archive Search About

seperate SPs from UFNs in INFORMATION_SCHEMA.PARAMETERS...

Author
22 Sep 2005 2:47 PM
kevin
using SQL SERVER 2K

1. using INFORMATION_SCHEMA.PARAMETERS alone, how can I definitively know a
user defined function from a stored procedure.  I am currently doing this
*********
SELECT *
FROM INFORMATION_SCHEMA.PARAMETERS P JOIN INFORMATION_SCHEMA.ROUTINES R
ON(P.SPECIFIC_NAME = R.SPECIFIC_NAME)
where R.ROUTINE_TYPE = 'PROCEDURE'
*********

2. How can I definitively determine a system object as opposed to a user
created one.  SELECT *FROM INFORMATION_SCHEMA.PARAMETERS returns rows for SQL
Server defined SPs related to Database Diagrams (?) that are name like
'dt_%'.  I would like a better way to ensure that I am only looking at user
defined objects (tables, procs, etc).  This is obviously possible as
Enterprise Manager allows you to hide these very same objects.

thanks
kevin

Author
22 Sep 2005 3:03 PM
Aaron Bertrand [SQL Server MVP]
> 1. using INFORMATION_SCHEMA.PARAMETERS alone, how can I definitively know
> a
> user defined function from a stored procedure.

SELECT *  FROM INFORMATION_SCHEMA.PARAMETERS
     WHERE OBJECTPROPERTY(OBJECT_ID(SPECIFIC_SCHEMA+'.'+SPECIFIC_NAME),
'IsProcedure') = 1
-- I think if this is 0, it has to be a function?

> 2. How can I definitively determine a system object as opposed to a user
> created one.  SELECT *FROM INFORMATION_SCHEMA.PARAMETERS returns rows for
> SQL
> Server defined SPs related to Database Diagrams (?) that are name like
> 'dt_%'.

AND OBJECTPROPERTY(OBJECT_ID(SPECIFIC_SCHEMA+'.'+SPECIFIC_NAME),
'IsMSShipped') = 0

A

AddThis Social Bookmark Button