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

Expand Post »

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

tonyaim83

Reputation Points: 8

Solved Threads: 0

Light Poster

Offline

49 posts
since Aug 2007

Permalink

Oct 20th, 2008

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

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

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'

dickersonka

Reputation Points: 133

Solved Threads: 141

Veteran Poster

Offline

1,162 posts
since Aug 2008

Permalink

Oct 25th, 2008

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

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

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

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

Geek-Master

Reputation Points: 12

Solved Threads: 6

Junior Poster

Offline

156 posts
since Dec 2004

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: Trouble Ticket System

Next Thread in MS SQL Forum Timeline: Need help with a trigger

DaniWeb Message

Cancel Changes

User Name
Password