Joe Webb Blog

Musing and observations about SQL Server, other technogies, and sometimes just life in general

Getting column information using T-SQL

by Joe Webb in  

 

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

Legacy Comments
Bahador
2008-04-27		 re: Getting column information using T-SQL
Nice and simple, just saved for future use ;)
Jeff DeLuca
2008-04-28		 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.
prashanth
2008-04-28		 re: Getting column information using T-SQL
what about sp_help 'tablename'
Joe Webb
2008-04-29		 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
ToniMarieM
2008-04-29		 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
Joe Webb
2008-04-30		 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
TKnoob
2008-04-30		 re: Getting column information using T-SQL
Nice script. Very helpful.
net205
2008-06-09		 re: Getting column information using T-SQL
sql server2005 or sql server 2008？
Joe Webb
2008-06-14		 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.

Ajay Aggarwal
2009-01-25		 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
sqlsamson
2009-06-19		 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
Yordan Georgiev
2009-09-08		 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
Yordan Georgiev
2009-09-08		 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
Yordan Georgiev
2009-09-08		 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
Yordan Georgiev
2009-09-08		 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
Rajib Bahar
2009-11-18		 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]
Patrick Dewey
2010-05-12		 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!
Joe Webb
2010-05-12		 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
snow boots for women
2010-10-20		 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
corey bui
2010-11-27		 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
Will Strohl
2011-07-11		 re: Getting column information using T-SQL
Outstandingly helpful! This just helped me get some support information for one of my projects. Thanks!
applicant tracking software
2011-10-26		 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.
Kristof Elst
2012-01-13		 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.
Tu Cao Huu
2012-02-28		 re: Getting column information using T-SQL
Thanks a lot!