column_def in sp_sproc_columns doesn''t show default value
-
I'd like to find out at runtime the default value for a stored procedure parameter.
I'm trying to use the system stored procedure sp_sproc_columns to determine this.
Online Books says COLUMN_DEF contains the default value for the column and the column_name column displays the name of the stored procedure parameter. However COLUMN_DEF always shows NULL for all stored procedures in the system.
Does anyone have any ideas how to determine the default value for a stored procedure parameter?
Thanks in advance.
Fred
-
SELECT *
FROM syscolumns
INNER JOIN syscomments ON syscolumns.cdefault = syscomments.id
_____________
Code for TallyGenerator -
Serqiy,
This looks like it gives me default values for table columns. I need to get the default value for a stored procedure parameter like the following:
CREATE PROCEDURE dbo.PROC_AllEvents
@InputSessionID int = 100,
....
I would like to find out that the default value for @InputSessionID is 100.
Can this be done?
Fred
-
add to my script:
where syscolumns.id = object_id(N'dbo.PROC_AllEvents')
and you'll see sky with diamonds!
SPs are in that list too.
_____________
Code for TallyGenerator -
Sorry Sergiy but this can't work >>
CREATE PROCEDURE dbo.TestDefaults @Input as int = 0
AS
SET NOCOUNT ON
Select @Input * 2 as Demo
SET NOCOUNT OFF
GO
Select O.Name, S.Name, S.cdefault from dbo.SysObjects O inner join dbo.SysColumns S on O.id = S.id where O.XType = 'P' AND O.Name = 'TestDefaults'
--TestDefaults@Input0
GO
DROP PROCEDURE TestDefaults
The cdefault column reffers to a default constraint object and those are not created when you create an sp
. So I'd be inclined to say that it's impossible to do without using some parsing technic. -
the procedure sp_sproc_columns was "meant" to do that
UNFORTUNATELY COLUMN_DEF (which is is what you are after) is HARDCODED to: convert( nvarchar(4000), NULL)
The reason: there is no place in the schema reserved for that (Design oversite
)Remi is correct! as of today you will have to perform some sort of parsing on the definition if you need that info
Cheers!
* Noel
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply