Joe Webb

Musing and observations about SQL Server, other technogies, and sometimes just life in general
posts - 85, comments - 857, trackbacks - 0

My Links

News

This blog has moved!

Click here for the new location.

Follow me on Twitter
Add to Technorati Favorites
Search this Blog
 



Archives

Post Categories

About me
<< 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


kick it on DotNetKicks.com

Print | posted on Sunday, April 27, 2008 9:05 AM | Filed Under [ T-SQL ]

Feedback

Gravatar

# re: Getting column information using T-SQL

Nice and simple, just saved for future use ;)
4/27/2008 11:37 AM | Bahador
Gravatar

# 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
Gravatar

# re: Getting column information using T-SQL

what about sp_help 'tablename'
4/28/2008 9:15 PM | prashanth
Gravatar

# 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
Gravatar

# 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
Gravatar

# 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
Gravatar

# re: Getting column information using T-SQL

Nice script. Very helpful.
4/30/2008 11:08 AM | TKnoob
Gravatar

# re: Getting column information using T-SQL

sql server2005 or sql server 2008？
6/9/2008 10:54 PM | net205
Gravatar

# 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
Gravatar

# re: Getting column information using T-SQL

Very Nice.

Also, I tried Information_Schema.tables, to get the list of all tables.

Select * from Information_Schema.Tables

Looks like Information_schema schema has wealth of data stored in its tables. For a complete list of tables, refer to following link, I just found on web:

http://developer.mimer.com/documentation/html_91/Mimer_SQL_Engine_DocSet/Data_dic_views2.html
1/25/2009 9:40 PM | Ajay Aggarwal
Gravatar

# re: Getting column information using T-SQL

I normally need to find columns so I to use the INFORMATION_SCHEMA.COLUMNS

USE [database]

DECLARE @col NVARCHAR(50)
SET @col = N'CommentCode%' -- Change to object name

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE @col
6/19/2009 11:53 AM | sqlsamson
Gravatar

# re: Getting column information using T-SQL

use GenApp
go

--TSQL SNIPPET FOR LISTING TABLE COLUMNS AS LIST FOR SELECT , UPDATE INSERT STATEMENTS
set nocount off;
--SEARCH FOR A TABLE WHICH HAS "GuiPage" IN ITS NAME
select name AS 'TABLE_NAME' from sys.tables where name like '%History%'

--PRINT THE COLUMNS OF THIS TABLE
DECLARE @ColName varchar(100)
DECLARE @cursorColNames CURSOR
SET @cursorColNames = CURSOR FOR
select column_name from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME ='GuiPageHistory'

OPEN @cursorColNames
FETCH NEXT
FROM @cursorColNames INTO @ColName
WHILE @@FETCH_STATUS = 0
BEGIN


--exec sp_HelpText @ColName --- or print them
print '[' + @ColName + '],'
--print 'obj1.' + @ColName + ' = obj2.'+ @ColName

FETCH NEXT
FROM @cursorColNames INTO @ColName
END
CLOSE @cursorColNames


select @@error
9/8/2009 2:11 AM | Yordan Georgiev
Gravatar

# re: Getting column information using T-SQL

use GenApp
go

--TSQL SNIPPET FOR LISTING TABLE COLUMNS AS LIST FOR SELECT , UPDATE INSERT STATEMENTS
set nocount off;
--SEARCH FOR A TABLE WHICH HAS "GuiPage" IN ITS NAME
select name AS 'TABLE_NAME' from sys.tables where name like '%History%'

--PRINT THE COLUMNS OF THIS TABLE
DECLARE @ColName varchar(100)
DECLARE @cursorColNames CURSOR
SET @cursorColNames = CURSOR FOR
select column_name from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME ='GuiPageHistory'

OPEN @cursorColNames
FETCH NEXT
FROM @cursorColNames INTO @ColName
WHILE @@FETCH_STATUS = 0
BEGIN


--exec sp_HelpText @ColName --- or print them
print '[' + @ColName + '],'
--print 'obj1.' + @ColName + ' = obj2.'+ @ColName

FETCH NEXT
FROM @cursorColNames INTO @ColName
END
CLOSE @cursorColNames


select @@error
9/8/2009 2:11 AM | Yordan Georgiev
Gravatar

# re: Getting column information using T-SQL

--CODE SNIPPET TO LIST TABLE COLUMNS
-- RUN IN SSMS WITH cTRL + t FIRST TO OUTPUT THE RESULT TO TEXT FOR COPY PASTE

--FIRST SEARCH THE TABLE WHICH HAD A "Feature" in its name
--SELECT NAME FROM SYS.TABLES WHERE NAME LIKE '%Feature%'

