What is SYSNAME data type in SQL Server?

Question

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?

Gebler

In addition to these fine answers below, its also used to cause havoc for someone trying to get column metadata using sys.types, because it shares the same system_type_id as nvarchar. – Cyndi

Answer 1

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.

Using Special Data Types

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://mcmap.net/q/102906/-how-can-sysname-null-in-this-sql-server-system-stored-procedure-if-sysname-cannot-be-null

Just because it is the default (to be NOT NULL) does not guarantee that it will be!

Wicketkeeper

"Is there use case you can provide?" I don't think you will find a practical business reason for using. Mostly it is used internally in MS SQL as it is probably used quite a bit in tables, etc. – Menorah

You would use sysname for forward (and backward) compatibility in your scripts. – Poirier

just putting in 2 cents here: the columns that brought me here are declared as nvarchar(max) not null in the SP but they're showing as a sysname in the sys tables. – Icono

@Barry Actually... according to sys.types it's a nvarchar(256) not null. Note that system type ID = 231 (nvarchar). It works as a type alias in TDS nowadays; the first ID of an alias is 256, which corresponds to sysname. As for usage: sysname is used in information schema's. – Occupational

@Wicketkeeper this is just a general FYI. The sysname data type can indeed be null. I tested this out with a dummy table, inserted data with no problem on SQL Server 2008 R2. Here is my script: CREATE TABLE dbo.sysnameTest ( blah sysname NULL ); GO; INSERT INTO dbo.sysnameTest VALUES (NULL), ('blah'); SELECT * FROM dbo.sysnameTest; – Projector

@Projector I have added some further clarification this point. But yes, essentially you can set it to NULL if you want but by default it is NOT NULL – Wicketkeeper

@Wicketkeeper understood. Can I assume that it is a best practice to not make it null then? See when you said it was NOT NULL I was thinking like a timestamp data type which you really really cannot make NULL it just isn't possible. So in this case, it is "Just because you can, doesn't mean you should." – Projector

@Occupational The max_length length in sys.tables is in bytes. nvarchar uses two bytes per character, which is why its defined as nvarchar(128). – Branchiopod

"Its value cannot be NULL" That is not quite true, you can create a column of type sysname null and insert null values into it. So it's like other column types in that respect. The only difference is that if you specify plain sysname without explicit null or not null, then not null is assumed. – Ellisellison

Further to my comment above, please see stackoverflow.com/questions/52285088 OK to edit the answer to correct it? – Ellisellison

Answer 2

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.

Weaver

Answer 3

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

Icono

sys.types holds the user defined types that you create as well. If you do create type MyInt from int you will have two rows with system_type_id = 56. Another that is duplicated by default is 240 which is the system type for hierarchyid, geometry and geography. – Weaver

I completely forgot about that. So... what is the ideal way to query for this stuff with the sysname in there? since it's an 'alias' can I just do where typ.name<>'sysname' or would that have some other kind of consequence that I'm unaware of? – Icono

Join against sys.types on both system_type_id and user_type_id. SQL Fiddle – Weaver

oh, that's what I said at the very top. I thought you were saying that was wrong... – Icono

Sorry about that, not my meaning. Just wanted to point out some other things that could mess up your query, not only sysname could cause you grief without joining on both columns. – Weaver

Answer 4

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

Alley

Answer 5

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.

Talkathon

Answer 6

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;

Milanmilanese

Answer 7

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).

Beitris

Answer 8

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.

Ellisellison

You don't need that at all. Just use a nvarchar(128) not null column. The name is just that, a name. It doesn't have to be sysname to be used – Vadose

Sure, and in fact it doesn't even have to be of that type, nvarchar(300) would work too or even just varchar if you don't use Unicode in table names (as I'm sure almost nobody does). The advantage of sysname is partly that it makes the intention clearer: this column holds an object name; and partly that even if you migrate to a different version of MSSQL that changes the data type used for object names (as has happened before), it will continue to be the right type. – Ellisellison

No it doesn't. It's just another user type (essentially an alias) mapped to nvarchar(128) NOT NULL. In fact, that's how you can find the type - by checking the user_type_id column's value. You gain nothing more by using that type, than you would by creating your own user type – Vadose

Do you mean that if the definition of sysname were changed in a newer MSSQL version, and the database were backed up and restored into that newer instance, all the columns that had previously been sysname would now be of the wrong type and no longer match the type used in the system tables? – Ellisellison

It can't, or worse, if that ever happens, your column is hosed. sysname is a user-defined type with usert_type_id 256. There's no ALTER TYPE so there's no way to change it. You'd have to create a new type and change all columns that used the old type to the new one. If MS ever decided to change this they'd have to migrate existing system table data to the new type. You can expect them to do this for the system tables they already know about, but any user tables would have to be migrated by users – Vadose

Assuming the definition widens, you may not have any issues reading the data, you won't be able to write values that exceed the old definition though. If it becomes narrower, you may or may not have problems reading. That's as hypothetical as assuming the definition will change though. If you want to handle that, don't use the sysname UDT – Vadose

I ask because I know that the definition of sysname has changed in the past. It used to be varchar(30). – Ellisellison

And now you know it changes on every database, which means you can't trust it. Data stored with one definition won't be usable with data using a different definition – Vadose