Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Assistance w/query that pulls table name, col name, data type Expand / Collapse
Author
Message
Posted Thursday, April 25, 2013 10:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 25, 2013 11:47 AM
Points: 4, Visits: 8
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]
[/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
Post #1446612
Posted Thursday, April 25, 2013 10:45 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:10 PM
Points: 23,009, Visits: 31,507
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]
[/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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1446614
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse