Getting column information using T-SQL

<< SQLExamples: A great new SQL Server resource | Home | Finding Notification Services Instances >>

Getting column information using T-SQL

Ocassionally, I see a question in the MSDN Forums where someone wants to know how to retrieve a list of column information for a table using only T-SQL.

Although there are several ways in which to approach this, I like the following method.

SELECT 
   ORDINAL_POSITION
  ,COLUMN_NAME
  ,DATA_TYPE
  ,CHARACTER_MAXIMUM_LENGTH
  ,IS_NULLABLE
  ,COLUMN_DEFAULT
FROM   
  INFORMATION_SCHEMA.COLUMNS 
WHERE   
  TABLE_NAME = 'Product' 
ORDER BY 
  ORDINAL_POSITION ASC;

Do you have a different way of doing this that you'd like to share? If so, feel free to leave a comment, sharing your way and why you like it.

Cheers!

Joe

Print | posted on Sunday, April 27, 2008 9:05 AM

Feedback

# re: Getting column information using T-SQL

Nice and simple, just saved for future use ;)

4/27/2008 11:37 AM | Bahador

# re: Getting column information using T-SQL

Nice! I just made it into a hotkey. Highlight a table in single quotes and voila!
Thanks for the tip.

4/28/2008 7:19 AM | Jeff DeLuca

# re: Getting column information using T-SQL

what about sp_help 'tablename'

4/28/2008 9:15 PM | prashanth

# re: Getting column information using T-SQL

sp_help 'tablename' is another great way. I tend to use it when working interactively at a query window.

Since it returns multiple resultsets (7 I think), it may be a bit much for someone who's interested in only the basic information. Or for someone who is creating a script for installation, configuration, etc. That's more what I was thinking for this post.

Thanks for pointing it out, though! It's a better alternative for the interactive stuff.

Joe

4/29/2008 3:19 AM | Joe Webb

# re: Getting column information using T-SQL

Couldn't you also use the syscolumns table (in every db) and choose which areas you want information for?

Granted you would need to do some additional set up work for the query to make it more human readable (several columns refer to IDs of the data so you can use that to get the data versus providing the actual data itself but there are 23 columns of data in information_schema.columns versus 32 in the syscolumns database. Then again additional data if it is not needed has no value.

select * from syscolumns
where id=object_id('Customers')
ORDER BY
colid ASC

4/29/2008 10:23 PM | ToniMarieM

# re: Getting column information using T-SQL

Absolutely. This technique is more backwards compatible. But as you pointed out, it's also a little more convoluted in some respects.

Thanks for sharing!

Joe

4/30/2008 1:21 AM | Joe Webb

# re: Getting column information using T-SQL

Nice script. Very helpful.

4/30/2008 11:08 AM | TKnoob

# re: Getting column information using T-SQL

sql server2005 or sql server 2008？

6/9/2008 10:54 PM | net205

# re: Getting column information using T-SQL

The INFORMATION_SCHEMA views are part of the ANSI standard so it should apply to SQL Server 2005 and beyond. As an aside, it also applies to other non-Microsoft platforms such as MySQL.

6/14/2008 10:29 AM | Joe Webb

Post Comment

Title
Name
Email
Url
Comment
Remember Me?
Please add 5 and 7 and type the answer here: Enter the code shown above:

Joe Webb

My Links

SQLTeam.com Links

News

Archives

Post Categories

About me