October 5, 2011 06:48 by
Scott
In this tutorial, I will show you how to get column name and corresponding datatypes in particular table using SQL Server.
Description:
I have one table with lot columns in database at that time I tried to know the column names and corresponding datatypes and their maximum sizes in particular table for that I written the following query in SQL server to get column names and datatypes in particular table.
USE MySampleDB
GO
SELECT column_name 'Column Name',
data_type 'Data Type',
character_maximum_length 'Maximum Length'
FROM information_schema.columns
WHERE table_name = 'Country'
Demo
If anyone gets error with above query like
Invalid object name 'information_schema.columns'
This error because of case sensitive databases to rectify this error we need to write query like this
USE CRMK
GO
SELECT column_name 'Column Name',
data_type 'Data Type',
character_maximum_length 'Maximum Length'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'Country'
Here you need to enter your table name if that is in Caps lock you should enter caps lock name of your table only because it it is case sensitive.