--select column_name from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='Feature'
--Declare the Table variable
DECLARE @ColNames TABLE
(
Number INT IDENTITY(1,1), --Auto incrementing Identity column
ColName VARCHAR(300) --The string value
)
--Decalre a variable to remember the position of the current delimiter
DECLARE @CurrentDelimiterPositionVar INT
--Decalre a variable to remember the number of rows in the table
DECLARE @Count INT
--Populate the TABLE variable using some logic
INSERT INTO @ColNames SELECT column_name from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='Feature'
--Initialize the looper variable
SET @CurrentDelimiterPositionVar = 1
--Determine the number of rows in the Table
SELECT @Count=max(Number) from @ColNames
--A variable to hold the currently selected value from the table
DECLARE @CurrentValue varchar(300);
--Loop through until all row processing is done
WHILE @CurrentDelimiterPositionVar <= @Count
BEGIN
--Load current value from the Table
SELECT @CurrentValue = ColName FROM @ColNames WHERE Number = @CurrentDelimiterPositionVar
--Process the current value
if @CurrentDelimiterPositionVar = @Count
print '[' + @CurrentValue + ']' -- this is the last row no comma!
else
print '[' + @CurrentValue + '],' -- print it without the ,
-- print 'obj1.' + @CurrentValue+ ' = obj2.'+ @CurrentValue

-- print @CurrentValue --SIMPLE PRINT
--Increment loop counter
SET @CurrentDelimiterPositionVar = @CurrentDelimiterPositionVar + 1;
END

set nocount off
9/8/2009 2:27 AM | Yordan Georgiev
Gravatar

# re: Getting column information using T-SQL

--CODE SNIPPET TO LIST TABLE COLUMNS
-- RUN IN SSMS WITH cTRL + t FIRST TO OUTPUT THE RESULT TO TEXT FOR COPY PASTE

--FIRST SEARCH THE TABLE WHICH HAD A "Feature" in its name
--SELECT NAME FROM SYS.TABLES WHERE NAME LIKE '%Feature%'

--select column_name from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='Feature'
--Declare the Table variable
DECLARE @ColNames TABLE
(
Number INT IDENTITY(1,1), --Auto incrementing Identity column
ColName VARCHAR(300) --The string value
)
--Decalre a variable to remember the position of the current delimiter
DECLARE @CurrentDelimiterPositionVar INT
--Decalre a variable to remember the number of rows in the table
DECLARE @Count INT
--Populate the TABLE variable using some logic
INSERT INTO @ColNames SELECT column_name from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='Feature'
--Initialize the looper variable
SET @CurrentDelimiterPositionVar = 1
--Determine the number of rows in the Table
SELECT @Count=max(Number) from @ColNames
--A variable to hold the currently selected value from the table
DECLARE @CurrentValue varchar(300);
--Loop through until all row processing is done
WHILE @CurrentDelimiterPositionVar <= @Count
BEGIN
--Load current value from the Table
SELECT @CurrentValue = ColName FROM @ColNames WHERE Number = @CurrentDelimiterPositionVar
--Process the current value
if @CurrentDelimiterPositionVar = @Count
print '[' + @CurrentValue + ']' -- this is the last row no comma!
else
print '[' + @CurrentValue + '],' -- print it without the ,
-- print 'obj1.' + @CurrentValue+ ' = obj2.'+ @CurrentValue

-- print @CurrentValue --SIMPLE PRINT
--Increment loop counter
SET @CurrentDelimiterPositionVar = @CurrentDelimiterPositionVar + 1;
END

set nocount off
9/8/2009 2:27 AM | Yordan Georgiev
Gravatar

# re: Getting column information using T-SQL

I have used almost similar code in the past. I wrote an entry recently that is related to this post. My topic builds on to this by permitting the user to search for a particular data on any given database and column assuming that column can be converted to a varchar type.

See [http://www.rajib-bahar.com/rajib/BlogEngine.Web/post/2009/11/18/Search-for-data-on-any-given-database-and-on-any-column.aspx]
11/18/2009 9:11 PM | Rajib Bahar
Gravatar

# re: Getting column information using T-SQL

I'm working in an environment where I can't use sp_help because it doesn't return a single recordset. This works wonderfully! Thank you very much!
5/12/2010 5:34 PM | Patrick Dewey
Gravatar

# re: Getting column information using T-SQL

Thanks Patrick! I'm glad you've this useful.

By the way, I've moved my blog. I'm not blogging at http://www.webbtechsolutions.com/blog
5/12/2010 6:27 PM | Joe Webb
Gravatar

# re: Getting column information using T-SQL

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.

snow boots | columbia jackets | mac makeup | the north face outlet
10/20/2010 2:20 AM | snow boots for women
Gravatar

# re: Getting column information using T-SQL

You can get column information using SQL Locator.
You can also search for anything inside SQL Server databases without writing queries. Search results can be replaced, removed, saved, and shared with others. Easy to use and fully featured.

http://www.SQLLocator.com
11/27/2010 11:00 AM | corey bui
Gravatar

# re: Getting column information using T-SQL

Outstandingly helpful! This just helped me get some support information for one of my projects. Thanks!
7/11/2011 6:18 PM | Will Strohl
Gravatar

# re: Getting column information using T-SQL

I use the tablenames as well. It's been a lot quicker for me since most of what I do needs to be queriable. I looked into the SQL3 locater, but I'm not sure if I can get everything to transfer from my current structure.
10/26/2011 3:08 PM | applicant tracking software
Gravatar

# re: Getting column information using T-SQL

I use the code on my blog: www.sentientbeings.com/...

It uses no cursors and will give you table name, column name, datatype, precision and scale if applicable, nullable and identity settings and will indicate primary and foreign keys to boot.

1/13/2012 2:43 AM | Kristof Elst
Gravatar

# re: Getting column information using T-SQL

Thanks a lot!
2/28/2012 1:35 AM | Tu Cao Huu
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET

Copyright © Joe Webb