April 25, 2013 at 10:41 am
Hello Everyone!
I'm working on a query to pull table name, schema, column name, data type, and field length; just can't get the data type right. Here is what I have so far:
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name, t.name AS type_name, c.max_length AS length
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
ORDER BY schema_name, table_name;[/b]
t.name AS type_name returns table name, I need to have it return the data type. It works perfectly for my needs, ok, except that part. 😛
Thank YOU in advance for your assistance 😀
Wren
April 25, 2013 at 10:45 am
karen.richardson (4/25/2013)
Hello Everyone!I'm working on a query to pull table name, schema, column name, data type, and field length; just can't get the data type right. Here is what I have so far:
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name, t.name AS type_name, c.max_length AS length
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
ORDER BY schema_name, table_name;[/b]
t.name AS type_name returns table name, I need to have it return the data type. It works perfectly for my needs, ok, except that part. 😛
Thank YOU in advance for your assistance 😀
Wren
You need to add the table sys.types to your query. From sys.columns you can join to sys.types using the user_type_id column.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply