sqlserver:information_schema

차이

문서의 선택한 두 판 사이의 차이를 보여줍니다.

차이 보기로 링크

--- sqlserver:information_schema [2020/06/25 09:04] – taekgu
+++ sqlserver:information_schema [2025/04/15 10:05] (현재) – 바깥 편집 127.0.0.1
@@ 줄 1: / 줄 1: @@
 ====== INFORMATION_SCHEMA ======
+===== TABLES =====
+<code sql>
+select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
+  from INFORMATION_SCHEMA.TABLES
+</code>
 ===== VIEWS =====
@@ 줄 6: / 줄 12: @@
   FROM INFORMATION_SCHEMA.VIEWS
 </code>
-===== TABLES =====
+===== COLUMNS =====
 <code sql>
-select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
+SELECT *--COLUMN_NAME, IS_NULLABLE, DATA_TYPE
-  from INFORMATION_SCHEMA.TABLES
+FROM INFORMATION_SCHEMA.COLUMNS
+</code>
+===== 각종 Database정보 얻기 =====
+==== 테이블 목록 가져오기: ====
+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
+==== 컬럼 정보 가져오기 ====
+<code sql>
+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 = '테이블명'
+ORDER BY A.ORDINAL_POSITION
 </code>

sqlserver/information_schema.1593075895.txt.gz · 마지막으로 수정됨: 2025/04/15 10:05 (바깥 편집)