Member Avatar for tonyaim83

How do I get ONLY data type and name of all the columns available in given table_name

  • 3 Contributors
  • 2 Replies
  • 199 Views
  • 5 Days Discussion Span
  • Latest Post Latest Post by Member #25180

Dani AI

Generated

Short answer: both approaches already shown are valid— gave the old sysobjects/syscolumns route and pointed to INFORMATION_SCHEMA.COLUMNS (good, portable). If you need a single clean column that shows the type with length/precision (e.g. varchar(50), nvarchar(max), decimal(10,2)) and want to exclude computed columns, using the modern catalog views is the most robust option:

SELECT
  c.name AS column_name,
  t.name +
    CASE
      WHEN t.name IN ('varchar','char','varbinary','binary')
        THEN '(' + CASE WHEN c.max_length = -1 THEN 'max' ELSE CAST(c.max_length AS VARCHAR(10)) END + ')'
      WHEN t.name IN ('nvarchar','nchar')
        THEN '(' + CASE WHEN c.max_length = -1 THEN 'max' ELSE CAST(c.max_length/2 AS VARCHAR(10)) END + ')'
      WHEN t.name IN ('decimal','numeric')
        THEN '(' + CAST(c.precision AS VARCHAR(10)) + ',' + CAST(c.scale AS VARCHAR(10)) + ')'
      ELSE ''
    END AS data_type
FROM sys.columns c
JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE c.object_id = OBJECT_ID(N'schema.TableName')  -- replace with your schema.TableName
  AND c.is_computed = 0
ORDER BY c.column_id;

Notes and gotchas: max_length is in bytes (divide by 2 for nvarchar/nchar), -1 means max. Use OBJECT_ID('schema.TableName') so the correct schema is targeted; if you want computed columns keep or remove c.is_computed = 0. INFORMATION_SCHEMA is fine when you only need the base data_type plus portability, but it doesn’t produce the formatted type string above. If you need nullable/identity info add c.is_nullable or c.is_identity. Replace schema.TableName and run in the correct database.

Recommended Answers

All 2 Replies

Member Avatar for dickersonka

here is a couple more columns than you need, just so you can be clear on the query

SELECT
sysobjects.name AS "TABLE_NAME", 
syscolumns.name AS "COLUMN_NAME", 
systypes.name AS "DATA_TYPE", 
syscolumns.LENGTH AS "LENGTH" 
FROM         
	sysobjects 
INNER JOIN 
	syscolumns ON sysobjects.id = syscolumns.id 
INNER JOIN                      
	systypes ON syscolumns.xtype = systypes.xtype 
WHERE     
(sysobjects.xtype = 'U') and
sysobjects.name = 'MyTableName'
ORDER BY sysobjects.name, syscolumns.colid

substitute your table name in for 'MyTableName'

Member Avatar for Member #25180

In both SQL 2000 and 2005 you can run this query using the information_schema.columns

SELECT
     data_type,
     column_name
FROM information_schema.columns
WHERE table_name = 'table'

This should do it, just make sure to run this in the correct database. If you have two table names with the same name in different schemas you may need to change the WHERE clause to this

WHERE table_name = 'table'
AND table_schema = 'schema'
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.