Select Column Information using SQL Server

Use this T-SQL to select infomation about a specific table/columns, Useful for automated scripts.

DECLARE @tablename varchar(100)
SET @tablename = N'myTable'
SELECT AS [Name], AS [DataType],
ISNULL(, N''AS [SystemType],
CAST(CASE WHEN IN (N'nchar', N'nvarchar'AND clmns.max_length <> -1 THEN
clmns.max_length/2 ELSE clmns.max_length END AS int) AS [Length],
CAST(clmns.precision AS int) AS [NumericPrecision]
sys.tables AS tbl
INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tbl.object_id
LEFT OUTER JOIN sys.types AS usrt ON usrt.user_type_id = clmns.user_type_id
LEFT OUTER JOIN sys.types AS baset ON baset.user_type_id = clmns.system_type_id and 
baset.user_type_id = baset.system_type_id
( and SCHEMA_NAME(tbl.schema_id)=N'dbo')
clmns.column_id ASC

By the way, I worked this out using SQL Profiler. I switched on a trace and then clicked on a table to show columns. The trace then showed some SQL similar to this (more complex) and I just rendered down what I needed.

The point I am making is that SQL Profiler turned out to be a handy resource on the sys schema.

Then again it has been pointed out to me that the following is also valid:

select column_name, data_type, character_maximum_length from information_schema.columns
where table_name = 'myTable'
Author Dave Howard

I have been involved in IT development for the last 10 years - a lot of it around desktop applications and telecoms.


Damien Guard said:

Selecting directly from the system tables is unsupported by Microsoft and is exactly why they created the sp_help stored procedure and it's associates.


19/Sep/2006 15:28 PM

Damien Guard said:

Alternatively SQL 2005 adds metadata support for tables and columns using the syntax:


Where Products is the table you want.


19/Sep/2006 15:34 PM

Jason said:

There is a built in stored procedure called sp_columns that takes a tablename as a parameter. this returns all information for the given table


sp_columns Employees

13/Jun/2007 03:26 AM

nikhil said:

Thanks Jason, This is what i was looking for

14/Oct/2010 04:46 AM

Add Comment


Your comment has been received and will be shown once it passes moderation.