MS SQL

How do I get ONLY DATATYPE and NAME of all the columns available in given table_name

Please support our MS SQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Aug 2007

Posts: 49

Reputation: tonyaim83 is an unknown quantity at this point

Solved Threads: 0

tonyaim83

Offline

Light Poster

How do I get ONLY DATATYPE and NAME of all the columns available in given table_name

Oct 20th, 2008

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

•

Join Date: Aug 2008

Posts: 1,158

Reputation: dickersonka will become famous soon enough

Solved Threads: 136

dickersonka dickersonka is offline

Offline

Veteran Poster

Re: How do I get ONLY DATATYPE and NAME of all the columns available in given table_name

Oct 20th, 2008

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

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
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
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'

Custom Application & Software Development
www.houseshark.net

•

Join Date: Dec 2004

Posts: 149

Reputation: Geek-Master is an unknown quantity at this point

Solved Threads: 6

Geek-Master Geek-Master is offline

Offline

Junior Poster

Re: How do I get ONLY DATATYPE and NAME of all the columns available in given table_name

Oct 25th, 2008

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

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
SELECT
     data_type,
     column_name
FROM information_schema.COLUMNS
WHERE table_name = 'table'
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

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
WHERE table_name = 'table'
AND table_schema = 'schema'
WHERE table_name = 'table'
AND table_schema = 'schema'