Sql query Script to get the columns with datatype of tables with default values

Category > MYSQL || Published on : Friday, February 26, 2016 || Views: 9354 || Sql query Script to get the columns with datatype of tables with default values


Introduction

Here Pawan Kumar will explain how to Sql query Script to get the columns with datatype of tables with default values

Description

In previous post I have explained How to bind dropdown list with XML file in asp.net?, Validate ASP.Net form using CSS in C#, API Demonstration ASP.NET, Age Calculator using ASP.NET & C#, How to Access AppSettings in Code Behind File, Increse Decrease Font Size or Zoom In Zoom Out Text Size Using jQuery, and many more articles.

Now I will explain How to Sql query Script to get the columns with datatype of tables with default values

So follow the steps to learn Sql query Script to get the columns with datatype of tables with default values

 

Sql query Script to get the columns with datatype of tables with default values

Note: for this example we will use northwind database from microsoft website.

Step 1: Right click on the northwind database and click "New Query" the write the below query:-

 SELECT TableName=Table_schema+'.'+Table_Name,
ColumnName=COLUMN_NAME,DataType=UPPER(DATA_TYPE)
+CASE WHEN CHARACTER_MAXIMUM_LENGTH IS NULL THEN ' ' ELSE '('+CONVERT(VARCHAR(5), CHARACTER_MAXIMUM_LENGTH)+') ' END
+CASE WHEN IS_NULLABLE='YES' THEN 'NULL ' ELSE 'NOT NULL ' END+CASE WHEN Column_Default IS NULL THEN '' ELSE 'DEFAULT('+Column_Default+'),' END
FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_Name='CustomerCustomerDemo'

Output will be like below screenshot:-

Conclusion:

So, In this tutorial we have learned, Sql query Script to get the columns with datatype of tables with default values