What is SYSNAME data type in SQL Server?
SqlSql ServerTsqlTypesSql Problem Overview
What is the SQL Server SYSNAME data type for? BOL says:
> The sysname data type is used for > table columns, variables, and stored > procedure parameters that store object > names.
but I don't really get that. Is there a use-case you can provide?
Sql Solutions
Solution 1 - Sql
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
It is basically the same as using 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.
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)
I wouldn't 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)
Documentation
sysname is defined with the documentation for nchar and nvarchar, in the remarks section:
> 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.
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.
> 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).
Some further information about sysname allowing or disallowing NULL values can be found here https://stackoverflow.com/a/52290792/300863
Just because it is the default (to be NOT NULL) does not guarantee that it will be!
Solution 2 - Sql
> 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.
Solution 3 - Sql
Just as an FYI....
select * from sys.types where system_type_id = 231 gives you two rows.
(i'm not sure what this means yet but i'm 100% sure it's messing up my code right now)
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 the system_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
Solution 4 - Sql
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.
USE Students
DECLARE @TABLE_NAME sysname
SELECT @TABLE_NAME = 'Stud_dtls'
SELECT TABLE_SCHEMA
FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_NAME = @TABLE_NAME
Solution 5 - Sql
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.
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.
Solution 6 - Sql
FWIW, you can pass a table name to useful system SP's like this, should you wish to explore a database that way :
DECLARE @Table sysname; SET @Table = 'TableName';
EXEC sp_fkeys @Table;
EXEC sp_help @Table;
Solution 7 - Sql
Another use case is when using the SQL Server 2016+ functionality of AT TIME ZONE
The below statement will return a date converted to 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 varchar will cause an error).
Solution 8 - Sql
> Is there use case you can provide?
Anywhere you want to store an object name for use by database maintenance scripts. For example, a script purges old rows from certain tables that have a date column. It's configured with a table that gives table name, column name to filter on, and how many days of history to keep. Another script dumps certain tables to CSV files, and again is configured with a table listing the tables to dump. These configuration tables can use the sysname type to store table and column names.