테이블 정의서 자동으로 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