MSSQL 테이블 명세서 자동 생성 - MSSQL teibeul myeongseseo jadong saengseong

테이블 정의서 자동으로 HTML으로 출력하는 쿼리

첨부된 파일을 실행하면 결과가 나오고 출력된 결과를 HTML로 만들면 자동으로 테이블 정의서가 생성되는 쿼리입니다.

DB_TABLE_TO_HTML_QUERY_Version_1.0.sql

0.02MB

▼  파일을 다운로드하고 다운로드한 sql을 실행시켜 주시면 됩니다.

▼ 출력된 메시지를 Ctrl + A 눌러서 전부 복사해줍니다.

▼ 메모장을 열어서 복사한 걸 붙여 넣기 해줍니다.

▼ 메모장을 html으로 저장해 줍니다.

▼ 저장한 스크립트를 더블클릭해서 열어 줍니다.

▼ 클릭해서 열어보면 테이블 명세서가 열리고 표시된 테이블을 클릭하면 상세를 볼 수 있습니다.

테이블명세서 쿼리 입니다. 

정부지원 산출문 자료에 사용하셔도 되고, 개발시 테이블스키마 참고할때 사용하면 유용하실거 같아요. 

Use 데이터베이스명
go
SELECT 
	 d.value 테이블명
	, a.TABLE_NAME 테이블ID
	, e.value 컬럼명
	, a.COLUMN_NAME	컬럼ID		
	, case when a.CHARACTER_MAXIMUM_LENGTH IS null then a.DATA_TYPE else a.DATA_TYPE + '(' + case when ltrim(str(a.CHARACTER_MAXIMUM_LENGTH)) = '-1' then 'max' else ltrim(str(a.CHARACTER_MAXIMUM_LENGTH)) end  + ')' end as DATATYPE	
	, case when a.column_name=isnull(b.column_name,'') and c.constraint_type='PRIMARY KEY' then 'Y' else ' ' end as PK
	, case when a.column_name=isnull(b.column_name,'') and c.constraint_type='FOREIGN KEY' then 'Y' else ' ' end as FK
	, case when a.IS_NULLABLE='YES' then 'Y' else '' end as NOT_NULL	
FROM INFORMATION_SCHEMA.COLUMNS a 
	LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE b 
		ON (a.table_name+a.column_name=b.table_name+b.column_name) 
	LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS c 
		ON (b.constraint_name=c.constraint_name) 
	LEFT JOIN ( SELECT OBJECT_ID(Objname) Table_Id,Value FROM ::FN_LISTEXTENDEDPROPERTY(NULL, 'user','dbo','Table',NULL, NULL, NULL) ) d 
		ON d.Table_id = OBJECT_ID(A.Table_Name)
	LEFT JOIN SYS.EXTENDED_PROPERTIES e 
		ON e.Major_Id = OBJECT_ID(A.Table_Name) 
		AND e.Minor_Id = A.Ordinal_Position 
WHERE a.TABLE_NAME = '테이블명'
ORDER BY a.TABLE_NAME, a.ORDINAL_POSITION;

1. 만든 이유
    INFORMATION_SCHEMA.COLUMNS 의 존재를 알게된 후 그냥 만들어 놓으면 편할 것 같아서.
    원래 테이블 명세서라는게 프로젝트 개발 전에 모두 완료되어 하지만.. 현실은 그렇지 않음.. 쿨럭.

2. 주의점
    화일 소스에 DB 접속정보가 있으니 보안에 조심조심..
    
3. 장점
   현재 DB 의 DB 명세서를 한방에 만들 수 있다.

4. 단점
    ms-sql 에서만 테스트 했슴. 다른 DB 는 모름.

5. 사용방법
    접속 정보 입력하고 실행만 하면 됨.

6. 사용된 쿼리구문..

select   

    t_columns.table_catalog,  

    t_columns.table_name,  

    t_columns.column_name,    

    t_columns.ordinal_position,  

    t_columns.column_default,    

    t_columns.is_nullable, 

    t_columns.data_type, 

    t_columns.character_maximum_length,  

    t_columns.is_nullable, 

    case when t_columns.column_name=isnull(t_column_usage.column_name,'') and t_table_const.constraint_type='PRIMARY KEY' then 'Y' else ' ' end as PK, 

    case when t_columns.column_name=isnull(t_column_usage.column_name,'') and t_table_const.constraint_type='FOREIGN KEY' then 'Y' else ' ' end as FK, 

    case when t_columns.column_name=isnull(t_column_usage.column_name,'') and t_table_const.constraint_type='FOREIGN KEY' then 

(SELECT 

PK_Table  = PK.TABLE_NAME

FROM 

INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C 

INNER JOIN 

INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK 

ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME 

INNER JOIN 

INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK 

ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME 

INNER JOIN 

INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU 

ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME 

INNER JOIN 

( 

SELECT 

i1.TABLE_NAME, i2.COLUMN_NAME 

FROM 

INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1 

INNER JOIN 

INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 

ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME 

WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY' 

) PT 

ON PT.TABLE_NAME = PK.TABLE_NAME and C.CONSTRAINT_NAME=t_table_const.CONSTRAINT_NAME

)

    else ' ' end as FK_table ,

    case when t_columns.column_name=isnull(t_column_usage.column_name,'') and t_table_const.constraint_type='FOREIGN KEY' then 

(SELECT 

PK_Column = PT.COLUMN_NAME

FROM 

INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C 

INNER JOIN 

INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK 

ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME 

INNER JOIN 

INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK 

ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME 

INNER JOIN 

INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU 

ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME 

INNER JOIN 

( 

SELECT 

i1.TABLE_NAME, i2.COLUMN_NAME 

FROM 

INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1 

INNER JOIN 

INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 

ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME 

WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY' 

) PT 

ON PT.TABLE_NAME = PK.TABLE_NAME and C.CONSTRAINT_NAME=t_table_const.CONSTRAINT_NAME

)

    else ' ' end as FK_column

  FROM 

    INFORMATION_SCHEMA.COLUMNS t_columns left outer join INFORMATION_SCHEMA.KEY_COLUMN_USAGE t_column_usage 

      on t_columns.TABLE_NAME+t_columns.column_name=t_column_usage.TABLE_NAME+t_column_usage.column_name  

    left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS t_table_const 

      on t_column_usage.constraint_name=t_table_const.constraint_name 

  order by   

     t_columns.table_name,t_columns.ordinal_position