How to tell whether a column is of sysname type

Question

It's a common question how to find the columns in a table by querying the system columns. For example SQL server query to get the list of columns in a table along with Data types, NOT NULL, and PRIMARY KEY constraints gives a query which works in most cases. However, a column of type sysname is returned as plain nvarchar. In Management Studio (SSMS) scripting out a table definition will correctly give the sysname type. And so will sp_columns. But how can I find whether a column is of sysname type using an SQL query against the system tables? (I don't want to run sp_columns separately for each table.)

In case you are wondering what the sysname type is to start with, What is SYSNAME data type in SQL Server? gives some info.

To give more detail:

create view some_table_names as select name from sys.tables

Then running

sp_columns 'some_table_names'

reports type_name=sysname. But a simple query against sys.columns gives just varchar:

select type_name(c.system_type_id)
from sys.objects t
join sys.columns c
  on t.object_id = c.object_id
where t.name = 'some_table_names'

I had a look at the definition of sp_columns to see if I could do the same thing. It looks up the column details in a system table sys.spt_columns_odbc_view. But this is apparently some top secret internal table that can only be queried from a direct administrator connection (DAC) or from SSMS. (See What is spt_columns_odbc_view and why is it not accessible?) The sp_columns proc manages to query this view even though I am not running it from Management Studio or over a DAC. But I don't know how to repeat that trick in my own code.

Is there some other way to tell whether a column is of sysname type?

Answer 1

First of all, there's no valid case for storing data as sysname, even for maintenance scripts. For a script you want the actual name of a table or column, so storing it in an nvarchar(128) field is perfectly fine.

The system itself treats sysname as a user type in the sys.types table. If you check the record, you'll see that the database itself tells you this is a nvarchar. If you want to return that alias name, join the sys.columns.user_type_id column with types.user_type_id, eg :

select object_name(object_id), 
types.name,
* 
from sys.columns
inner join sys.types on types.user_type_id =sys.columns.user_type_id
where columns.user_type_id=256

or just

select object_name(object_id), 
TYPE_NAME(user_type_id),
* 
from sys.columns
where user_type_id=256

UPDATE

I just checked the type's record on a server where I databases with different collations and noticed that the collation changes to match the database's. So even on the same server, using that alias can lead to collation issues

Answer 2

The sys.types catalog view exposes data types that can be specified in DDL. You can join to this view on user_type_id to identify column type. As you can see from this query, sysname is not an internal secret type.

SELECT c.Name AS ColumnName, ty.name AS TypeName, c.max_length AS ColumnLengthBytes
FROM sys.objects t
JOIN sys.columns c ON t.object_id = c.object_id 
JOIN sys.types ty ON c.user_type_id = ty.user_type_id
WHERE t.name = N'test_table';

sysname is similar to a user-defined type. It differs from a UDT created with CREATE TYPE in that the is_user_defined column of sys.types will be zero instead of one since it's defined by SQL Server rather than a user.

One can also join on system_type_id to also return both the user and base system type.

SELECT c.Name AS ColumnName, ty.name AS TypeName, c.max_length AS ColumnLengthBytes
FROM sys.objects t
JOIN sys.columns c ON t.object_id = c.object_id 
JOIN sys.types ty ON ty.system_type_id = c.system_type_id
WHERE t.name = N'test_table';