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

User · Answer

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

User · Answer

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 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   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   stackoverflow com a 52290792 300863  Just because it is the default  to be NOT NULL  does not guarantee that it will be

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] What is SYSNAME data type in SQL Server?

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to types