SQL Server - Get Column Name and Type From Table

CODING

Microsoft SQL Server provides two methods for obtaining meta data: system stored procedures or information schema views.

Here are the SQL snippets for getting meta information about a database table in SQL Server. These queries could provide answer to the following requests:

- Advertisement -
- Advertisement -

  • How to get the name of all columns of a table in SQL Server
  • How to get colum and field type in SQL Server
  • How to get meta data about SQL Server table

Solution #1

SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
FROM information_schema.columns
WHERE TABLE_NAME = [table_name]
ORDER BY COLUMN_NAME

Solution #2

SELECT * FROM sys.columns
WHERE object_id = OBJECT_ID('dbo.[table_name]')

We prefer the first solution over the second one (that gets information from "sys.columns") because in the frist case the query shows DATA_TYPE directly.

- Advertisement -