SQL Server 中的 SYSNAME 数据类型是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5720212/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is SYSNAME data type in SQL Server?
提问by jrara
What is the SQL Server SYSNAME data type for? BOLsays:
SQL Server SYSNAME 数据类型的用途是什么？BOL说：
The sysname data type is used for table columns, variables, and stored procedure parameters that store object names.
sysname 数据类型用于存储对象名称的表列、变量和存储过程参数。
but I don't really get that. Is there a use-case you can provide?
但我真的不明白。您可以提供用例吗？
回答by codingbadger
sysnameis a built in datatype limited to 128 Unicode characters that, IIRC, is used primarily to store object names when creating scripts. Its value cannot be NULL
sysname是一种内置数据类型，限制为 128 个 Unicode 字符，IIRC 主要用于在创建脚本时存储对象名称。它的价值不能NULL
It is basically the same as using nvarchar(128) NOT NULL
它与使用基本相同 nvarchar(128) NOT NULL
EDIT
编辑
As mentioned by @Jim in the comments, I don't think there is really a business case where you would use sysnameto be honest. It is mainly used by Microsoft when building the internal systables and stored procedures etc within SQL Server.
正如@Jim 在评论中提到的那样，我认为实际上没有一个商业案例可以让您sysname说实话。它主要被微软用于sys在 SQL Server 中构建内部表和存储过程等。
For example, by executing Exec sp_help 'sys.tables'you will see that the column nameis defined as sysnamethis is because the value of this is actually an object in itself (a table)
例如，通过执行Exec sp_help 'sys.tables'你会看到列name被定义为sysnamethis is 因为 this 的值实际上是一个对象本身（一个表）
I would worry too much about it.
我会担心太多。
It's also worth noting that for those people still using SQL Server 6.5 and lower (are there still people using it?) the built in type of sysnameis the equivalent of varchar(30)
还值得注意的是，对于那些仍在使用 SQL Server 6.5 及更低版本的人（还有人在使用它吗？），内置类型sysname相当于varchar(30)
Documentation
文档
sysnameis defined with the documentation for ncharand nvarchar, in the remarks section:
sysname定义与文档nchar，并nvarchar在备注部分：
sysnameis a system-supplied user-defined data type that is functionally equivalent to nvarchar(128), except that it is not nullable. sysnameis used to reference database object names.
sysname是系统提供的用户定义数据类型，在功能上与nvarchar(128)等效，但它不可为空。sysname用于引用数据库对象名称。
To clarify the above remarks, by defaultsysnameis defined as NOT NULLit is certainly possible to define it as nullable. It is also important to note that the exact definition can vary between instances of SQL Server.
为了澄清上述说明，默认情况下定义了sysname，NOT NULL因为当然可以将其定义为可为空的。同样重要的是要注意确切的定义在 SQL Server 实例之间可能会有所不同。
The sysnamedata type is used for table columns, variables, and stored procedure parameters that store object names. The exact definition of sysnameis related to the rules for identifiers. Therefore, it can vary between instances of SQL Server. sysnameis functionally the same as nvarchar(128)except that, by default, sysnameis NOT NULL. In earlier versions of SQL Server, sysnameis defined as varchar(30).
的数据类型为sysname数据类型用于表的列，变量和存储过程参数存储对象名称。sysname的确切定义 与标识符规则有关。因此，它可能因 SQL Server 实例而异。sysname在功能上与nvarchar(128)相同，但默认情况下，sysname不是 NULL。在 SQL Server 的早期版本中，sysname被定义为 varchar(30)。
回答by Mikael Eriksson
Is there use case you can provide?
您可以提供用例吗？
If you ever have the need for creating some dynamic sqlit is appropriate to use sysnameas data type for variables holding table names, column names and server names.
如果您需要创建一些动态 sql，那么将其用作sysname包含表名、列名和服务器名的变量的数据类型是合适的。
回答by gloomy.penguin
Just as an FYI....
就像一个仅供参考....
select * from sys.types where system_type_id = 231gives you two rows.
select * from sys.types where system_type_id = 231给你两行。
(i'm not sure what this means yet but i'm 100% sure it's messing up my code right now)
（我还不确定这意味着什么，但我 100% 确定它现在弄乱了我的代码）
edit:i guess what it means is that you should join by the user_type_id in this situation (my situation) or possibly both the user_type_id and th esystem_type_id
编辑：我想这意味着你应该在这种情况下（我的情况）加入 user_type_id 或者 user_type_id 和 esystem_type_id
name system_type_id user_type_id schema_id principal_id max_length precision scale collation_name is_nullable is_user_defined is_assembly_type default_object_id rule_object_id
nvarchar 231 231 4 NULL 8000 0 0 SQL_Latin1_General_CP1_CI_AS 1 0 0 0 0
sysname 231 256 4 NULL 256 0 0 SQL_Latin1_General_CP1_CI_AS 0 0 0 0 0
create procedure dbo.yyy_test (
@col_one nvarchar(max),
@col_two nvarchar(max) = 'default',
@col_three nvarchar(1),
@col_four nvarchar(1) = 'default',
@col_five nvarchar(128),
@col_six nvarchar(128) = 'default',
@col_seven sysname
)
as begin
select 1
end
This query:
这个查询：
select parm.name AS Parameter,
parm.max_length,
parm.parameter_id
from sys.procedures sp
join sys.parameters parm ON sp.object_id = parm.object_id
where sp.name = 'yyy_test'
order by parm.parameter_id
yields:
产量：
parameter max_length parameter_id
@col_one -1 1
@col_two -1 2
@col_three 2 3
@col_four 2 4
@col_five 256 5
@col_six 256 6
@col_seven 256 7
and this:
和这个：
select parm.name as parameter,
parm.max_length,
parm.parameter_id,
typ.name as data_type,
typ.system_type_id,
typ.user_type_id,
typ.collation_name,
typ.is_nullable
from sys.procedures sp
join sys.parameters parm ON sp.object_id = parm.object_id
join sys.types typ ON parm.system_type_id = typ.system_type_id
where sp.name = 'yyy_test'
order by parm.parameter_id
gives you this:
给你这个：
parameter max_length parameter_id data_type system_type_id user_type_id collation_name is_nullable
@col_one -1 1 nvarchar 231 231 SQL_Latin1_General_CP1_CI_AS 1
@col_one -1 1 sysname 231 256 SQL_Latin1_General_CP1_CI_AS 0
@col_two -1 2 nvarchar 231 231 SQL_Latin1_General_CP1_CI_AS 1
@col_two -1 2 sysname 231 256 SQL_Latin1_General_CP1_CI_AS 0
@col_three 2 3 nvarchar 231 231 SQL_Latin1_General_CP1_CI_AS 1
@col_three 2 3 sysname 231 256 SQL_Latin1_General_CP1_CI_AS 0
@col_four 2 4 nvarchar 231 231 SQL_Latin1_General_CP1_CI_AS 1
@col_four 2 4 sysname 231 256 SQL_Latin1_General_CP1_CI_AS 0
@col_five 256 5 nvarchar 231 231 SQL_Latin1_General_CP1_CI_AS 1
@col_five 256 5 sysname 231 256 SQL_Latin1_General_CP1_CI_AS 0
@col_six 256 6 nvarchar 231 231 SQL_Latin1_General_CP1_CI_AS 1
@col_six 256 6 sysname 231 256 SQL_Latin1_General_CP1_CI_AS 0
@col_seven 256 7 nvarchar 231 231 SQL_Latin1_General_CP1_CI_AS 1
@col_seven 256 7 sysname 231 256 SQL_Latin1_General_CP1_CI_AS 0
回答by aok
Let me list a use case below. Hope it helps. Here I'm trying to find the Table Owner of the Table 'Stud_dtls' from the DB 'Students'. As Mikael mentioned, sysname could be used when there is a need for creating some dynamic sql which needs variables holding table names, column names and server names. Just thought of providing a simple example to supplement his point.
让我在下面列出一个用例。希望能帮助到你。在这里，我试图从数据库“Students”中找到表“Stud_dtls”的表所有者。正如 Mikael 所提到的，当需要创建一些需要包含表名、列名和服务器名的变量的动态 sql 时，可以使用 sysname。只是想提供一个简单的例子来补充他的观点。
USE Students
DECLARE @TABLE_NAME sysname
SELECT @TABLE_NAME = 'Stud_dtls'
SELECT TABLE_SCHEMA
FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_NAME = @TABLE_NAME
回答by usefulBee
sysnameis used by sp_send_dbmail, a stored procedure that "Sends an e-mail message to the specified recipients" and located in the msdb database.
sysname由sp_send_dbmail“向指定收件人发送电子邮件”的存储过程使用并位于 msdb 数据库中。
According to Microsoft,
据微软称，
[ @profile_name = ] 'profile_name'Is the name of the profile to send the message from. The profile_name is of type sysname, with a default of NULL. The profile_name must be the name of an existing Database Mail profile. When no profile_name is specified, sp_send_dbmail uses the default private profile for the current user. If the user does not have a default private profile, sp_send_dbmail uses the default public profile for the msdb database. If the user does not have a default private profile and there is no default public profile for the database, @profile_name must be specified.
[ @profile_name = ] 'profile_name'是从中发送消息的配置文件的名称。profile_name 的类型为 sysname，默认值为 NULL。profile_name 必须是现有数据库邮件配置文件的名称。如果未指定 profile_name，sp_send_dbmail 将使用当前用户的默认专用配置文件。如果用户没有默认的专用配置文件，sp_send_dbmail 将使用 msdb 数据库的默认公用配置文件。如果用户没有默认的私有配置文件并且数据库没有默认的公共配置文件，则必须指定@profile_name。
回答by AjV Jsy
FWIW, you can pass a table name to useful system SP's like this, should you wish to explore a database that way :
FWIW，如果您希望以这种方式探索数据库，您可以像这样将表名传递给有用的系统 SP：
DECLARE @Table sysname; SET @Table = 'TableName';
EXEC sp_fkeys @Table;
EXEC sp_help @Table;
回答by d219
Another use case is when using the SQL Server 2016+ functionality of AT TIME ZONE
另一个用例是在使用 SQL Server 2016+ 功能时 AT TIME ZONE
The below statement will return a date converted to GMT
以下语句将返回转换为 GMT 的日期
SELECT
CONVERT(DATETIME, SWITCHOFFSET([ColumnA], DATEPART(TZOFFSET, [ColumnA] AT TIME ZONE 'GMT Standard Time')))
If you want to pass the time zone as a variable, say:
如果要将时区作为变量传递，请说：
SELECT
CONVERT(DATETIME, SWITCHOFFSET([ColumnA], DATEPART(TZOFFSET, [ColumnA] AT TIME ZONE @TimeZone)))
then that variable needs to be of the type sysname(declaring it as varcharwill cause an error).
那么该变量需要属于该类型sysname（varchar将其声明为会导致错误）。
