2009. 11. 17. 14:32
MSSQL
/******************************************************************************************
저장 프로시저 정보
*******************************************************************************************/
SELECT
CASE WHEN nType = 0 THEN A.name ELSE '' END ProcName
, CASE WHEN nType = 1 THEN b.name ELSE '' END ParaName
, C.name +
CASE WHEN UPPER(C.name) IN ('CHAR','NCHAR','VARCHAR','NVARCHAR') THEN '(' +
CONVERT(VARCHAR(8), B.max_length) + ')'
ELSE ''
END TypeName
, REPLACE(D.Definition, char(10) + char(13) , char(10) + char(13) ) Script
FROM (
SELECT object_id, name, nType
FROM sys.objects CROSS JOIN (SELECT 0 nType UNION ALL SELECT 1) B) A
LEFT OUTER JOIN sys.sql_modules D
ON A.object_id = D.object_id
AND A.nType = 0
LEFT OUTER JOIN sys.parameters B
ON A.object_id = B.object_id
AND A.nType = 1
LEFT OUTER JOIN sys.types C
ON B.system_type_id = C.system_type_id
AND B.user_type_id = C.user_type_id
WHERE OBJECTPROPERTY(A.object_id, 'IsProcedure') = 1
AND OBJECTPROPERTY(A.object_id, 'IsMSShipped') = 0
AND CASE WHEN nType = 1 THEN b.name ELSE '' END IS NOT NULL
ORDER BY A.name, nType, ISNULL(B.parameter_id, 0)
CASE WHEN nType = 0 THEN A.name ELSE '' END ProcName
, CASE WHEN nType = 1 THEN b.name ELSE '' END ParaName
, C.name +
CASE WHEN UPPER(C.name) IN ('CHAR','NCHAR','VARCHAR','NVARCHAR') THEN '(' +
CONVERT(VARCHAR(8), B.max_length) + ')'
ELSE ''
END TypeName
, REPLACE(D.Definition, char(10) + char(13) , char(10) + char(13) ) Script
FROM (
SELECT object_id, name, nType
FROM sys.objects CROSS JOIN (SELECT 0 nType UNION ALL SELECT 1) B) A
LEFT OUTER JOIN sys.sql_modules D
ON A.object_id = D.object_id
AND A.nType = 0
LEFT OUTER JOIN sys.parameters B
ON A.object_id = B.object_id
AND A.nType = 1
LEFT OUTER JOIN sys.types C
ON B.system_type_id = C.system_type_id
AND B.user_type_id = C.user_type_id
WHERE OBJECTPROPERTY(A.object_id, 'IsProcedure') = 1
AND OBJECTPROPERTY(A.object_id, 'IsMSShipped') = 0
AND CASE WHEN nType = 1 THEN b.name ELSE '' END IS NOT NULL
ORDER BY A.name, nType, ISNULL(B.parameter_id, 0)
