May 23, 2010 at 10:21 pm
I need a query / way to get the list of parameters in stored procedures in my database having default values set. Is it possible in SQL Server 2005 / 2008?
May 24, 2010 at 3:59 am
It's more than unlikely to get those values, since there are several ways to assign a default value to a parameter (e.g. as part of the CREATE PROCEDURE statement, using SET or SELECT and assign a fixed value or using SELECT and assign a valued based on a query).
What makes it even more complicated: if I have a SELECT statement that assign a value to a variable based on a query right after the DECLARE section, will this statement be considered as a declaring a DEFAULT value?
May 24, 2010 at 4:19 am
you may want to look at "SYS.PARAMETERS"
May 24, 2010 at 4:29 am
lmu92 (5/24/2010)
It's more than unlikely to get those values, since there are several ways to assign a default value to a parameter (e.g. as part of the CREATE PROCEDURE statement, using SET or SELECT and assign a fixed value or using SELECT and assign a valued based on a query).What makes it even more complicated: if I have a SELECT statement that assign a value to a variable based on a query right after the DECLARE section, will this statement be considered as a declaring a DEFAULT value?
I need to have the default values assigned to them in the definition of parametrs in SP. Currently I am performing this task by string manipulation, but looking for a better solution. Any Idea?
Thanks.
May 24, 2010 at 4:30 am
Gopi Muluka (5/24/2010)
you may want to look at "SYS.PARAMETERS"
In SYS.PARAMETERS, we do have Has_Default_Value bit and Default _Value column. But these are related to the CLR objects. http://msdn.microsoft.com/en-us/library/ms176074.aspx
May 28, 2010 at 5:30 am
I have submitted a script to accomplish this task. plz comment to make it meor efficient.
http://www.sqlservercentral.com/scripts/Stored+Procedures/70363/
February 7, 2013 at 8:57 am
"SQL Server only maintains default values for CLR objects in this catalog view (sys.parameters); therefore, this column (has_default_value) has a value of 0 for Transact-SQL objects. To view the default value of a parameter in a Transact-SQL object, query the definition column of the sys.sql_modules catalog view, or use the OBJECT_DEFINITION system function."
http://msdn.microsoft.com/en-us/library/ms176074.aspx
The path does not define the traveler. Rather, she experiences the path and becomes whom God meant her to be.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy