농부지기

[ MS Sql - Procedure 내용 검색 ]

1. 정의

    - Procedure 내용 검색

[ MS Sql - 현재 수행중인 Procedure 조회 ]

1. 정의 : db단에서 현재 수행중인 Procedure찾기 

[ MS Sql - System Table (Schema 정보)  ]

INFORMATION_SCHEMA. 을 기준으로 MS Sql Schema 정보를 볼 수 있다.

--컬럼정보

--테이블정보
select * --column_name, data_type, character_maximum_length, column_default from INFORMATION_SCHEMA.TABLES where table_name = 'table_name';

--View정보
select * --column_name, data_type, character_maximum_length, column_default from INFORMATION_SCHEMA.VIEWS;

--Procedure

SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%execute%' AND ROUTINE_TYPE='PROCEDURE' order by ROUTINE_NAME;

[ MS SQL.System Table-Procedure 목록보기 ]

SELECT * FROM INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_CATALOG = 'TUSA_A' AND SPECIFIC_NAME LIKE 'USP_USA_SO%' ;

select * from sys.objects;

Select ROUTINE_NAME
From INFORMATION_SCHEMA.ROUTINES
Where ROUTINE_TYPE='PROCEDURE' Order By ROUTINE_NAME

[ MS SQL.System Table-Comment 보기 ]

[컬럼 Comment보기 - 방법1]

   SELECT OBJTYPE, OBJNAME, NAME, VALUE
     FROM ::FN_LISTEXTENDEDPROPERTY(NULL, 'SCHEMA', 'dbo', 'TABLE', 'FD_GOMPERTZ_FCST_MST', 'COLUMN', DEFAULT)

[컬럼 Comment보기 - 방법2]

SELECT A.TABLE_NAME , C.VALUE AS 테이블명 , A.COLUMN_NAME AS 컬럼명 , A.DATA_TYPE AS 타입 , ISNULL(CAST(A.CHARACTER_MAXIMUM_LENGTH AS VARCHAR) , CAST(A.NUMERIC_PRECISION AS VARCHAR) + ',' + CAST(A.NUMERIC_SCALE AS VARCHAR)) AS 컬럼길이 , A.COLUMN_DEFAULT AS 디폴트 , A.IS_NULLABLE AS null여부 , B.VALUE AS 컬럼_주석 FROM INFORMATION_SCHEMA.COLUMNS A LEFT OUTER JOIN SYS.EXTENDED_PROPERTIES B ON B.major_id = object_id(A.TABLE_NAME) AND A.ORDINAL_POSITION = B.minor_id LEFT OUTER JOIN ( SELECT object_id(objname) AS TABLE_ID , VALUE FROM ::FN_LISTEXTENDEDPROPERTY(NULL, 'User','dbo','table', NULL, NULL, NULL) ) C ON object_id(A.TABLE_NAME) = C.TABLE_ID WHERE A.TABLE_NAME = 'SO_HDR' ORDER BY A.TABLE_NAME, A.ORDINAL_POSITION ;

[테이블 Comment보기 - 방법1]

   select name tablename,

           (select value from sys.extended_properties where major_id = a.id and minor_id = 0 ) comment
      from sysobjects a
   where rtrim(a.xtype) = 'U'
    order by 2 asc ;

[ MS SQL.System Table-목록 보기 ]