一个用于返回所有存储过程及其参数的T-SQL

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

SET

 

 

NOCOUNT

ON

------the max length of the parameters, using to format parameters-----------

DECLARE

 

 

@MaxParamLen

INT

SET

 

 

@MaxParamLen =

20

-----display parameters in upper case or not-----------------------------

DECLARE

 

 

@UpperParamType

BIT

SET

 

 

@UpperParamType =

1

--------the table variable to save stored procedures with parameters------------------------

DECLARE

 

 

@SPTable

TABLE

(

 

 

Id INT IDENTITY PRIMARY KEY ,

SPName

 

SYSNAME

,

ParameterName

 

SYSNAME

,

ParameterType

 

SYSNAME

,

Parameterlength

 

INT

)

 

---------insert stored procedures with parameters---------------------------------

INSERT

 

 

INTO @SPTable ( SPName , ParameterName , ParameterType , Parameterlength

)

SELECT

 

 

o . Name , CHAR ( 9 )+ p . Name , t . Name , p .

max_length

FROM

 

 

sys.objects

o

 

 

INNER JOIN sys.parameters p ON o . object_id = p .

object_id

 

 

INNER JOIN sys.types t ON p . system_type_id = t .

user_type_id

WHERE

 

 

Type =

'P'

ORDER

 

 

BY o .

Name

-------------set max parameter length-----------------------------

SET

 

 

@MaxParamLen =( SELECT MAX ( LEN ( LTRIM ( RTRIM ( ParameterName )))) FROM @SPTable )+

2

-----------set parameter type to upper case-------------------------------------

IF

 

 

@UpperParamType =

1

UPDATE

 

 

@SPTable

SET

 

 

ParameterType = UPPER ( ParameterType

)

-------------------set the length of --NCHAR and NVARCHAR to its half --------------------------------------

UPDATE

 

 

@SPTable

SET

 

 

Parameterlength = Parameterlength /

2

WHERE

 

 

ParameterType in ( 'nchar' , 'nvarchar'

)

-----------set parameter length to null for int type and so on-------------------------------------

UPDATE

 

 

@SPTable

SET

 

 

Parameterlength

=null

WHERE

 

 

ParameterType in ( 'binary' , 'varbinary' , 'int' , 'bigint' , 'samllint' , 'bit' , 'image' , 'ntext' , 'uniqueidentifier' , 'datetime' , 'smalldatetime'

)

------------add length to parameter type ---------------------------------

UPDATE

 

 

@SPTable

SET

 

 

ParameterType = ParameterType

+

case

 

 

when Parameterlength IS NULL THEN ',' ELSE '(' + CONVERT ( VARCHAR ( 5 ), Parameterlength )+ '),' END

-------------add space after parameter name to format parameters------------------------------------

UPDATE

 

 

@SPTable

SET

 

 

ParameterName = ParameterName + REPLICATE ( ' ' , @MaxParamLen - LEN ( ParameterName

))

WHERE

 

 

LEN ( ParameterName )<

@MaxParamLen

-------------remove the comma from the last parameter--------------------------------------------------

UPDATE

 

 

sp

SET

 

 

ParameterType = SUBSTRING ( ParameterType , 1 , LEN ( ParameterType )- 1

)

FROM

 

 

@SPTable sp

 

 

INNER JOIN ( SELECT SPName , MAX ( Id )

Id

 

 

FROM

@SPTable

 

 

GROUP BY SPName )

lp

 

 

ON sp . Id = lp .

Id

-------------add '(' to the first parameter------------------------------------------------------------------------

UPDATE

 

 

sp

SET

 

 

ParameterName = '(' + CHAR ( 13 )+

ParameterName

FROM

 

 

@SPTable sp

 

 

INNER JOIN ( SELECT SPName , MIN ( Id )

Id

 

 

FROM

@SPTable

 

 

GROUP BY SPName )

lp

 

 

ON sp . Id = lp .

Id

 

-------------add ')' to the last parameter------------------------------------------------------------------------

UPDATE

 

 

sp

SET

 

 

ParameterType = ParameterType + CHAR ( 13 )+

')'

FROM

 

 

@SPTable sp

 

 

INNER JOIN ( SELECT SPName , MAX ( Id )

Id

 

 

FROM

@SPTable

 

 

GROUP BY SPName )

lp

 

 

ON sp . Id = lp .

Id

---------------clear the repeated stored procedure names from @SPTable--------------------------------------

UPDATE

 

 

sp

SET

 

 

SPName =

''

FROM

 

 

@SPTable sp

 

 

INNER JOIN ( SELECT SPName , MIN ( Id )

Id

 

 

FROM

@SPTable

 

 

GROUP BY SPName )

fp

 

 

ON sp . SPName = fp . SPName and sp . Id > fp .

Id

-------------add 'Tab' to the beginning of parameter names-------------------------------------------------------

UPDATE

 

 

sp

SET

 

 

SPName = CHAR ( 13 )+

SPName

FROM

 

 

@SPTable sp

WHERE

 

 

SPName <>

''

--------show the result of stored procedures-it is recommanded to show the result in text mode---------------------

SELECT

 

 

SPName + ParameterName +

ParameterType

FROM

 

 

@SPTable