Get All Columns in a Table Schema Information In One Click

Overview

In this article, we will learn how to get all the column information of a table with one click, including Column Name, DataType, Data Length, Column Description, etc. I found this technique very useful when I had to share the table column information along with some sample data into an Excel sheet with other clients.

Sample Script

In this script, we need to pass only the table name and schema name.

set @TableSchema ='dbo' --Schema name table 'Item' set @TableName= 'Item' -- Name of table.

declare @TableSchema varchar(50)
declare @TableName varchar(50)

set @TableSchema ='dbo' --Edit as per your table schema
set @TableName= 'Item' --Edit as per your table name

select @TableSchema +'.' +@TableName TableName

SELECT ORDINAL_POSITION AS [SNo],

COLUMN_NAME AS [Column Name], 

DATA_TYPE AS [Data Type],

CASE 
    WHEN DATA_TYPE ='decimal'  THEN  '(' + convert(varchar(20),NUMERIC_PRECISION) + ','  + convert(varchar(20),NUMERIC_SCALE) + ')'
    WHEN CHARACTER_MAXIMUM_LENGTH IS NULL THEN ''
    WHEN CHARACTER_MAXIMUM_LENGTH ='-1' then 'MAX'
    ELSE CONVERT(varchar(50), CHARACTER_MAXIMUM_LENGTH) 
End  AS [Length],

CASE WHEN COL.IS_NULLABLE = 'No' THEN 'Y' ELSE 'Yes' End [Is Mandatory],

ISNULL(prop.value,'') [Description]

FROM INFORMATION_SCHEMA.TABLES AS tbl
INNER JOIN INFORMATION_SCHEMA.COLUMNS AS col 
ON col.TABLE_NAME = tbl.TABLE_NAME
AND tbl.TABLE_SCHEMA=col.TABLE_SCHEMA
AND tbl.table_schema=@TableSchema 
AND tbl.TABLE_NAME = @TableName
INNER JOIN sys.columns AS sc 
ON sc.object_id = object_id(tbl.table_schema + '.' + tbl.table_name)
AND sc.NAME = col.COLUMN_NAME
AND tbl.TABLE_SCHEMA=@TableSchema 
AND tbl.TABLE_NAME = @TableName
LEFT JOIN sys.extended_properties AS prop 
ON prop.major_id = sc.object_id
AND prop.minor_id = sc.column_id
AND prop.NAME = 'MS_Description'
WHERE tbl.table_schema=@TableSchema and tbl.TABLE_NAME = @TableName
order by ORDINAL_POSITION

When you execute this script, it shows the column information like the following screenshot,

How to Prepare Excel Sheet With Sample Data

Step 1

Copy this result data with Headers information. Right click on the first cell -> click on the menu [Copy with Headers] and paste it into your excel sheet.

Step 2

Now, we have to paste few sample data into this excel sheet. For this, I will execute the following script.

Select top 2 * From dbo.Item  --

Step 3

Now copy the result and data without header columns.

Step 4

Now paste this copied data into another excel sheet.

Step 5

Now again copy this data from the excel sheet where you have pasted it.

Step 6

Now transpose this data into the first sheet where you have pasted column information.

Note
In Excel paste options, you can find this transpose option which will paste your records in column format.

Finally, it will look like the following screenshot,