Hamed Hamed - 1 year ago 68
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 Source

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