如何判断列是否为系统名称类型

Question

如何通过查询系统列来查找表中的列是一个常见问题。 例如，用于获取表中列列表以及数据类型、NOT NULL 和 PRIMARY KEY 约束的 SQL Server 查询给出了一个在大多数情况下都有效的查询。 但是，类型sysname的列以纯nvarchar返回。 在管理工作室 (SSMS( 中，编写表定义的脚本将正确提供sysname类型。sp_columns也是如此. 但是，如何使用针对系统表的 SQL 查询来确定列是否属于sysname类型呢？ (我不想为每个表单独运行sp_columns。

如果您想知道sysname类型是什么，SQL Server中的SYSNAME数据类型是什么？提供了一些信息。

要提供更多详细信息：

create view some_table_names as select name from sys.tables

然后运行

sp_columns 'some_table_names'

报告type_name=sysname. 但是针对 sys.columns 的简单查询只给出了varchar：

select type_name(c.system_type_id)
from sys.objects t
join sys.columns c
on t.object_id = c.object_id
where t.name = 'some_table_names'

我看了一下sp_columns的定义，看看我是否可以做同样的事情。 它在系统表中查找列详细信息sys.spt_columns_odbc_view。 但这显然是一些绝密的内部表，只能从直接管理员连接 (DAC( 或 SSMS 查询。 (请参阅什么是spt_columns_odbc_view以及为什么无法访问？sp_columns进程设法查询此视图，即使我没有从管理工作室或通过 DAC 运行它。 但是我不知道如何在我自己的代码中重复这个技巧。

有没有其他方法可以判断列是否属于sysname类型？

Answer 1

sys.types目录视图公开可在 DDL 中指定的数据类型。您可以在user_type_id上联接到此视图以标识列类型。从此查询中可以看出，sysname不是内部机密类型。

SELECT c.Name AS ColumnName, ty.name AS TypeName, c.max_length AS ColumnLengthBytes
FROM sys.objects t
JOIN sys.columns c ON t.object_id = c.object_id 
JOIN sys.types ty ON c.user_type_id = ty.user_type_id
WHERE t.name = N'test_table';

sysname类似于用户定义类型。它与使用CREATE TYPE创建的 UDT 的不同之处在于，sys.types的is_user_defined列将为零而不是 1，因为它是由 SQL Server 而不是用户定义的。

还可以在system_type_id上联接，以返回用户和基本系统类型。

SELECT c.Name AS ColumnName, ty.name AS TypeName, c.max_length AS ColumnLengthBytes
FROM sys.objects t
JOIN sys.columns c ON t.object_id = c.object_id 
JOIN sys.types ty ON ty.system_type_id = c.system_type_id
WHERE t.name = N'test_table';

Answer 2

首先，即使对于维护脚本，也没有有效的案例将数据存储为sysname。对于脚本，您需要表或列的实际名称，因此将其存储在nvarchar(128)字段中是完全可以的。

系统本身将sysname视为sys.types表中的用户类型。如果您检查记录，您将看到数据库本身告诉您这是一个nvarchar。如果要返回该别名，请将sys.columns.user_type_id列与types.user_type_id连接，例如：

select object_name(object_id), 
types.name,
* 
from sys.columns
inner join sys.types on types.user_type_id =sys.columns.user_type_id
where columns.user_type_id=256

或者只是

select object_name(object_id), 
TYPE_NAME(user_type_id),
* 
from sys.columns
where user_type_id=256

更新

我刚刚在服务器上检查了该类型的记录，在该服务器上，我使用不同的排序规则进行数据库，并注意到排序规则发生了变化以匹配数据库的排序规则。因此，即使在同一台服务器上，使用该别名也可能导致排序规则问题