Where do I find Sql Server metadata for column dat

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'

Answer 1

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

Answer 2

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 system_type field. The keys for that field are in sys.types.

In it's 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.system_type_id = c.system_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.