Posted inSQL
Microsoft
18 years ago
Populate has_default_value in sys.parameters
As we all know, T-SQL stored procedure parameter defaults are not stored in sys.parameters, allparameters, and systemparameters. They are also not exposed through spsproccolumns, sys.columns, or spprocedureparams_rowset.
Declined
SQL OSSuggestionsDeclined
Company Response
Microsoft
Company Response
Up: 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, thiscolumn 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 RegardsAndrew[MSFT]<=-=Nov 13 2006 2:56PM=-=>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?
Vote
0 Comments