我如何获取类似于 INFORMATION_SCHEMA.COLUMNS 视图本身的 INFORMATION_SCHEMA.COLUMNS 元数据视图?

我如何获取类似于 INFORMATION_SCHEMA.COLUMNS 视图本身的 INFORMATION_SCHEMA.COLUMNS 元数据视图?

所有列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

头脑

检查 SSMS 中的视图

您还可以在以下位置找到信息视图本身视图 > 系统视图在 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_columnsexec 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 精度 小数位数 collat​​ion_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_collat​​ion_id tds_collat​​ion_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

相关内容