출처 : 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
'MS-SQL' 카테고리의 다른 글
| 기본설정된 DB가 문제가 발생되었을 때.. (0) | 2015.12.10 |
|---|---|
| MDF 파일만 남아있는 상태에서 복원하기 (0) | 2015.11.24 |
| [펌]MS SQL 2000 에서 정보를 보는 쿼리들 (0) | 2015.07.18 |
| OBJECT_ID를 이용한 객체유형 확인 (0) | 2015.04.02 |
| MS-SQL2000 sa 계정삭제 (0) | 2015.01.22 |
