|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
seperate SPs from UFNs in INFORMATION_SCHEMA.PARAMETERS...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 > 1. using INFORMATION_SCHEMA.PARAMETERS alone, how can I definitively know SELECT * FROM INFORMATION_SCHEMA.PARAMETERS> a > user defined function from a stored procedure. 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 AND OBJECTPROPERTY(OBJECT_ID(SPECIFIC_SCHEMA+'.'+SPECIFIC_NAME), > created one. SELECT *FROM INFORMATION_SCHEMA.PARAMETERS returns rows for > SQL > Server defined SPs related to Database Diagrams (?) that are name like > 'dt_%'. 'IsMSShipped') = 0 A |
|||||||||||||||||||||||