How do I get the column type and length. SQL help needed

Question

How do I get the column type and length. SQL help needed

mw_sql_developer

SSCoach

Points: 19445
More actions
February 23, 2015 at 8:12 am

#295441

Hello and Good Day.
Below is a SQL statement that shows what columns belong to a certain view. My next question is how can I get a third column that shows the column type ?
VARCHAR(100), or UNIQUEIDENTIFIER or INTEGER or TEXT or NVARCHAR(256) or ........????
Please
select top 100 a.name, b.name, from sys.views a inner join sys.columns b
on a.object_id = b.object_id where a.name like '%Case_Times%'

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Eric M Russell SSC Guru Points: 125596 More actions · Answer 1

mw112009 (2/23/2015)
Hello and Good Day.
Below is a SQL statement that shows what columns belong to a certain view. My next question is how can I get a third column that shows the column type ?
VARCHAR(100), or UNIQUEIDENTIFIER or INTEGER or TEXT or NVARCHAR(256) or ........????
Please
select top 100 a.name, b.name, from sys.views a inner join sys.columns b
on a.object_id = b.object_id where a.name like '%Case_Times%'

The SYS.COLUMNS table contains columns SYSTEM_TYPE_ID, MAX_LENGTH, PRECESION, and SCALE. The table SYS.TYPES can be joined to get data type description.

Also the INFORMATION_SCHEMA.COLUMNS view has the various text descriptions already joined in.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

mw_sql_developer SSCoach Points: 19445 More actions · Answer 2

Cool

But I would appreciate someone writing that query for me please

SQL Severe DBA Hall of Fame Points: 3579 More actions · Answer 3

Probably something like this .

select top 100 a.name, b.name,sc.DATA_TYPE from sys.views a inner join sys.columns b

on a.object_id = b.object_id

INNER JOIN INFORMATION_SCHEMA.COLUMNS SC ON b.name = sc.COLumn_name

where a.name like '%Case_Times%'

Lowell SSC Guru Points: 323546 More actions · Answer 4

SELECT TOP 100 a.name,

b.name,

type_name(b.system_type_id) AS DataType,

CASE

WHEN LEFT(type_name(b.system_type_id), 2) IN('nt','nv') THEN b.max_length / 2

ELSE b.max_length

END AS DataSize,

b.column_id

FROM sys.views a

INNER JOIN sys.columns b

ON a.object_id = b.object_id

--WHERE a.name LIKE '%Case_Times%'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!