티스토리 뷰
--테이블 목록 가져오기:
SELECT * FROM sysobjects WHERE xtype='U'
--뷰:
SELECT * FROM sysobjects WHERE xtype='V'
--프로시져:
SELECT * FROM sysobjects WHERE xtype='P'
--테이블 리스트
SELECT * FROM INFORMATION_SCHEMA.TABLES
--뷰 리스트
SELECT * FROM INFORMATION_SCHEMA.VIEWS
--컬럼 리스트
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
--컬럼 키값
SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
--프로시져
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
--프로시져 파라메타값
SELECT * FROM INFORMATION_SCHEMA.PARAMETERS
--컬럼 정보 가져오기
SELECT
A.TABLE_CATALOG
,A.TABLE_NAME
,A.ORDINAL_POSITION
,A.COLUMN_NAME
,A.DATA_TYPE
,ISNULL(A.CHARACTER_MAXIMUM_LENGTH,'')
,ISNULL(A.NUMERIC_PRECISION,'')
,A.IS_NULLABLE
,ISNULL(A.COLUMN_DEFAULT,'')
,ISNULL(B.CONSTRAINT_NAME,'')
,ISNULL(A.CHARACTER_SET_NAME,'')
,ISNULL(A.COLLATION_NAME,'')
,CASE WHEN ISNULL(C.NAME,'') = '' THEN '' ELSE 'Identity' END auto
FROM
INFORMATION_SCHEMA.COLUMNS A
LEFT OUTER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE B
ON A.TABLE_NAME = B.TABLE_NAME
AND A.COLUMN_NAME = B.COLUMN_NAME
LEFT OUTER JOIN
syscolumns C
ON C.ID = object_id(A.TABLE_NAME) AND A.COLUMN_NAME = C.NAME AND C.COLSTAT & 1 = 1
WHERE
A.TABLE_NAME in (SELECT name FROM sysobjects WHERE xtype='U')
ORDER BY A.ORDINAL_POSITION
'MSSQL' 카테고리의 다른 글
[MS-SQL] 단일사용자 모드 해제 (0) | 2017.12.08 |
---|---|
LINK DB (0) | 2017.09.08 |
MSSQL 데이터베이스엔진 설치 오류 (0) | 2017.08.24 |
[MS SQL]SIMPLE LOG LEVEL (0) | 2017.04.18 |
MS-SQL 테이블 목록, 컬럼 상세내용 보기 (0) | 2016.10.13 |