常用的元数据信息查询语句,记录下来,在某个时候还是很有用的。
--查询架构信息,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