SQLINFO – We play with data and technology

Recently, One of my friend Ria Rajan had a Question, The Question is can we find the Parameters of a compiled procedure? I had built a query for her to get the details; I think it will help all of us, mostly developers.

--Creating a sample procedure

CREATE PROC Usp_test (@id INT)
AS
  BEGIN
      SELECT @id;
  END  

GO 

--Query to get the parameters of the sample Procedure

SELECT p.name,
       t.name,
       p.max_length
FROM   sys.all_parameters p
       INNER JOIN sys.types t
         ON p.user_type_id = t.user_type_id
WHERE  p.object_id = Object_id('usp_test')  -- Procedure Name

Result: