测试环境:SQL SERVER 2005
SQL语句
declare @SchemaName nvarchar(50)
set @SchemaName='构架名称,如DBO'
declare @CommandName nvarchar(50)
set @CommandName='存储过程名称'
SELECT
DB_NAME() AS [PROCEDURE_CATALOG],
@SchemaName AS [PROCEDURE_SCHEMA],
NULL AS [PROCEDURE_NAME],
'@RETURN_VALUE' AS [PARAMETER_NAME],
0 AS [ORDINAL_POSITION],
CAST(4 AS smallint) AS [PARAMETER_TYPE],
0 AS [PARAMETER_HASDEFAULT],
NULL AS [PARAMETER_DEFAULT],
CAST(0 AS bit) AS [IS_NULLABLE],
0 AS [DATA_TYPE],
NULL AS [CHARACTER_MAXIMUM_LENGTH],
NULL AS [CHARACTER_OCTET_LENGTH],
CAST(10 AS smallint) AS [NUMERIC_PRECISION],
CAST(NULL AS smallint) AS [NUMERIC_SCALE],
NULL AS [DESCRIPTION],
'int' AS [TYPE_NAME],
'int' AS [LOCAL_TYPE_NAME]
UNION ALL
SELECT
DB_NAME() AS [PROCEDURE_CATALOG],
SCHEMA_NAME(sp.schema_id) AS [PROCEDURE_SCHEMA],
NULL AS [PROCEDURE_NAME],
param.name AS [PARAMETER_NAME],
param.parameter_id AS [ORDINAL_POSITION],
CAST(CASE WHEN param.is_output = 1 THEN 2 ELSE 1 END AS smallint) AS [PARAMETER_TYPE],
0 AS [PARAMETER_HASDEFAULT],
NULL AS [PARAMETER_DEFAULT],
CAST(1 AS bit) AS [IS_NULLABLE],
0 AS [DATA_TYPE],
CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar') AND param.max_length <> -1 THEN param.max_length/2 ELSE param.max_length END AS int) AS [CHARACTER_MAXIMUM_LENGTH],
NULL AS [CHARACTER_OCTET_LENGTH],
CAST(param.precision AS smallint) AS [NUMERIC_PRECISION],
CAST(param.scale AS smallint) AS [NUMERIC_SCALE],
NULL AS [DESCRIPTION],
ISNULL(baset.name, N'') AS [TYPE_NAME],
ISNULL(baset.name, N'') AS [LOCAL_TYPE_NAME]
FROM
sys.all_objects AS sp
INNER JOIN sys.all_parameters AS param ON param.object_id=sp.object_id
LEFT OUTER JOIN sys.types AS baset ON baset.user_type_id = param.system_type_id and baset.user_type_id = baset.system_type_id
WHERE
(sp.type = N'P' OR sp.type = N'RF' OR sp.type='PC')and(sp.name=@CommandName and SCHEMA_NAME(sp.schema_id)=@SchemaName)
ORDER BY
5 ASC
修改架构名称和存储过程名称后执行结果类似
ETMCDB dbo NULL @RETURN_VALUE 0 4 0 NULL 0 0 NULL NULL 10 NULL NULL int int
ETMCDB dbo NULL @OrderId 1 1 0 NULL 1 0 4 NULL 10 0 NULL int int
ETMCDB dbo NULL @DetailId 2 1 0 NULL 1 0 4 NULL 10 0 NULL int int
内容摘自
