Populate has_default_value in sys.parameters
As we all know, T-SQL stored procedure parameter defaults are not stored in sys.parameters, all_parameters, and system_parameters. They are also not exposed through sp_sproc_columns, sys.columns, or sp_procedure_params_rowset.
However, Management Studio has no problems at least indicating whether or not a default value exists (though not the actual value) for a specific parameter (this is new in 2005; Query Analyzer did not show this). So I know that the information can be retrieved somehow, whether it is parsing the text in sys.sql_modules.definition, or using spt_sproc_columns_odbc_view, or some internal mechanism invisible to profiler.
This is not an overly common request, but it happens enough in the community to spur me to submit this feedback. For the most recent thread about it, see:
AdminMicrosoft SQL Server
(Product Manager, Microsoft Azure)
shared this idea
Upvotes: 62
<=-=Nov 13 2006 2:23PM=-=>HI Tim,
As posted by Tibor Karaszi, BOL document that “SQL Server only maintains default values for CLR objects in this catalog view; therefore, this
column 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.”
We dont store even the bit that indicating parameter is of default value in Yukon.
Thanks for the feedback, we will look into addressing this more completely in future releases.
Best Regards
Andrew
[MSFT]
Hi Andrew, this is Aaron not Tim. If you are not gong to fix these omissions from sys.parameters, then could you at least share/publish the code that Management Studio uses to parse syscomments.text, since it is fully capable of showing Default or No Default in Object Explorer (see attachment). I think this would be useful in order to prevent people who want this functionality from re-inventing the wheel.
<=-=Nov 6 2009 11:28AM=-=>Andrew from Microsoft,
Your response on this issue is simply not acceptable. You need to be aware that this metadata is accessed and used programatically, and you should never expect your customers to parse scripts to get values that should be exposed in structured metadata. Saying you will look into addressing the issue in future releases, and then doing nothing for three years is not the right answer. This should be a simple problem to fix, and you should release update for it quickly.
<=-=Feb 21 2010 9:05PM=-=>And whatever solution you come up with SHOULD NOT USE cursors. Can it be done? Yes. I could do it without cursors – for a fee…….
<=-=Feb 22 2010 12:31AM=-=>Update: I just finished it tonight and put it to use. NO CURSORS!!!
<=-=Jun 4 2015 4:34AM=-=>So, 9 years later this is still not working?