Category >
MYSQL
|| Published on :
Friday, February 26, 2016 || Views:
10289
||
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