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

Recommended Answers

All 2 Replies

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 Geek-Master

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.