SQL Server Stored Procedure Default Parameters and INFORMATION_SCHEMA

Published: 2015-12-04

David Edelstein

I’ve written a few different bits of custom code that look into INFORMATION_SCHEMA to find stored proc parameter names, types etc.  This is useful for code-gen or for tracking changes etc.  For some integration code generation, I needed to find if the sqlserver stored proc param had a default value or not.  If it had a default value and my application tried to send null, then I want to omit the parameter in the generated query. 

This piece of info is not in INFORMATION_SCHEMA.  There is an intriguing column in sys.parameters called ‘has_default_value’ which looks perfect.  However, according to the documentation, (and confirmed in my own application) this is always set to 0 unless the proc is a CLR object.

So, still needing a solution, I decided to parse out the arguments from the stored proc definition signature.  Basically, what this snippet will allow you to do is find the position in the argument list of the stored proc and take all the chars up until the next token(,) or token(as).  In my application, I only needed to test to see if the parameter contained an equals sign, but this query will actually return you the entire param snippet.  

SELECT 
	r.ROUTINE_SCHEMA,
	r.ROUTINE_NAME,
	substring(p.PARAMETER_NAME, 2, 100) as PARAMETER_NAME,
	p.ORDINAL_POSITION,
	p.DATA_TYPE,
	cast(substring(r.routine_definition, 
		charindex(p.PARAMETER_NAME, r.ROUTINE_DEFINITION), 
		case 
			when p.ORDINAL_POSITION is null then 
				charindex(p.PARAMETER_NAME, r.ROUTINE_DEFINITION)
			when p.ORDINAL_POSITION not IN (
				select max(pinner.ORDINAL_POSITION) 
				from INFORMATION_SCHEMA.PARAMETERS pinner
				where pinner.SPECIFIC_NAME=p.SPECIFIC_NAME and pinner.SPECIFIC_SCHEMA=p.SPECIFIC_SCHEMA
			) then 
				charindex(',', substring(r.routine_definition, charindex(p.PARAMETER_NAME, r.ROUTINE_DEFINITION), 99))
			else 
				patindex('%as%', substring(r.routine_definition, charindex(p.PARAMETER_NAME, r.ROUTINE_DEFINITION), 99)) 
		end
		) as varchar(100)) as ParamSnippet

from INFORMATION_SCHEMA.ROUTINES r
left join INFORMATION_SCHEMA.PARAMETERS p on p.SPECIFIC_NAME=r.ROUTINE_NAME
where ROUTINE_TYPE='PROCEDURE'
order by r.ROUTINE_NAME, p.ORDINAL_POSITION

Here is a test stored proc and its output:

create procedure dbo.TestDefaultParams
	@required1 int,
	@optional1 int=default,
	@optional2 int=9999,
	@optional3 varchar(10) = 'hellooooo',
	@required2 varchar(10)
as
	select @required1, @optional1, @optional2, @optional3, @required2
go

With a bit more script you could clean up the result snippet even more! Or just do any further parsing in your application