Oracle의 USER_SOURCE 딕셔너리뷰와 유사한 기능을 하는 명령어입니다.
아래의 명령어를 사용해서 소스를 확인할 수 있습니다.
명령어
sp_helptext [프로시저 명];
ex) sp_helptext TableComment;
CREATE PROC [dbo].[TableComment]
(
@P_TableName VARCHAR(200) -- 테이블명
)
AS
SET NOCOUNT ON
BEGIN
DECLARE @TableComment VARCHAR(200)
SET @TableComment = ''
SELECT
@TableComment = CONVERT(VARCHAR(200), value)
FROM ::fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', @P_TableName, default, default);
SELECT
A.name AS [테이블 물리명]
, @TableComment AS [테이블 논리명]
, B.name AS [컬럼 물리명]
, E.value AS [컬럼 논리명]
, ', ' + B.name AS [코딩용 컬럼 물리명]
, 'AS ' + B.name AS [코딩용 AS]
, '-- ' + CONVERT(VARCHAR(100), E.value) AS [주석용 컬럼 논리명]
, UPPER(C.name) AS [데이터 타입]
, (
CASE C.NAME
WHEN 'VARCHAR' THEN '(' + CONVERT(VARCHAR, B.prec) + ')'
WHEN 'NVARCHAR' THEN '(' + CONVERT(VARCHAR, B.prec) + ')'
WHEN 'CHAR' THEN '(' + CONVERT(VARCHAR, B.prec) + ')'
WHEN 'DECIMAL' THEN '(' + CONVERT(VARCHAR, B.prec) + ',' + CONVERT(VARCHAR, B.scale) + ')'
WHEN 'NUMERIC' THEN '(' + CONVERT(VARCHAR, B.prec) + ',' + CONVERT(VARCHAR, B.scale) + ')'
ELSE ''
END
) AS [길이]
, (
CASE
WHEN B.isnullable = 1 THEN ''
ELSE 'NOT NULL'
END
) AS NullAble
, ISNULL('PK' + CONVERT(VARCHAR, PKInfo.keyno), '') AS PK_No
FROM
sysobjects AS A WITH (NOLOCK)
LEFT JOIN syscolumns AS B WITH (NOLOCK)
ON
A.id = B.id
LEFT JOIN systypes AS C WITH (NOLOCK)
ON
B.xtype = C.xtype
LEFT JOIN sys.objects AS D WITH (NOLOCK)
ON
A.id = D.object_id
LEFT JOIN sys.extended_properties AS E WITH (NOLOCK)
ON
D.object_id = E.major_id
AND B.colid = E.minor_id
LEFT OUTER JOIN Sysindexkeys AS PKInfo
ON
A.id = PKInfo.id
AND B.colid = PKInfo.colid
AND PKInfo.indid = 1
WHERE
A.xtype = 'U'
AND A.name LIKE @P_TableName
AND C.name <> 'sysname'
ORDER BY
A.name
, B.colorder
;
END
Oracle의 USER_SOURCE와 비교
ex )
SELECT *
FROM USER_SOURCE
WHERE
NAME='AW_DROP_PROC'
AW_DROP_PROC PROCEDURE 1 PROCEDURE aw_drop_proc
AW_DROP_PROC PROCEDURE 2 (obj_type IN VARCHAR2, obj_name IN VARCHAR2, obj_owner IN VARCHAR2)
AW_DROP_PROC PROCEDURE 3 AS
AW_DROP_PROC PROCEDURE 4 DBERR20 EXCEPTION;
AW_DROP_PROC PROCEDURE 5 PRAGMA EXCEPTION_INIT(DBERR20, -33295);
AW_DROP_PROC PROCEDURE 6 HASCOT EXCEPTION;
AW_DROP_PROC PROCEDURE 7 PRAGMA EXCEPTION_INIT(HASCOT, -37409);
AW_DROP_PROC PROCEDURE 8 HASOLP EXCEPTION;
AW_DROP_PROC PROCEDURE 9 PRAGMA EXCEPTION_INIT(HASOLP, -37163);
AW_DROP_PROC PROCEDURE 10
...생략
'다른 DBMS > MSSQL' 카테고리의 다른 글
테이블 메타데이터,데이터 스크립트 추출 (0) | 2021.04.06 |
---|---|
다른테이블과 JOIN해서 UPDATE하기 (0) | 2021.03.12 |
테이블명 변경,컬럼명 변경, 컬럼 추가 (0) | 2021.02.25 |
Table,Column Comment 확인 쿼리 (0) | 2021.02.23 |
테이블 용량 확인 쿼리 (0) | 2021.02.10 |
댓글