SQL Server 프로그래밍기능 파라미터 검색 쿼리

SQL Server 프로그래밍기능(Stored Procedure 등) 파라미터 검색 쿼리

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

GO

 

USE tempdb

GO

--SQLDBWook(sqldbwook@gmail.com)

SELECT PROC_NM             = object_name(PRM.object_id)

       ,      PARAM_NM     = PRM.name  

       ,      PARAM_TYPE   =            TYP.name                               

                                        +      CASE WHEN TYP.name IN ('nvarchar','nchar','varchar','char','varbinary','binary')

                                                            THEN   CASE WHEN PRM.max_length = -1

                                                                                       THEN '(max)'

                                                                                 WHEN TYP.name IN ('nvarchar','nchar')

                                                                                       THEN '(' + CAST(PRM.max_length/2 AS varchar(25)) + ')'

                                                                                       ELSE '(' + CAST(PRM.max_length   AS varchar(25)) + ')'

                                                                         END

                                                      WHEN TYP.name IN ('decimal','numeric')

                                                            THEN       '('+CAST(PRM.precision AS varchar(25))+','+CAST(PRM.scale AS varchar(25))+')'

                                                            ELSE   ''

                                               END                       

       ,      PARAM_ORDER = PRM.parameter_id         

       --,    *           

  FROM              sys.parameters      AS PRM

 INNER JOIN   sys.types           AS TYP

                           ON PRM.system_type_id = TYP.system_type_id

 WHERE object_name(PRM.object_id) LIKE 'USPX%'

 ORDER BY

             object_name(PRM.object_id)

       ,      PRM.parameter_id