Hamed Hamed - 5 months ago 7
SQL Question

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

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 above query,

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

How Can I get real name ?

Answer

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