get basic SQL Server table structure information

27

I can get the number of columns in an SQL Server database with this:

SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'Address'

But is there any way (for an unknown number of columns) I can get the name and datatype and length of each column?

This question is tagged with sql sql-server

~ Asked on 2013-02-11 20:10:06

The Best Answer is


59

Instead of using count(*) you can SELECT * and you will return all of the details that you want including data_type:

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'Address'

MSDN Docs on INFORMATION_SCHEMA.COLUMNS

~ Answered on 2013-02-11 20:13:54


25

sp_help will give you a whole bunch of information about a table including the columns, keys and constraints. For example, running

exec sp_help 'Address' 

will give you information about Address.

~ Answered on 2013-02-11 20:14:16


Most Viewed Questions: