MS SQL。주석(Description)

1. 기본

 

# 단일

 

--내용

 

# 문장

 

/*
	내용
*/

 

2. 테이블(Table)

 

# 추가

 

EXEC sp_addextendedproperty 'MS_Description', '테이블 주석', 'user', dbo, 'table', [테이블명]

 

# 수정

 

EXEC sp_updateextendedproperty 'MS_Description', '테이블 주석', 'user', dbo, 'table', [테이블명]

 

# 조회

 

SELECT NAME,
      (SELECT TOP 1 VALUE FROM SYS.EXTENDED_PROPERTIES WHERE MAJOR_ID = A.ID AND MINOR_ID = 0) COMMENT,
       CRDATE
  FROM SYSOBJECTS A
 WHERE RTRIM(A.XTYPE) = 'U' AND NAME = [테이블명]

 

3. 컬럼(Column)

 

# 추가

 

EXEC sp_addextendedproperty 'MS_Description', '컬럼 주석', 'user', dbo, 'table', [테이블명], 'column', [컬럼명]

 

# 수정

 

EXEC sp_updateextendedproperty 'MS_Description', '컬럼 주석', 'user', dbo, 'table', [테이블명], 'column', [컬럼명]

 

4. 뷰(View)

 

# 추가

 

EXEC sp_addextendedproperty @name=N'MS_Description', @value=N'뷰 주석' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'[뷰명]'

 

# 수정

 

EXEC sp_updateextendedproperty @name=N'MS_Description', @value=N'뷰 주석' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'[뷰명]'

 

# 뷰 컬럼 주석 추가

 

EXEC sp_addextendedproperty @name=N'MS_Description', @value=N'컬럼 주석' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'[뷰명]', @level2type=N'COLUMN',@level2name=N'[컬럼명]'

 

# 뷰 컬럼 주석 수정

 

EXEC sp_updateextendedproperty @name=N'MS_Description', @value=N'컬럼 주석' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'[뷰명]', @level2type=N'COLUMN',@level2name=N'[컬럼명]'

 

5. 프로시저(Procedure)

 

# 추가

 

EXEC sp_addextendedproperty @name=N'MS_Description', @value=N'프로시저 주석' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'[프로시저명]'

 

# 수정

 

EXEC sp_updateextendedproperty @name=N'MS_Description', @value=N'프로시저 주석' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'[프로시저명]'