Originally posted by Coolberg
With "data dictionary", do you mean the system tables? I *think* you can't find a default definition like that without examining the SP's definition in system tables.
Yes, the data dictionary is composed of all system tables (sysobjects,syscolums...). You can find a definition for a table or stored proc by quering sysobject,syscolumns, and systypes, but I do not know where the default values are stored in the system tables.
Re: Where in the Data Dictionary can I find a parameter default value?
Q1 Does any know how to get the DEFAULT value defined by a stored procedure?
CREATE PROCEDURE MyProc( @MyParam VARCHAR(1) = 'A' )
Q2 Where in the data dictionary can I find the default value for @MyParam?
A1 One answer is: Code an informational feature into each user stored procedure; another (bad) answer is: Create a user proc that attempts to correctly read and interpet system tables for the information.
A2 I don't think that information is in the SQL92 standard system catalog INFORMATION_SCHEMA views (data dictionary). Check INFORMATION_SCHEMA.ROUTINES and ROUTINE_COLUMNS to be sure.
In Sql Server 2k, the syscomments system table currently should have a version of user stored procedure text, including parameters and any defaults, in the text column. In future versions, it may be anywhere!
The approach I've most often implemented has been to include code within stored procedures that returns such information (and more) to users (mainly developers) within each procedure. Via, for example:
exec MyProc @DisplayInfo = 'help'
which would display: usage notes, parameter default information, etc., etc., .
A few versions ago I considered implementing a stored proc designed to provide default and other information for user created stored procs by quering sysprocedures and other system tables (fortunately, it was never implemented into any production environments).
In the past some had / have hoped that Default type objects would be made usable and / or bindable to stored procedure parameters in subsequent Sql Server versions (that has not happened, but if it had, one could use exec sp_help 'MyProcDefaultXYZ' to return the default value of the 'MyProcDefaultXYZ' Default object). The advantage of such a feature would have been that stored proc defaults could then be changed without needing access / rights to each stored proc using them.