What is the SQL Server SYSNAME data type for? BOL says:
SQL Server SYSNAME数据类型用于什么?波尔说:
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?
但我真的不明白。你能提供一个用例吗?
6 个解决方案
#1
99
sysname is 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主要用于在创建脚本时存储对象名称。它的值不能为空
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 sysname to be honest. It is mainly used by Microsoft when building the internal sys tables and stored procedures etc within SQL Server.
正如@Jim在评论中提到的，我认为确实没有一个商业案例可以让你使用sysname来诚实。主要用于Microsoft在SQL Server中构建内部sys表和存储过程等。
For example, by executing Exec sp_help 'sys.tables' you will see that the column name is defined as sysname this is because the value of this is actually an object in itself (a table)
例如，通过执行Exec sp_help 'sys。你会看到列名被定义为sysname因为它的值本身就是一个对象(表)
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 sysname is the equivalent of varchar(30)
同样值得注意的是，对于那些仍在使用SQL Server 6.5或更低(还有人在使用它吗?)的用户来说，内置的sysname类型等同于varchar(30)
Documentation
文档
sysname is defined with the documentation for nchar and nvarchar, in the remarks section:
sysname由nchar和nvarchar的文档定义，在备注部分:
sysname is a system-supplied user-defined data type that is functionally equivalent to nvarchar(128), except that it is not nullable. sysname is used to reference database object names.
sysname是一个系统提供的用户定义数据类型，它在功能上等同于nvarchar(128)，但它不是空的。sysname用于引用数据库对象名称。
To clarify the above remarks, by default sysname is defined as NOT NULL it 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，当然可以将其定义为nullable。还需要注意的是，准确的定义在SQL Server的实例之间可能存在差异。
使用特殊的数据类型
The sysname data type is used for table columns, variables, and stored procedure parameters that store object names. The exact definition of sysname is related to the rules for identifiers. Therefore, it can vary between instances of SQL Server. sysname is functionally the same as nvarchar(128) except that, by default, sysname is NOT NULL. In earlier versions of SQL Server, sysname is defined as varchar(30).
sysname数据类型用于存储对象名称的表列、变量和存储过程参数。sysname的确切定义与标识符的规则相关。因此，在SQL Server的实例之间可能会有所不同。sysname在功能上与nvarchar(128)相同，但默认情况下，sysname不是NULL。在SQL Server的早期版本中，sysname被定义为varchar(30)。
#2
44
Is there use case you can provide?
是否有您可以提供的用例?
If you ever have the need for creating some dynamic sql it is appropriate to use sysname as data type for variables holding table names, column names and server names.
如果您曾经需要创建一些动态sql，那么可以使用sysname作为数据类型，以保存表名、列名和服务器名。
#3
3
Just as an FYI....
就像一个供参考....
select * from sys.types where system_type_id = 231 gives you two rows.
从系统选择*。类型，其中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和th 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
#4
2
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.
让我在下面列出一个用例。希望它可以帮助。在这里，我试图从DB 'Students'中找到表'Stud_dtls'的表所有者。正如Mikael所提到的，当需要创建一些动态sql时，可以使用sysname，这些sql需要包含表名、列名和服务器名的变量。只是想提供一个简单的例子来补充他的观点。
USE Students
DECLARE @TABLE_NAME sysname
SELECT @TABLE_NAME = 'Stud_dtls'
SELECT TABLE_SCHEMA
FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_NAME = @TABLE_NAME
#5
1
FWIW, you can pass a table name to useful system SP's like this, should you wish to explore a database that way :
FWIW，您可以将一个表名传递给有用的system SP，如果您希望这样探索一个数据库:
DECLARE @Table sysname; SET @Table = 'TableName';
EXEC sp_fkeys @Table;
EXEC sp_help @Table;
#6
0
sysname is 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.
#1
99
sysname is 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主要用于在创建脚本时存储对象名称。它的值不能为空
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 sysname to be honest. It is mainly used by Microsoft when building the internal sys tables and stored procedures etc within SQL Server.
正如@Jim在评论中提到的，我认为确实没有一个商业案例可以让你使用sysname来诚实。主要用于Microsoft在SQL Server中构建内部sys表和存储过程等。
For example, by executing Exec sp_help 'sys.tables' you will see that the column name is defined as sysname this is because the value of this is actually an object in itself (a table)
例如，通过执行Exec sp_help 'sys。你会看到列名被定义为sysname因为它的值本身就是一个对象(表)
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 sysname is the equivalent of varchar(30)
同样值得注意的是，对于那些仍在使用SQL Server 6.5或更低(还有人在使用它吗?)的用户来说，内置的sysname类型等同于varchar(30)
Documentation
文档
sysname is defined with the documentation for nchar and nvarchar, in the remarks section:
sysname由nchar和nvarchar的文档定义，在备注部分:
sysname is a system-supplied user-defined data type that is functionally equivalent to nvarchar(128), except that it is not nullable. sysname is used to reference database object names.
sysname是一个系统提供的用户定义数据类型，它在功能上等同于nvarchar(128)，但它不是空的。sysname用于引用数据库对象名称。
To clarify the above remarks, by default sysname is defined as NOT NULL it 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，当然可以将其定义为nullable。还需要注意的是，准确的定义在SQL Server的实例之间可能存在差异。
使用特殊的数据类型
The sysname data type is used for table columns, variables, and stored procedure parameters that store object names. The exact definition of sysname is related to the rules for identifiers. Therefore, it can vary between instances of SQL Server. sysname is functionally the same as nvarchar(128) except that, by default, sysname is NOT NULL. In earlier versions of SQL Server, sysname is defined as varchar(30).
sysname数据类型用于存储对象名称的表列、变量和存储过程参数。sysname的确切定义与标识符的规则相关。因此，在SQL Server的实例之间可能会有所不同。sysname在功能上与nvarchar(128)相同，但默认情况下，sysname不是NULL。在SQL Server的早期版本中，sysname被定义为varchar(30)。
#2
44
Is there use case you can provide?
是否有您可以提供的用例?
If you ever have the need for creating some dynamic sql it is appropriate to use sysname as data type for variables holding table names, column names and server names.
如果您曾经需要创建一些动态sql，那么可以使用sysname作为数据类型，以保存表名、列名和服务器名。
#3
3
Just as an FYI....
就像一个供参考....
select * from sys.types where system_type_id = 231 gives you two rows.
从系统选择*。类型，其中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和th 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
#4
2
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.
让我在下面列出一个用例。希望它可以帮助。在这里，我试图从DB 'Students'中找到表'Stud_dtls'的表所有者。正如Mikael所提到的，当需要创建一些动态sql时，可以使用sysname，这些sql需要包含表名、列名和服务器名的变量。只是想提供一个简单的例子来补充他的观点。
USE Students
DECLARE @TABLE_NAME sysname
SELECT @TABLE_NAME = 'Stud_dtls'
SELECT TABLE_SCHEMA
FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_NAME = @TABLE_NAME
#5
1
FWIW, you can pass a table name to useful system SP's like this, should you wish to explore a database that way :
FWIW，您可以将一个表名传递给有用的system SP，如果您希望这样探索一个数据库:
DECLARE @Table sysname; SET @Table = 'TableName';
EXEC sp_fkeys @Table;
EXEC sp_help @Table;
#6
0
sysname is 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.