所有列INFORMATION_SCHEMA.COLUMNS
均列在学习 -> SQL -> SQL Server -> 列 (Transact-SQL)::
COLUMN_NAME
数据类型为nvarchar(128)
:
但是没有关于此列的更多元数据。它采用哪种排序规则?
此列的所有属性的文档在哪里:
在微软网站上?
或者可能在某些 SQL Server 菜单本身中?我有 Server Management Studio。使用 MySQL 数据库上的 DBeaver,您可以右键单击检查任何视图或表的属性,以便查看所有列属性。但 MS SQL Server 没有这个。
或者我如何才能获取
INFORMATION_SCHEMA.COLUMNS
视图的视图INFORMATION_SCHEMA.COLUMNS
?例如:select * from INFORMATION_SCHEMA.COLUMNS where c.TABLE_SCHEMA = 'INFORMATION_SCHEMA' and c.TABLE_NAME = 'COLUMNS'
答案1
Visual Studio 中的 C#
例如,您可以从 Visual Studio 使用 Visual C#“ConsoleApp (.Net Framework)”项目运行此 C# 代码:
它为您提供了一个标准的空项目代码,您可以从中继续:
SqlDataReader.GetColumnSchema 方法
您可以打开阅读器,然后使用 获取列架构GetColumnSchema()
。这是一个 C# 示例:
using var connection = new SqlConnection(ConnectionString);
using var command =
new SqlCommand("SELECT TOP 1 * FROM INFORMATION_SCHEMA.COLUMNS", connection);
connection.Open();
using var reader = command.ExecuteReader();
foreach (DbColumn column in reader.GetColumnSchema()) {
Console.WriteLine($"{column.ColumnName} {column.DataTypeName}({column.ColumnSize})");
}
Console.ReadKey();
印刷:
TABLE_CATALOG nvarchar(128)
TABLE_SCHEMA nvarchar(128)
TABLE_NAME nvarchar(128)
COLUMN_NAME nvarchar(128)
ORDINAL_POSITION int(4)
COLUMN_DEFAULT nvarchar(4000)
IS_NULLABLE varchar(3)
DATA_TYPE nvarchar(128)
CHARACTER_MAXIMUM_LENGTH int(4)
CHARACTER_OCTET_LENGTH int(4)
NUMERIC_PRECISION tinyint(1)
NUMERIC_PRECISION_RADIX smallint(2)
NUMERIC_SCALE int(4)
DATETIME_PRECISION smallint(2)
CHARACTER_SET_CATALOG nvarchar(128)
CHARACTER_SET_SCHEMA nvarchar(128)
CHARACTER_SET_NAME nvarchar(128)
COLLATION_CATALOG nvarchar(128)
COLLATION_SCHEMA nvarchar(128)
COLLATION_NAME nvarchar(128)
DOMAIN_CATALOG nvarchar(128)
DOMAIN_SCHEMA nvarchar(128)
DOMAIN_NAME nvarchar(128)
SqlConnection.GetSchema 方法
以下是提问者为较旧的设置提供的一些代码SqlConnection.GetSchema()
,是在SqlConnection.GetSchema 方法
using System;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
namespace ConsoleApp_metadata_of_information_schema_test
{
class Program
{
private static string _connectionString => @"Server=mydomain\myservername;Data Source=mydatabase;Initial Catalog=mytestcatalog;Integrated Security=SSPI";
public static void Main(string[] args)
{
SqlConnection conn = null;
SqlCommand command = null;
try
{
conn = new SqlConnection() { ConnectionString = _connectionString };
conn.Open();
command = new SqlCommand("SELECT TOP 1 * FROM INFORMATION_SCHEMA.COLUMNS", conn);
var reader = command.ExecuteReader();
ShowDataTable(reader.GetSchemaTable(), 20);
// Obtains the next character or function key pressed by the user. The pressed key is optionally displayed in the console window.
Console.ReadKey();
}
catch (SqlException ex)
{
Debug.WriteLine(ex.Message);
Debug.WriteLine("Exiting Main");
}
}
private static void ShowDataTable(DataTable table, Int32 length)
{
foreach (DataColumn col in table.Columns)
{
Console.Write("{0,-" + length + "}", col.ColumnName);
}
Console.WriteLine();
foreach (DataRow row in table.Rows)
{
foreach (DataColumn col in table.Columns)
{
if (col.DataType.Equals(typeof(DateTime)))
Console.Write("{0,-" + length + ":d}", row[col]);
else if (col.DataType.Equals(typeof(Decimal)))
Console.Write("{0,-" + length + ":C}", row[col]);
else
Console.Write("{0,-" + length + "}", row[col]);
}
Console.WriteLine();
}
}
}
}
出局(仅前四行):
ColumnName ColumnOrdinal ColumnSize NumericPrecision NumericScale IsUnique IsKey BaseServerName BaseCatalogName BaseColumnName BaseSchemaName BaseTableName DataType AllowDBNull ProviderType IsAliased IsExpression IsIdentity IsAutoIncrement IsRowVersion IsHidden IsLong IsReadOnly ProviderSpecificDataTypeDataTypeName XmlSchemaCollectionDatabaseXmlSchemaCollectionOwningSchemaXmlSchemaCollectionNameUdtAssemblyQualifiedNameNonVersionedProviderTypeIsColumnSet
TABLE_CATALOG 0 128 255 255 False TABLE_CATALOG System.String True 12 False False False False True System.Data.SqlTypes.SqlStringnvarchar 12 False
TABLE_SCHEMA 1 128 255 255 False TABLE_SCHEMA System.String True 12 False False False False True System.Data.SqlTypes.SqlStringnvarchar 12 False
TABLE_NAME 2 128 255 255 False TABLE_NAME System.String False 12 False False False False False System.Data.SqlTypes.SqlStringnvarchar 12 False
COLUMN_NAME 3 128 255 255 False COLUMN_NAME System.String True 12 False False False False False System.Data.SqlTypes.SqlStringnvarchar 12 False
头脑
- 您在输出中找不到排序规则SqlConnection.GetSchema 方法以及其DataTable 类作为输出。
- 排序规则也不在较新的SqlDataReader.GetColumnSchema 方法以及其DbColumn 类。
检查 SSMS 中的视图
答案2
我认为通过转到本机 mssql DMV,我已经找到了您要查找的内容。注意 - 我必须使用它们的“all_”版本(其中包括系统对象);我思考这就是为什么 information_schema.columns 没有出现在您的原始查询中:
select c.*
from sys.all_columns as c
join sys.all_objects as o
on o.object_id = c.object_id
where o.schema_id = schema_id('INFORMATION_SCHEMA')
and o.name = 'COLUMNS';
答案3
这里列出了获取列属性信息的其他一些方法。其他答案和这个答案一起可以构建一种INFORMATION_SCHEMA.COLUMNS
排序INFORMATION_SCHEMA.COLUMNS
。下面的前三个标题包含所需的内容,存储过程显示数据类型和排序规则,而这个问题的其他答案没有一次性提供这些内容。
然而,另一个 C# 答案可以占用所有这些并将其混合成一个输出。因此,这是将整个输出放在一起以达到完整INFORMATION_SCHEMA.COLUMNS
输出的唯一方法。这就是为什么 C# 答案仍然是可接受的答案,INFORMATION_SCHEMA.COLUMNS
即使前三个标题的内置存储过程(exec sp_hepl
、、)应该是输出的核心。exec sp_columns
exec sp_describe_first_result_set
exec sp_help'您的表名称'
看获取列类型的SQL语句,答案是“exec sp_help YOURTABLENAME”,请查阅该答案以进行投票。
exec sp_help 'INFORMATION_SCHEMA.COLUMNS'
输出列:
列名 类型 计算长度 Prec 比例 可空 TrimTrailingBlanks FixedLenNullInSource 排序规则
输出:
Name Owner Type Created_datetime
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------- -----------------------
COLUMNS INFORMATION_SCHEMA view 2016-04-30 00:45:03.617
Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------------------------------- ----------- ----- ----- ----------------------------------- ----------------------------------- ----------------------------------- --------------------------------------------------------------------------------------------------------------------------------
... (cut) ...
COLUMN_NAME sysname no 256 yes (n/a) (n/a) Latin1_General_CI_AS
Identity Seed Increment Not For Replication
-------------------------------------------------------------------------------------------------------------------------------- --------------------------------------- --------------------------------------- -------------------
No identity column defined. NULL NULL NULL
RowGuidCol
--------------------------------------------------------------------------------------------------------------------------------
No rowguidcol column defined.
No constraints are defined on object 'INFORMATION_SCHEMA.COLUMNS', or you do not have permissions.
No foreign keys reference table 'INFORMATION_SCHEMA.COLUMNS', or you do not have permissions on referencing tables.
The object 'INFORMATION_SCHEMA.COLUMNS' does not have any indexes, or you do not have permissions.
因此,这将一次性显示数据类型和排序规则,接近整体INFORMATION_SCHEMA.COLUMNS
。
执行 sp_describe_first_result_set
DECLARE @query nvarchar(max) = 'select * from INFORMATION_SCHEMA.COLUMNS';
EXEC sp_describe_first_result_set @query, null, 0;
输出列:
is_hidden column_ordinal 名称 is_nullable system_type_id system_type_name max_length 精度 小数位数 collation_name user_type_id user_type_database user_type_schema user_type_name assembly_qualified_type_name xml_collection_id xml_collection_database xml_collection_schema xml_collection_name is_xml_document is_case_sensitive is_fixed_length_clr_type source_server source_database source_schema source_table source_column is_identity_column is_part_of_unique_key is_updateable is_computed_column is_sparse_column_set ordinal_in_order_by_list order_by_is_descending order_by_list_length tds_type_id tds_length tds_collation_id tds_collation_sort_id
输出:
is_hidden column_ordinal name is_nullable system_type_id system_type_name max_length precision scale collation_name user_type_id user_type_database user_type_schema user_type_name assembly_qualified_type_name xml_collection_id xml_collection_database xml_collection_schema xml_collection_name is_xml_document is_case_sensitive is_fixed_length_clr_type source_server source_database source_schema source_table source_column is_identity_column is_part_of_unique_key is_updateable is_computed_column is_sparse_column_set ordinal_in_order_by_list order_by_is_descending order_by_list_length tds_type_id tds_length tds_collation_id tds_collation_sort_id
--------- -------------- -------------------------------------------------------------------------------------------------------------------------------- ----------- -------------- -------------------------------------------------------------------------------------------------------------------------------- ---------- --------- ----- -------------------------------------------------------------------------------------------------------------------------------- ------------ -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --------------- ----------------- ------------------------ -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------ --------------------- ------------- ------------------ -------------------- ------------------------ ---------------------- -------------------- ----------- ----------- ---------------- ---------------------
... (cut) ...
0 4 COLUMN_NAME 1 231 nvarchar(128) 256 0 0 Latin1_General_CI_AS 256 testDB sys sysname NULL NULL NULL NULL NULL 0 0 0 NULL NULL NULL NULL NULL 0 NULL 1 0 0 NULL NULL NULL 231 256 13632521 0
... (cut) ...
(23 rows affected)
执行 sp_columns
exec sp_columns 'COLUMNS', 'INFORMATION_SCHEMA'
有关更多参数,请在键入时检查 SSMS 提示:
输出列:
TABLE_QUALIFIER TABLE_OWNER TABLE_NAME COLUMN_NAME DATA_TYPE TYPE_NAME 精度 长度 小数位数 基数 可空 备注 COLUMN_DEF SQL_DATA_TYPE SQL_DATETIME_SUB CHAR_OCTET_LENGTH ORDINAL_POSITION IS_NULLABLE SS_DATA_TYPE
输出:
TABLE_QUALIFIER TABLE_OWNER TABLE_NAME COLUMN_NAME DATA_TYPE TYPE_NAME PRECISION LENGTH SCALE RADIX NULLABLE REMARKS COLUMN_DEF SQL_DATA_TYPE SQL_DATETIME_SUB CHAR_OCTET_LENGTH ORDINAL_POSITION IS_NULLABLE SS_DATA_TYPE
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --------- -------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ------ ------ -------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------- ---------------- ----------------- ---------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------
... (cut)...
testDB INFORMATION_SCHEMA COLUMNS COLUMN_NAME -9 sysname 128 256 NULL NULL 1 NULL NULL -9 NULL 256 4 YES 39
... (cut)...
(23 rows affected)
将视图设为表格并检查“列属性”
从确定 Nvarchar 长度我从视图中得到了创建表的提示,以便我可以检查其列属性“数据类型”。我只检查COLUMN_NAME
因为我只需要它,但它代表任何其他列,请参阅从视图创建表:
SELECT COLUMN_NAME into dbo.INFORMATION_SCHEMA_COLUMNS_test from [INFORMATION_SCHEMA].[COLUMNS]
然后右键点击新表,脚本表为,创建到,新查询窗口:
CREATE TABLE [dbo].[INFORMATION_SCHEMA_COLUMNS_test](
[COLUMN_NAME] [sysname] NULL
) ON [PRIMARY]
GO
所以我们看到它具有数据类型sysname
。
再次右键单击表格,您还可以获得更多属性:
(一般的):
- (名称):COLUMN_NAME
- 允许空值:是
- 数据类型: sysname
- 默认值或绑定:--
表设计器:
- 排序规则:<数据库默认值>
- 计算列规范:--
- 压缩数据类型:sysname
- 描述: -
- 确定性:是
- DTS 发布:否
- 全文规范:否
- 有非 SQL Server 订阅者:否
- 身份规范:否
- 可转位:是
- 是否为列集:否
- 是否稀疏:否
- 合并发布:否
- 不可复制:否
- 已复制:否
- RowGuid:否
- 尺寸:128
因此,数据类型为“sysname”。大小为128
,即“以字节对为单位的字符串长度”,而不是字符数,请参见上面的链接确定 Nvarchar 长度。
MS SQL Server 数据类型sysname
这是什么数据类型sysname
?请参阅 Microsoft 指南学习 - SQL - SQL Server - nchar 和 nvarchar (Transact-SQL):
sysname
是系统提供的用户定义数据类型,其功能相当于 nvarchar(128),只是它不可为空。sysname 用于引用数据库对象名称。使用 nchar 或 nvarchar 的对象会被分配数据库的默认排序规则,除非使用 COLLATE 子句分配了特定的排序规则。
选择COLUMNPROPERTY()
内置函数来获取字节对中的字符串长度
您还可以使用 TSQL 获取列的“字节对中的字符串长度”,取自如何在一个 SQL 语句中获取 varchar[n] 字段的大小?:
select COLUMNPROPERTY(object_id('information_schema.columns'), 'column_name', 'PRECISION') as string_length_in_byte_pairs;
出去:
string_length_in_byte_pairs
128
更多信息COLUMNPROPERTY
,请参阅Microsoft - 学习 - SQL - SQL Server - COLUMNPROPERTY (Transact-SQL)。但我检查了一下,它没有数据类型或排序规则,只有精度有帮助。
选择SERVERPROPERTY()
函数来获取服务器的默认排序规则
要获取数据库的默认排序规则,请参阅Microsoft - 学习 - SQL - SQL Server 查看排序规则信息 - 使用 Transact-SQL 查看服务器的排序规则设置:
SELECT CONVERT (varchar(256), SERVERPROPERTY('collation'));
出去:
(No column name)
Latin1_General_CI_AS
因此,COLUMN_NAME
的数据类型为sysname
(= nvarchar(128)
),且 的排序规则为Latin1_General_CI_AS
。