Where do I find Sql Server metadata for column datatypes?

Question

I know that I can get access to column properties via:

select * 
from sysobjects

What I can't find however is information about where to get the type and type length for a column, ie: in

FOO VARCHAR(80)

Where do I look to find the "VARCHAR(80)" part of the type declaration in the metadata tables?

I tried looking at the systypes table, but its values for xtype do not match up to the values of xtype in the sysobjects table.

*I do not have access to the original SQL used to build these tables nor do I have any admin rights.

If you're familiar with DB2 I'm looking for the equivalent to

select name,
       coltype,
       length,
  from sysibm.syscolumns
where tbname = 'FOO'

Solution 1

You are close. You can look at sys.columns to get the columns.

You can filter on a table with OBJECT_ID=OBJECT_ID('dbo.Foo').

You can get the length from sys.columns. The data type is in the user_type_id field. The keys for that field are in sys.types.

In its entirety you can do:

select object_NAME(c.object_id), c.name, t.name, c.max_length
from sys.columns c
INNER JOIN sys.types t
    ON t.user_type_id = c.user_type_id

As a side note, in SQL Server the system tables are deprecated (i.e. syscolumns, sysobjects) and it's recommended as a best practice to use the views instead, sys.columns, sys.objects, etc.

This will give you Table, column, data type, and maxlength for each one.

Solution 2

The correct way to do this is to join to user_type_id in the sys.types table:

select object_NAME(c.object_id), c.name, t.name, c.max_length
from sys.columns c
INNER JOIN sys.types t
    ON t.user_type_id = c.user_type_id

user_type_id is identical to system_type_id for system types - see documentation: https://msdn.microsoft.com/en-gb/library/ms188021.aspx

Solution 3

To build on the answers above, it's often useful to get the column data type in the same format that you need to declare columns.

For example, varchar(50), varchar(max), decimal(p, s).

This allows you to do that:

SELECT 
  [Name]         = c.[name]
, [Type]         = 
    CASE 
      WHEN tp.[name] IN ('varchar', 'char') THEN tp.[name] + '(' + IIF(c.max_length = -1, 'max', CAST(c.max_length AS VARCHAR(25))) + ')' 
      WHEN tp.[name] IN ('nvarchar','nchar') THEN tp.[name] + '(' + IIF(c.max_length = -1, 'max', CAST(c.max_length / 2 AS VARCHAR(25)))+ ')'      
      WHEN tp.[name] IN ('decimal', 'numeric') THEN tp.[name] + '(' + CAST(c.[precision] AS VARCHAR(25)) + ', ' + CAST(c.[scale] AS VARCHAR(25)) + ')'
      WHEN tp.[name] IN ('datetime2') THEN tp.[name] + '(' + CAST(c.[scale] AS VARCHAR(25)) + ')'
      ELSE tp.[name]
    END
, [RawType]      = tp.[name]
, [MaxLength]    = c.max_length
, [Precision]    = c.[precision]
, [Scale]        = c.scale
FROM sys.tables t 
JOIN sys.schemas s ON t.schema_id = s.schema_id
JOIN sys.columns c ON t.object_id = c.object_id
JOIN sys.types tp ON c.user_type_id = tp.user_type_id
WHERE s.[name] = 'dbo' AND t.[name] = 'MyTable'

Comments

[Simon Zeinstra]

This is the better answer. JNK's anwer gave me duplicates

[George Menoutis]

binary length missing