Aug 5, 2016

How to get name, data type and fields size of table in SQL Server

Sometimes we need to see the column details like name, data type and size, this article may help you to find the details of column is database table.
Suppose we have an”Inventory” table as below.

CREATE TABLE [dbo].[Inventory2](
            [ID] [bigint] IDENTITY(1,1) PRIMARY KEY NOT NULL,
            [Title] [varchar](500) NULL,
            [SKU] [varchar](20) NULL,
            [Price] [decimal](18, 2) NULL,
            [Created_Date] [datetime] NULL,
            [Updated_Date] [datetime] NULL,

And we need to see the details of above table’s columns, run the following script it may help to find the details of table’s columns like Name, Data_Type, and Length.

SELECT column_name as 'COL_NAME', data_type as 'Data_Type',
Col_Length(table_name,column_name) as 'MAX_LENGTH'
FROM information_schema.columns
WHERE table_name = 'Inventory'

COL_NAME       Data_Type    MAX_LENGTH
-------------- ------------ ----------
ID                     bigint          8
Title                  varchar       500
SKU                  varchar       20
Price                 decimal       9
Created_Date    datetime      8
Updated_Date   datetime      8