一个用于返回所有存储过程及其参数的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