June 20, 2002 at 12:10 pm
SQL 7.0/ 2000
Is it possible to find out whether parameter in the procedure requires a value or what is the default value for it with systables.
Most of the information about sproc can be obtained through syscomments table, thou I can not find those 2 extras.
Thanks in advance
here is the script that I have so far.
DECLARE @objid int
SELECT @objid = [ID] from sysobjects where xtype = 'p' and name = 'spr_u_profile'
PRINT @objid
/*Check if procedure has parameters*/
IF EXISTS (SELECT [id] FROM syscolumns WHERE [id] = @objID)
BEGIN
SELECT
[name] as 'Parameter_Name' ,
type_name(xusertype) as 'TYPE' ,
Length = 'Length',
CASE
WHEN isoutparam = 0x00 THEN 'NO'
ELSE 'YES'
END as 'Out Param',
CASE
WHEN type_name(xtype) = 'uniqueidentifier' then xprec
ELSE OdbcPrec(xtype,length,xprec)
END as 'Prec',
OdbcScale(xtype,xscale) as 'Scale',
colid as 'Param_Order',
Collation as 'Collation'
FROM syscolumns
WHERE
[id] = @objid
END
June 20, 2002 at 7:26 pm
Check out thread http://www.sqlservercentral.com/forum/topic.asp?TOPIC_ID=4157&FORUM_ID=49&CAT_ID=1&Topic_Title=Default%20Values%20of%20a%20Stored%20Procedure&Forum_Title=Strategies for more details and a link to a sp that a member posted to help.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 21, 2002 at 8:15 am
I was looking something where I do not have to parse procedure text.
I believe that SQL Server has it saved somewhere in the system tables, thou so far I haven't been able to find it.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply