Sql-server – T-SQL query to show table definition of sys.time_zone_info

sql serversystem-tablest-sql

Following query could be used to show definitions of custom table.

SELECT ORDINAL_POSITION, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
       , IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'PostHistory'

not work for sys tables

SELECT ORDINAL_POSITION, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
       , IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'sys.time_zone_info'

I guess the info is in another table in another database what are they?

I've also tried sp_help 'sys.time_zone_info', and got

The EXECUTE permission was denied on the object 'sp_help', database 'mssqlsystemresource', schema 'sys'.

Best Answer

sys.time_zone_info is a system view with the definition:

CREATE VIEW sys.time_zone_info AS
    SELECT  name,
            current_utc_offset,
            is_currently_dst
    FROM OPENROWSET(TABLE TIME_ZONE_INFO, SYSUTCDATETIME())

The OPENROWSET call is a wrapper over an internal structure. You cannot get information about it directly.

The columns returned by the view are shown in the documentation


+--------------------+--------------+--------------------------------------------------------------------------------------------------------------------------------+
|    Column name     |  Data type   |                                                          Description                                                           |
+--------------------+--------------+--------------------------------------------------------------------------------------------------------------------------------+
| name               | sysname      | Name of the time zone in Windows standard format. For example, Cen. Australia Standard Time or Central European Standard Time. |
| current_utc_offset | nvarchar(12) | Current offset to UTC. For example, +01:00 or -07:00.                                                                          |
| is_currently_dst   | bit          | True if currently observing daylight savings time.                                                                             |
+--------------------+--------------+--------------------------------------------------------------------------------------------------------------------------------+

and from nchar and nvarchar (Transact-SQL):

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.

You can also obtain the information you want from the system catalogue views, for example:

SELECT
    AC.column_id,
    AC.[name],
    data_type = T.[name],
    AC.max_length,
    AC.[precision],
    AC.scale,
    AC.collation_name,
    AC.is_nullable
FROM sys.all_objects AS AO
JOIN sys.all_columns AS AC
    ON AC.[object_id] = AO.[object_id]
JOIN sys.types AS T
    ON T.user_type_id = AC.user_type_id
WHERE
    AO.[object_id] = OBJECT_ID(N'sys.time_zone_info');

db<>fiddle demo

Related Question