Querying Metadata

常用的元数据信息查询语句，记录下来，在某个时候还是很有用的。

--查询架构信息，SCHEMA_NAME这个方法主要用来将架构编号转换为架构名称

SELECT SCHEMA_NAME(schema_id) AS table_schema_name, name AS table_name
FROM sys.tables;

output:

table_schema_name  table_name
------------------ -------------
HR                 Employees
Production         Suppliers
Production         Categories
Production         Products
Sales              Customers
Sales              Shippers
Sales              Orders
Sales              OrderDetails

--查询表信息，TYPE_NAME这个方法，主要用来将类型编号转换为类型名称

SELECT
  name AS column_name,
  TYPE_NAME(system_type_id) AS column_type,
  max_length,
  collation_name,
  is_nullable
FROM sys.columns
WHERE object_id = OBJECT_ID(N'Sales.Orders');

 

output:

column_name     column_type     max_length collation_name            is_nullable
--------------- --------------- ---------- ------------------------- -----------
orderid         int             4          NULL                      0
custid          int             4          NULL                      0
empid           int             4          NULL                      0
orderdate       datetime        8          NULL                      0
requireddate    datetime        8          NULL                      0
shippeddate     datetime        8          NULL                      1
shipperid       int             4          NULL                      0
freight         money           8          NULL                      0
shipname        nvarchar        80         Latin1_General_CI_AI      0
shipaddress     nvarchar        120        Latin1_General_CI_AI      0
shipcity        nvarchar        30         Latin1_General_CI_AI      0
shipregion      nvarchar        30         Latin1_General_CI_AI      1
shippostalcode  nvarchar        20         Latin1_General_CI_AI      1
shipcountry     nvarchar        30         Latin1_General_CI_AI      0