How can I complete my current query about User Defined Table Types and getting Schema,IsIdentity,IsPrimaryKey and ...?

Question

I have a query for getting information about User Defined Table Types.

I need to complete this query by adding the following columns :

IsIdentity
IsPrimaryKey
Schema
ColumnDefaultValue

The query:

SELECT  o.name AS TableName ,
        c.name AS ColumnName ,
        c.isnullable AS [IsNullable] ,
        t.name AS [DataType] ,
        t.[length] AS [MaxLength] ,
        t.prec AS [Precision]
FROM    syscolumns c
        INNER JOIN sysobjects o ON o.id = c.id
        LEFT JOIN systypes t ON t.xtype = c.xtype
WHERE   c.id IN ( SELECT    type_table_object_id
                  FROM      sys.table_types )
ORDER BY o.name ,
        c.name;

And I have another question about the above query.

I have User Defined Table Types with dbo.MyType as name but in this query it shows me 'TT_MyType_37C5420D'.

How can I get the real name ?

Answer 1

You should use the new catalog views from the sys schema as of SQL Server 2005 and avoid the old syscolumns, sysobjects etc.

When rewriting your query to this:

SELECT  
    tt.name AS TableName,
    c.name AS ColumnName,
    c.is_nullable AS [IsNullable],
    c.is_identity,
    t.name AS [DataType],
    t.max_length [MaxLength],
    t.precision AS [Precision]
FROM
    sys.table_types tt
INNER JOIN 
    sys.columns c ON c.object_id = tt.type_table_object_id
LEFT JOIN 
    sys.types t ON t.system_type_id = c.system_type_id
ORDER BY 
    tt.name, c.name;

you can easily tap into the more extensive information available in the sys catalog view - like the c.is_identity column on sys.columns. And also - the name from sys.table_types seems to return the more humanly readable name as you were looking for.

Check out the very extensive MSDN documentation on the sys catalog views do discover a lot more information you might be interested in