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?
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';