SQL Server: sp_help with Default Values

Question

Is there an alternative to sp_help that includes the default values for parameters?

Answer 1

I did expect the default to be available in sys.all_parameters, and in fact that view has a default_value column. But unfortunately it's not used for T-SQL stored procedures. MSDN says:

SQL Server only maintains default values for CLR objects in this catalog view; therefore, this column will always have 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.

So it appears the only way to retrieve the default value of a stored procedure parameter is to parse the object definition.

Answer 2

I believe the only built-in method is to use sp_helptext to view the whole procedure text.

Answer 3

This article might be useful, it actually provides sample code to parse the routine definitions to determine the default parameters.

I would expect that this would be addressed in a future release - it's a significant gap in the metadata, and there's no real reason it shouldn't be provided.

Another possibility is to regularly parse the procs and tag the parameters with extended properties yourself - extended properties are far easier to query against and you can use them to self-document the database:

USE [SandBox]
GO

/****** Object:  StoredProcedure [dbo].[usp_Test]    Script Date: 04/01/2010 20:23:11 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[usp_Test]
@input INT = 0
AS BEGIN
SELECT @input AS [output]
END
GO

EXEC sys.sp_addextendedproperty @name=N'TestXP', @value=N'testing' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'usp_Test', @level2type=N'PARAMETER',@level2name=N'@input'
GO