출처 : http://abacus.tistory.com/category/SQL?page=1
참고 : MS-SQL 2005 버젼부터 사용가능
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)
---------------------------------------------------------------------------------------------------------------
DECLARE @SqlVersion AS CHAR(4)
DECLARE @maxi AS
INT , @maxj AS INT
DECLARE @i AS
INT , @j AS INT
DECLARE @Output VARCHAR(4000),@description VARCHAR(4000)
CREATE TABLE
#Tables (id int identity(1, 1), Object_id int, name varchar(155), type varchar(20), [definition] varchar(MAX))
CREATE TABLE
#Parameters (id
int identity(1,1), name varchar(155), type Varchar(155))
--SQL 버전뽑기
IF (substring(@@VERSION, 1, 25 ) = 'Microsoft SQL Server 2005')
SET
@SqlVersion = '2005'
ELSE IF (substring(@@VERSION, 1, 26 ) = 'Microsoft SQL Server
2000')
SET
@SqlVersion = '2000'
ELSE
SET
@SqlVersion = '2005'
--웹타이틀
Print '<html>'
Print '<head>'
Print '<title>::'
+ DB_name() + '::</title>'
--스타일
PRINT '<style>'
PRINT ' body {'
PRINT ' font-family:verdana;'
PRINT ' font-size:9pt;'
PRINT ' }'
PRINT ' td {'
PRINT ' font-family:verdana;'
PRINT ' font-size:9pt;'
PRINT ' }'
PRINT ' th {'
PRINT ' font-family:verdana;'
PRINT ' font-size:9pt;'
PRINT ' background:#d3d3d3;'
PRINT ' }'
PRINT ' table'
PRINT ' {'
PRINT ' background:#d3d3d3;'
PRINT ' }'
PRINT ' tr'
PRINT ' {'
PRINT ' background:#ffffff;'
PRINT ' }'
PRINT ' </style>'
PRINT '</head>'
PRINT '<body>'
SET NOCOUNT
ON
IF @SqlVersion = '2000'
BEGIN
INSERT
INTO #Tables (Object_id, name, type, [definition]) VALUES (1,1,1,1) --2000 없어서패수
END
ELSE IF
@SqlVersion = '2005'
BEGIN
INSERT
INTO #Tables (Object_id, name, type, [definition])
SELECT
OJ.object_Id,'[' + S.name + '].[' + OJ.name + ']' AS name
,CASE WHEN type = 'V' THEN 'View' WHEN type = 'U' THEN 'Table' WHEN type = 'P' THEN 'PROCEDURE' END,SM.definition
FROM
sys.sql_modules AS SM
JOIN
sys.objects AS OJ ON SM.object_id = OJ.object_id
LEFT
OUTER JOIN sys.schemas AS S ON OJ.schema_id = S.schema_id
WHERE
OJ.type = 'P' AND is_ms_shipped = 0
END
SET @maxi =
@@rowcount
--SP 리스트
PRINT '<table
border="0" cellspacing="0" cellpadding="0"
width="550px" align="center"><tr><td
colspan="3"
style="height:50;font-size:14pt;text-align:center;"><a
name="index"></a><b>Procedure
Index</b></td></tr></table>'
PRINT '<table
border="0" cellspacing="1" cellpadding="0" width="550px"
align="center"><tr><th>Sr</th><th>Object</th><th>Type</th></tr>'
SET @i =
1
WHILE(@i <= @maxi)
BEGIN
SELECT @output = '<tr><td
align="center">' + Cast((@i) as varchar) + '</td><td><a
href="#' + type
+ ':' + name + '">' + name + '</a></td><td>'
+ type + '</td></tr>'
FROM
#Tables WHERE id =
@i
PRINT
@Output
SET
@i = @i + 1
END
PRINT '</table><br
/>'
--SP 쿼리문
PRINT '<table
border="0" cellspacing="0" cellpadding="0" width="750px"
style="table-layout:fixed;"><tr><td><b>Description</b></td></tr><tr><td>'
+ isnull(@description, '') + '</td></tr></table><br
/>'
SET @i =
1
WHILE(@i <= @maxi)
BEGIN
--table
header
SELECT
@Output =
'<tr><th
align="left"><a name="' +
type + ':' + name + '"></a><b>'
+ type + ':' + name + '</b></th><td align="right"><a
href="#index">Index</a></td></tr>'
,
@description = [definition]
FROM
#Tables WHERE id =
@i
PRINT
'<br /><br /><br /><table
border="0" cellspacing="0" cellpadding="0"
width="750px"><tr><td
align="right"></td></tr>'
PRINT
@Output
PRINT
'</table><br />'
--Parameters정보테이블넣기
TRUNCATE
table #Parameters
IF
@SqlVersion = '2000'
BEGIN
PRINT '' --2000
없어서패수
END
ELSE
IF @SqlVersion =
'2005'
BEGIN
INSERT INTO #Parameters (Name, Type)
SELECT AP.name,type_name(user_type_id) +
CASE WHEN (type_name(user_type_id) = 'varchar' or type_name(user_type_id) = 'nvarchar' or type_name(user_type_id) ='char' or type_name(user_type_id) ='nchar')
THEN '(' + cast(max_length AS VARCHAR) + ')'
WHEN type_name(user_type_id) = 'decimal'
THEN '(' + cast([precision] AS VARCHAR) + ',' + cast(scale AS VARCHAR) + ')'
else ''
end
FROM sys.all_parameters AP
INNER JOIN #Tables T on T.object_id = AP.object_id
WHERE t.id = @i
END
SET
@maxj =
@@rowcount
SET
@j = 1
PRINT
'<table border="0"
cellspacing="0" cellpadding="0"
width="750px"><tr><td><b>Procedure
Prameters</b></td></tr></table>'
PRINT
'<table border="0"
cellspacing="1" cellpadding="0"
width="750px"><tr><th>Sr.</th><th>Name</th><th>Datatype</th></tr>'
WHILE(@j <= @maxj)
BEGIN
SELECT @Output = '<tr><td width="20px"
align="center">' + Cast((@j) AS varchar) + '</td><td
width="150px">' + isnull(name,'') + '</td><td
width="150px">' + upper(isnull(type,'')) + '</td></tr>'
from #Parameters
where id =
@j
PRINT @Output
SET @j = @j + 1;
END
PRINT
'</table><br />'
--Parameters정보테이블끝
--SP
쿼리
PRINT
'<table border="0"
cellspacing="0" cellpadding="0"
width="750px"><tr><td><b>Description</b></td></tr><tr><td
style="word-break:break-all;"><pre>' + isnull(@description, '') + '</pre></td></tr></table><br
/>'
--SP
쿼리끝
SET
@i = @i + 1
END
--Parameters정보테이블
SET @i =
1
WHILE(@i <= @maxi)
BEGIN
SET
@i = @i + 1
END
DROP Table
#Tables
DROP Table
#Parameters
