How to find data types of parameters used in stored procedures

Problem:

Finding all stored procedures which use parameters of XML datatypes or specific datatype all in one place.

Solution:

sys.parameters, Starting with SQL 2008 or higher

This object catalog view stores a row for each parameter of an object that accepts parameters. If the object is a scalar function, there is also a single row describing the return value. That row will have a parameter_id value of 0.

for example, I wanted to change all the XML parameters to table type parameters. I would have to go through each and every procedure to find out where all we have XML type parameters. Instead this script would do the job for us. Similarly you can change the type in the search criteria to what so ever you want to look for.

--List of all Stored Procedure  paramaters and datatype details

select  sp.name,

   'Parameter_name' = param.name, 

   'Type'   = type_name(param.user_type_id),

   'Length'   = param.max_length, 

   'Prec'   = case when type_name(param.system_type_id) = 'uniqueidentifier'

              then precision 

              else OdbcPrec(param.system_type_id, param.max_length, param.precision) end, 

   'Scale'   = OdbcScale(param.system_type_id, param.scale), 

   'Param_order'  = param.parameter_id, 

   'Collation'   = convert(sysname,

                   case when param.system_type_id in (35, 99, 167, 175, 231, 239) 

                   then ServerProperty('collation') end) 

from sys.parameters param INNER JOIN sys.procedures sp on sp.object_id= param.object_id

 where type_name(param.user_type_id)='XML'