달력

042018  이전 다음

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  •  
  •  
  •  
  •  
  •  
출처 : swynk.com

내용 : Information Schema Views에 대한 종류와 그 내용. 샘플을 다룹니다.

Microsoft SQL Server ships with some information schema views that are very helpful for
getting information about the meta-data. And Microsoft suggests to use these views
instead of querying the system tables like systypes, sysreferences, sysindexes etc. since
they can change from release to release.
I personally prefer to have a good knowledge of the system tables and occasionally use
them for database administration purposes but for the application it's good if your SQL
queries are based off these views so you are sure that when you migrate from one
version to the other of SQL Server, you are not going to be breaking something.
All these information_schema views exist in SQL Server 2000 and detailed information on
all of them is available in BOL so I wont' go into details of what each view does. Here is
the list of the views:

Information_Schema.Check_Constraints
Information_Schema.Column_Domani_Usage
Information_Schema.Column_Privleges
Information_Schema.Columns
Information_Schema.Constraint_Column_Usage
Information_Schema.Constraint_Table_Usage
Information_Schema.Domain_Constraints
Information_Schema.Domains
Information_Schema.Key_Column_Usage
Information_Schema.Parameters
Information_Schema.Referential_Constraints
Information_Schema.Ruotine_Columns
Information_Schema.Routines
Information_Schema.Schemata
Information_Schema.Table_Constraints
Information_Schema.Table_Constraints
Information_Schema.Table_Privileges
Information_Schema.Tables
Information_Schema.Views
Information_Schema.View_Column_Usage
Information_Schema.View_Table_Usage
Some examples:
1)For getting a list of constraints, column_names and their position in the constraint:


SELECT COLS.CONSTRAINT_NAME,COLS.COLUMN_NAME,COLS.ORDINAL_POSITION
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS COLS
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS CONS ON
COLS.CONSTRAINT_NAME = CONS.CONSTRAINT_NAME
WHERE COLS.CONSTRAINT_CATALOG = DB_NAME()
AND COLS.TABLE_NAME = 'TASK_DTL'
AND CONS.CONSTRAINT_TYPE = 'PRIMARY KEY'
ORDER BY COLS.CONSTRAINT_NAME, COLS.ORDINAL_POSITION

You can replace the name of the table to whatever you want and can also replace the
constraint_type if you want to search for constraints other than the Primary Key.


2) SELECT CONSTRAINT_NAME,
COLUMN_NAME,
ORDINAL_POSITION
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE CONSTRAINT_CATALOG = DB_NAME()
AND TABLE_NAME = 'X'
ORDER BY CONSTRAINT_NAME, ORDINAL_POSITION

Subsitute x with the name of the table and you will get the name of the constraints defined
on the table (Primary Key as well as the Foreign key constraints), the names of the
columns involved and the position of the columns in the constraint, for example, which
column is number 1 in the case of a covered primary key.

3) For a list of tables, their columns, data-type for the columns, NULL/Not NULL criteria:


SELECT A.TABLE_NAME, B.COLUMN_NAME, B.DATA_TYPE, B.IS_NULLABLE
FROM INFORMATION_SCHEMA.TABLES A, INFORMATION_SCHEMA.COLUMNS B
WHERE A.TABLE_NAME = B.TABLE_NAME
ORDER BY A.TABLE_NAME
Posted by Tornado tornado