List of parameters having default values

Question

List of parameters having default values

Atif-ullah Sheikh

SSChampion

Points: 12495
More actions
May 23, 2010 at 10:21 pm

#222850

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?
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sometimes, winning is not an issue but trying.
You can check my BLOG [font="Arial Black"]here[/font][/url][/right]

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

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?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Gopi Muluka SSCarpal Tunnel Points: 4594 More actions · Answer 2

Gopi Muluka

SSCarpal Tunnel

Points: 4594

May 24, 2010 at 4:19 am

#1171447

you may want to look at "SYS.PARAMETERS"

Atif-ullah Sheikh SSChampion Points: 12495 More actions · Answer 3

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.

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.
You can check my BLOG [font="Arial Black"]here[/font][/url][/right]

Atif-ullah Sheikh SSChampion Points: 12495 More actions · Answer 4

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

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.
You can check my BLOG [font="Arial Black"]here[/font][/url][/right]

Atif-ullah Sheikh SSChampion Points: 12495 More actions · Answer 5

I have submitted a script to accomplish this task. plz comment to make it meor efficient.

http://www.sqlservercentral.com/scripts/Stored+Procedures/70363/

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.
You can check my BLOG [font="Arial Black"]here[/font][/url][/right]

KELightsey Newbie Points: 9 More actions · Answer 6

"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.