Hi Steve,
Thanks for your enquiry. There's no particular reason why this should only be supported for Oracle. It's just that we haven't had any specific requests for other databases yet.
Indeed, unfortunately, the sys.parameters.has_default_value column is only populated for CLR procedures, not for Transact-SQL procedures, as it seems:
Of course, it's a good idea to add another vote to this feature here. Maybe we'll see it in a future SQL Server version:
As far as parsing goes, we already do some parsing in other databases where the dictionary views don't suffice, so I suspect that we can live with this hack. I've registered a feature request for this:
If you don't mind, I'd like to go through this together with you, as you probably have a couple of syntax corner cases in your database that we won't think of. The procedure definition syntax is documented here:
So, we have something like this syntax:
CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ]
[ { @parameter [ type_schema_name. ] data_type }
[ VARYING ] [ = default ] [ OUT | OUTPUT | [READONLY]
] [ ,...n ]
[ WITH <procedure_option> [ ,...n ] ]
[ FOR REPLICATION ]
AS ...
And...
CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type
[ = default ] [ READONLY ] }
[ ,...n ]
]
)
RETURNS
A couple of remarks:
- Even if the syntax for procedures omits parentheses to wrap parameters, they are allowed (and thus optional)
- Parameters can be clearly identified by their leading @ signs
- Defaulted parameters can be identified by an equal sign
- The last parameter is followed by:
o a closing parenthesis in functions followed by the RETURNS keyword. Data types can also have parentheses, though
o any of these keywords: WITH, FOR, AS possibly preceded by a closing parenthesis.
o since "default" can be a VARCHAR value, we must probably parse the values to ensure that we don't capture closing parenthesis or terminating keywords that are contained inside of those default values
Do you see any edge cases that fall outside of the remarks mentioned above?