May 15, 2015 at 9:21 am
I'm trying to query for a list of our objects that have parameters defined with default values; using sys.parameters, I thought columns has_default_value and default_value would contain the info I needed. However, after seeing all 0 / NULL values (and knowing I had many defined default values), I checked:
https://msdn.microsoft.com/en-us/library/ms176074(v=sql.100).aspx
which states "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."
I have no intention of "parsing out" the parameter default value from the object definition; I do notice that Object Explorer indicates whether a parameter is or is not a Default (I'm curious how it gets that info -- I hope it isn't by parsing out).
Any ideas, info, scripts, etc. would be appreciated.
Thanks,
~ Jeff
May 15, 2015 at 12:04 pm
Take a look at 'sp_columns' (for tables) and 'sp_sproc_columns' (for stored procedures) in BOL. Both are system stored procedures and they might be what you are looking for.
In BOL, the general heading for these procs is 'Catalog Stored Procedures.' There are lots of others available.
HTH,
Sigerson
"No pressure, no diamonds." - Thomas Carlyle
May 15, 2015 at 12:45 pm
I generally haven't much luck with the SQL Server sp's; I presume they act on the same data I can find in the system views / functions, but never what I really want (best example: sp_depends, which only gives me one level "upward", when I really want all levels -- and so have to code a really cumbersome recursive CTE that often runs over the max recursion level).
The sys.sp_sproc_columns procedure (seems to) return the parameter definitions but nothing more than the sys.parameters view does (but interesting that on my 2008 server, there are output columns NULLABLE (0/1) and IS_NULLABLE (YES/NO) ... gotta love it!)
~ Jeff
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply