0

I was not able to find in which system view the default values of stored procedure are recorded. For example, if we have:

CREATE PROCEDURE [dbo].[...]
(
    @A BIGINT 
   ,@B TINYINT = 2
   ,@C VARCHAR(12) = 'test'
)
AS

Is there a place where I can see that parameter @A is required and/or @B and @C have default values?

gotqn
  • 42,737
  • 46
  • 157
  • 243
  • 2
    Before someone suggests this `sys.parameters` column `default_value` is only relevant for CLR procs not TSQL ones – Martin Smith Aug 09 '21 at 11:46
  • 1
    The default parameter value is not exposed by a catalog view. Maybe [this thread](https://stackoverflow.com/questions/47484834/get-default-value-of-stored-procedure-parameter) will help. – Dan Guzman Aug 09 '21 at 11:54
  • Does this answer your question? [Get default value of stored procedure parameter](https://stackoverflow.com/questions/47484834/get-default-value-of-stored-procedure-parameter) – Thom A Aug 09 '21 at 11:55
  • @DanGuzman and Larnu Thanks, it confirms there is no clear way to get them. – gotqn Aug 09 '21 at 11:59
  • They could be parsed from the procedure definition – SteveC Aug 09 '21 at 12:02
  • Does this answer your question? [Get default value of stored procedure parameter](https://stackoverflow.com/questions/47484834/get-default-value-of-stored-procedure-parameter) See also https://sqlperformance.com/2020/09/sql-performance/paramparser-1 – Charlieface Aug 09 '21 at 12:08

1 Answers1

0

You can find them here

select pa.* from sys.procedures pr 
join sys.parameters pa on pr.object_id = pa.object_id
Isparia
  • 682
  • 5
  • 20