How to generate Select query with all the columns from a table quickly in SQL Server - SQL Server / TSQL Tutorial Part 107

Scenario:

You are new to SQL Server and you want to select all the columns from SQL Server Table to see the data in SSMS.

Solution:

There are multiple ways to quickly select all the columns from a table and run the query to display data for them.

1) Use * 

You can use Select * From SchemaName.TableName

See the green highlighted *, once you use in your Select query , it is going to return all the column from table.

2) Use Select Top X Rows in SSMS
Go to Database in which your table exists, Right Click on the table and then choose Select top X row (x= depends upon your setting for SSMS) and it will generate select query with all the column from a table for you.

How to generate Select query with all the column for SQL Server Table
The below query will be generate with all the column from the table you have selected.
Select all columns quickly from a table for Select query in SQL Server

 3) Drag the Columns to Query Window
If you are not interested to use above two methods, you can open new query window , type Select and then drag the columns tab from the table you would like to use the columns. it will bring all the columns.
How to select all the columns from a table for Select query in SQL Server

Generate Select query with all the columns from a table quickly in SQL Server

Now you can write the rest of the part such as from SchemaName.TableName.

By knowing above methods, it really helps to prepare your queries quickly as sometime we have to select all columns and exclude couple of them. We can generate the query with all the  columns and then delete the columns which are not required from query.



How to generate Insert Statements from Text Files for SQL Server Table in SQL Server - SQL Server / TSQL Tutorial Part 106

Scenario:

You are working as SQL Server developer, you get text files such as comma separate values, Tab delimited or pipe delimited files. It is one time task and you need to generate insert statements from these files so you can load the data into dbo.Customer table in DEV,QA,UAT and Production environments by using those scripts.

Solution:

I wrote a post , how to generate Insert statements from Excel file. Click here.  It means if we can open the text files with excel then we can use excel formulas to build insert statements.

If we have comma separate values file, you can directly open with excel and then use the steps shown in this post.

If you have tab delimited or pipe delimited file, first of all we need to open with excel and then use the formulas.

Step 1: 
Let's say that I have Tab delimited  Customer.txt file as shown below and want to open in excel.
How to generate Insert Statements from Tab delimited file for SQL Server Table

Step 2: 
Open Excel and then go to File and hit Open, Browse to customer.txt file
Generate Insert Statements from Text files for SQL Server Table 


Test Import Wizard will open, Choose Delimited and Click My Data has Headers if it does and  then Click Next
How to open Tab Delimited File in Excel to generate Insert statements for SQL Server Table

Choose the Delimiters, in our case it is Tab and hit Next.
How to Convert Tab Delimited File to Excel file and Generate Insert Statements for SQL Server Table

How to load Tab delimited file into Excel and Create Insert Statements for SQL Server Table


Once you will hit Finish, below excel sheet will be populated with flat file data.
How to generate Insert Statements from Excel File for SQL Server Table 

Now the data is in Excel file. we can use the excel formulas to generate insert statements as shown in this post.


Video Demo : How to generate Insert Statements from Flat File data for SQL Server Table

How to backup or create new table from Existing SQL Server Table in SQL Server - SQL Server / TSQL Tutorial Part 105

Scenario:

You are working as SQL Server developer, you need to provide some update or delete scripts to update or delete data from a table.You want to take the backup of those records or if the table is small you might want to backup entire table before you run update or delete statements.

How would you backup entire table or only the records on which you need to run update or delete statements?

Solution:

SQL Server does not provide Table level backup. When we say that we want to take the backup of the table, we are talking about making a copy of existing table with records.

Let's say if we have dbo.Customer Table with few records and we want to create backup table dbo.Customber_Bkp_TodayDate, we can use below script

First create dbo.Customer table with sample records

USE yourDatabaseName
Go
Create Table dbo.Customer(
Id int identity(1,1),
FName VARCHAR(50),
LName VARCHAR(50),
Age int,
DOB Date,
Salary Numeric(6,2))

--Use the Insert Into with Values single Statement
Insert into dbo.Customer
Values('Aamir','Shahzad',36,'1980-01-01',5000.50),
('Raza','M',33,'1983-03-03',4900.34),
('John','Smith',26,'1990-04-05',5000.50)



Now let's create dbo.Customber_Bkp_TodayDate backup table with all the records which are present in dbo.Customer.

Select * into dbo.Customber_Bkp_20160507 from dbo.Customer


To create new table with records, you have to use Into NewTable from OldTable as shown above.

If we are only interested to copy records where FName='Aamir' our query will be like below.

Select * into dbo.Customber_Bkp_20160507_OnlyAamir from dbo.Customer where FName='Aamir'


Run above scripts and check the tables if created with required records.
How to backup records into new table from existing SQL Server Table in SQL Server


Scripts used in the video demo:
--Take the backup or create table for all records
Select * into [YourDBName].dbo.Customer_20160510  from [dbo].[Customer]

--Create table for selected records
Select * into [YourDBName].dbo.Customer_20160510_TwoRecords from [dbo].[Customer]
where id<=2

--Check if table is created successfully
Select * From [YourDBName].dbo.Customer_20160510_TwoRecords

Select * from [dbo].[Customer]

--Update Records in current table
update [dbo].[Customer]
set LName=LName+' Test'
where id<=2


--Update records in current table from backup table
update d 
set d.LName=s.LName
from [YourDBName].dbo.Customer_20160510_TwoRecords s
inner join [dbo].[Customer] d
on s.id=d.id




Video Demo : How to create new table from existing table with data in SQL Server quickly


How to generate Insert Statements from Excel Data and Load into SQL Server Table - SQL Server / TSQL Tutorial Part 103

Scenario:

You are working as SQL Server Developer, you received data in an Excel file for dbo.Customer tables. You need to load this data into dbo.Customer table. How would you generate Insert Statement for given data in excel so you can load into DEV, QA,UAT and Production.

Solution:

There are multiple ways to achieve this requirement. You can use Import/Export Wizard first to load the data into Development environment and then generate insert script from table in SSMS.

If you don't want to load the data into table , you can use Excel formula's to generate Insert statement from data.

How to generate Insert Statements from Excel for SQL Server Table

First of all we need to know in which table we want to load the data. In our case it is dbo.Customer. Also the names of columns provided in Excel are exactly the same like our dbo.Customer table columns.
Go to any column and type this formula as shown below in first row. I typed in column G.

="insert into dbo.Customer ("&A1&","&B1&","&C1&","&D1&","&E1&") Values"

In Excel we start formula with = (equal to sign). For concatenation we use &. To add string we have to put double quotes around as we did for comma ",".

How to generate Insert Statements for SQL Server Table from Excel Data

Now first part of our Insert statement is ready. we have to generate the values part for our insert statement. Go to 2nd Row and use below formula.

="('"&A2&"','"&B2&"',"&C2&",'"&TEXT(D2,"dd/mm/yyyy")&"',"&E2&"),"

Drag it down to all the way till last record. All Insert values will be generate. Copy all the values from column G and paste into SSMS. There will be extra comma(,) at the end with last record. Remove that and run your insert statement to test if working as expected.

How to generate Insert Statements from Excel file for SQL Server table


Here are the generated Insert Statements, copied from G Column and pasted in SSMS.

Video Demo : How to generate Insert statement from Excel Records for SQL Server Table




How to insert Excel or CSV data into Table by using Graphical User Interface in SQL Server - SQL Server / TSQL Tutorial Part 102

Scenario:

You are working as SQL Server developer, you got an excel file or CSV file that contains data for dbo.Customer table. How can you insert Excel or CSV data to table by using Graphical user Interface.

Solution:

Step 1: 
Let's create dbo.Customer table first by using below script.

USE yourDatabaseName
Go
Create Table dbo.Customer(
Id int identity(1,1),
FName VARCHAR(50),
LName VARCHAR(50),
Age int,
DOB Date,
Salary Numeric(6,2))




Step 2:
Go to database , then tables and then right click on dbo.Customer and choose Edit Top X rows as per your settings.

How to insert Excel or CSV data to Table by using Graphical User Interface

Below windows will open that will let you insert the records into table dbo.Customer. 

How to insert records into table from Excel by using Graphical interface in SSMS

Step 3: 
Open the Excel or CSV file and copy the data as shown below. Don't copy the header as we don't want to insert header values into table.
Insert Excel data into SQL Server Table by using Graphical User Interface

Step 4:
Noticed that the columns and copied data has the same order. Come to SSMS and then paste the data.
How to insert Excel or CSV data into SQL Server Table Manually by using SSMS

As Id is identity ( Auto generated) column and we are trying to insert values in that, the excel data start with FName. As Fname data can not be inserted into Id, it will be ignored and rest of the data will be shift as well. The Last Name values are inserted into FName as can be seen below.

How to insert data from Excel or CSV to Table in SSMS

To avoid this, we should only select the columns in which we want to insert the data. Also select in the order in which we have the data from Excel or CSV.

Once you run the query, you will see the selected columns only. Go ahead and past the data as Step 4.
Insert data from Excel or CVS To SQL Server Table without using Import Wizard in SQL Server

Once you hit paste, data will be written to table as shown below.
How to insert data from Excel or CSV to SQL Server Table by using Edit window in SSMS

If you have few records, you might be ok with speed. If you have millions of records, inserting records by using Edit window can take long time. you might want to use Import Wizard in cases where you have huge number of records to insert into SQL Server Table.


Video Demo : How to insert Excel or CSV data into Table by using Copy Paste


How to insert rows in SQL Server Table by Edit Table Rows GUI - SQL Server / TSQL Tutorial Part 101

Scenario:

You are working as SQL Server developer, you need to insert couple of records into dbo.Customer table but you don't want to use TSQL scripts. How would you insert data into table without using TSQL?

Solution:

Let's create the dbo.Customer table first by using below script.

USE yourDatabaseName
Go
Create Table dbo.Customer(
Id int identity(1,1),
FName VARCHAR(50),
LName VARCHAR(50),
Age int,
DOB Date,
Salary Numeric(6,2))






Once the table is created. Go to Database and then Tables tab. Right click on the dbo.Customer table as shown and choose Edit Top X rows according to your setting.

How to insert data to SQL Server Table by using Graphical user interface in SQL Server

Now start typing the values you want to insert. As Id is identity column, we don't have to insert any value.
How to insert data into SQL Server Table by using Graphical user interface in SSMS

Once done with inserting the data, just close the windows and records will be saved to table.


Video Demo : How to insert records in SQL Server table by using Edit Top X rows

What are the different ways to insert data into SQL Server Table - SQL Server / TSQL Tutorial Part 100

Scenario:

As SQL Server Developer, you have to come with different ways to insert data into SQL Server Table.
Sometime you have to simply insert static records, sometime you have to insert data from another table to existing table. Let's check out different techniques to insert data into SQL Server table.

Solution:

Let's create the dbo.Customer Table first by using  below definition.

USE yourDatabaseName
Go
Create Table dbo.Customer(
Id int identity(1,1),
FName VARCHAR(50),
LName VARCHAR(50),
Age int,
DOB Date,
Salary Numeric(6,2))


1)  Use Multiple Inserts to insert data into table.


Insert into dbo.Customer(FName,LName,Age,DOB,Salary)
Values('Aamir','Shahzad',36,'1980-01-01',5000.50)
GO
Insert into dbo.Customer(FName,LName,Age,DOB,Salary)
Values ('Raza','M',33,'1983-03-03',4900.34)
GO
Insert into dbo.Customer(FName,LName,Age,DOB,Salary)
Values ('John','Smith',26,'1990-04-05',5000.50)



2) Use single Insert with multiple Values

As in above example, we use multiple inserts.Each was inserting single record. In SQL Server we can use single Insert with multiple values as shown below.

Insert into dbo.Customer(FName,LName,Age,DOB,Salary)
Values('Aamir','Shahzad',36,'1980-01-01',5000.50),
('Raza','M',33,'1983-03-03',4900.34),
('John','Smith',26,'1990-04-05',5000.50)


3)  Use Select with Insert to insert Records

We can use Insert with Select query to insert the result set returned by select query. 

Insert into dbo.Customer(FName,LName,Age,DOB,Salary)
Select 'Aamir' as FName,'Shahzad' as LName,36 as Age,'1980-01-01' as DOB,5000.50 as Salary
union all
Select 'Raza','M',33,'1983-03-03',4900.34
Union all
Select 'John','Smith',26,'1990-04-05',5000.50



4) Use Insert without provide Column Names

As you have seen in above examples, I have used column list with Insert, you don't have to use that if you know the order of columns and values you are using are also in order. I always use the column list in insert and select to make sure I am inserting correct data to table in correct columns.

--Insert without provide Column Names
Insert into dbo.Customer
Select 'Aamir' as FName,'Shahzad' as LName,36 as Age,'1980-01-01' as DOB,5000.50 as Salary
union all
Select 'Raza','M',33,'1983-03-03',4900.34
Union all
Select 'John','Smith',26,'1990-04-05',5000.50

Also we can use the insert without Column Names with Values option

Insert into dbo.Customer
Values('Aamir','Shahzad',36,'1980-01-01',5000.50),
('Raza','M',33,'1983-03-03',4900.34),
('John','Smith',26,'1990-04-05',5000.50)

5) Insert data from Another Table to Destination table

As we have seen that the select query results can be inserted into table. In above examples we have used the static values with select, You can select the data from table,view and function etc. to insert into your table. Let's say if we want to insert data into dbo.Customer table from dbo.CustomerUS table. you can use below query.

--Insert into table from another table
Select into dbo.Customer(FName,LName,Age,DOB,Salary)
Select FName,LName,Age,DOB,Salary from dbo.CustomerUS


Video Demo : How to insert data into SQL Sever Table by using T-SQL statements


How to generate drop Unique Constraint scripts in SQL Server Database - SQL Server / TSQL Tutorial Part 99

Scenario:

You are working as SQL Server developer, you need to generate scripts to drop all Unique Constraints in SQL Server Database.

Solution:

The below syntax can be used to drop Unique Constraint on table.

Alter table [SchemaName].[TableName]
Drop Constraint [ConstraintName]

The below query can be used to generate drop Unique Constraints in SQL Server database.

SELECT 
Table_Schema,
Table_Name,
Constraint_Name,
'Alter table ['
+Table_Schema+'].['
+Table_Name+']'
+' Drop Constraint ['+Constraint_Name+']'
as DropUniqueConstraintQuery
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
WHERE CONSTRAINT_TYPE = 'UNIQUE'



Generate scripts to drop Unique Constraint in SQL Server Database


Video Demo : Generate Scripts for Drop Unique Constraints in SQL Server Database



How to get list of columns with Unique Constraints in SQL Server Database - SQL Server / TSQL Tutorial Part 98

Scenario: 

You are working as SQL Server developer, you are asked to provide a query that should return all the Unique Constraint with Columns, Table and Schema Name.

Solution:

We can use system objects to get list of unique constraints with columns, tables and schema name. Below query will return you all the unique constraints with columns from SQL Server database.


;WITH CTE_UQ
AS (
    SELECT t.Table_Schema AS TableSchema
        ,t.table_name AS TableName
        ,c.column_name AS ColumnName
        ,t.constraint_name AS UniqueConstraintName
        ,t.constraint_type AS ConstraintType
    FROM information_schema.table_constraints t
    LEFT JOIN information_schema.key_column_usage c 
    ON t.constraint_catalog = c.constraint_catalog
        AND t.constraint_schema = c.constraint_schema
        AND t.constraint_name = c.constraint_name
    WHERE t.constraint_type = 'UNIQUE'
    )
SELECT TableSchema
    ,TableName
    ,UniqueConstraintName
    ,ConstraintType
    ,stuff((
            SELECT ',' + ColumnName
            FROM CTE_UQ i
            WHERE i.TableSchema = o.TableSchema
                AND i.TableName = o.TableName
                AND i.UniqueConstraintName = o.UniqueConstraintName
            FOR XML path('')
            ), 1, 1, '') UniqueColumnList
FROM CTE_UQ o
GROUP BY TableSchema
    ,TableName
    ,UniqueConstraintName
    ,ConstraintType


I executed above query on database and got below results.
How to get Column List with Unique Constraints in SQL Server Database


Video Demo : How to get all unique Constraints in SQL Server Database with Column Names

How to create Unique Constraint on Column for already existing Table - SQL Server / TSQL Tutorial Part 97

Scenario:

You are working as SQL Server developer, you need to create Unique Constraint on already existing table called dbo.Customer on column SSN.

Solution:

Let's create the dbo.Customer table first by using below script.

USE [YourDatabaseName]
GO
CREATE TABLE [dbo].[Customer](
    [FirstName] [varchar](50) NULL,
    [LastName] [varchar](50) NULL,
    [SSN] VARCHAR(11)
) 



Create Unique Constraint on SSN Column by using below script.

Alter Table dbo.Customer
Add Constraint UQ_dbo_Customer_SSN Unique(SSN)


If you need to create Unique Constraint on multiple columns, you can use below syntax. I am creating Unique Constraint for FirstName and LastName.

Alter Table dbo.Customer
Add Constraint UQ_dbo_Customer_FName_LName Unique(FirstName,LastName)


Use below query to check if Unique Constraints are created successfully.

SELECT *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
WHERE CONSTRAINT_TYPE = 'UNIQUE'

How to create Unique Constraint on Column for existing SQL Server Table


Video Demo : How to create Unique Constraint on Column for already existing Table


How to create Unique Constraint on Multiple Columns in SQL Server - SQL Server / TSQL Tutorial Part 96

Scenario:

You are working as SQL Server Developer, you are asked to provide create scripts for dbo.Customer table with Unique Constraint on First Name and Last Name columns.

Solution:

As we know that the Unique Constraint in SQL Server is created on a column or columns to restrict the column/s to accept only unique values.

The below script can be used to create Unique Constraint on multiple columns in our case FirstName and LastName.

USE [YourDatabaseName]
GO
CREATE TABLE [dbo].[Customer](
    [FirstName] [varchar](50) NULL,
    [LastName] [varchar](50) NULL,
    [SSN] VARCHAR(11),
    Constraint UQ_Dbo_Customer_FName_LName Unique(FirstName,LastName)
) 

--Let's insert some sample data to test Unique Constraint 
Insert into dbo.Customer(FirstName,LastName,SSN)
Values(Null,Null,'000-00-0000'),
('Aamir',Null,'000-00-0000'),
(Null,'Aamir','000-00-0000')
,('Aamir','Shahzad','000-00-0001')
,('Raza','M','000-00-0002')


If you have noticed the insert statement, In First Record I have put both Null values for FirstName and Last Name. In 2nd record I used the value 'Aamir' for first name and Null for Last Name. In 3rd record vice versa of 2nd record. From here what we understand that the unique constraint is working on both column values. As long as the combined value from both columns is unique, it is going to let us insert even one of them is null.

If I would like to insert another record in which I have Null for last name, it will let me without any problem, as it is unique from existing values.

Insert into dbo.Customer(FirstName,LastName,SSN)
Values('Raza',Null,'000-00-0000')



Let's check the records in table by using select query.
How to add Unique Constraint on Multiple Columns in SQL Server Table
If we try to insert a duplicate records, we will get below error.

Insert into dbo.Customer(FirstName,LastName,SSN)
Values('Raza',Null,'000-00-0000')


Msg 2627, Level 14, State 1, Line 30
Violation of UNIQUE KEY constraint 'UQ_Dbo_Customer_FName_LName'. Cannot insert duplicate key in object 'dbo.Customer'. The duplicate key value is (Raza, ).
The statement has been terminated.



Video Demo : Create Unique Constraint on Multiple Columns in SQL Server


What is Unique Constraint in SQL Server - SQL Server / TSQL Tutorial Part 95

What is Unique Constraint in SQL Server:

Unique Constraint in SQL Server is created on a column or columns to restrict the column/s to accept only unique values.
Only single Null value is allowed in a column on which Unique Constraint is created.

Scenario:

Let's say that you are working as SQL Server developer for insurance company, you are asked to create dbo.Customer table that should have a column SSN and it should always accept Unique values.


Solution:

For the above scenario, we can use Unique Constraint on SSN Column. Below script can be used to create unique constraint on a column.

USE [YourDatabaseName]
GO
CREATE TABLE [dbo].[Customer](
    [FirstName] [varchar](50) NULL,
    [LastName] [varchar](50) NULL,
    [SSN] VARCHAR(11),
    Unique(SSN)
) 

--Insert sample records in table 
Insert into dbo.Customer(FirstName,LastName,SSN)
Values('Raza','M',Null)
,('Aamir','Shahzad','000-00-0001')
,('Aamir','Shahzad','000-00-0002')



Check the data in dbo.Customer table by using Select query
How to add Unique Constraint on a Column in SQL Server 

Let's use the system views to check if Unique Constraint is added and what name SQL Server has given to it.

SELECT *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'UNIQUE'

How to add Unique Constraint in SQL Server 

As we can see that the SQL Server has given "UQ__Customer__CA1E8E3C7E8AFCB1" name to Unique Constraint. If we want to implement some naming convention then we should had provided name by ourselves.

If we try to insert the value that already exists in Unique Constraint column, we will get below error.

Insert into dbo.Customer(FirstName,LastName,SSN)
Values('Raza','M',Null)


Msg 2627, Level 14, State 1, Line 11
Violation of UNIQUE KEY constraint 'UQ__Customer__CA1E8E3C7E8AFCB1'. Cannot insert duplicate key in object 'dbo.Customer'. The duplicate key value is ().
The statement has been terminated.

If you would like to create the Unique Constraint with some naming convention, we can use below syntax.

USE [YourDatabaseName]
GO
CREATE TABLE [dbo].[Customer](
    [FirstName] [varchar](50) NULL,
    [LastName] [varchar](50) NULL,
    [SSN] VARCHAR(11),
    Constraint UQ_Dbo_Customer_SSN Unique(SSN)
) 




Video Demo : What is Unique Constraint in SQL Server





How to generate Scripts to Add Default Constraints to Column in Multiple Tables in SQL Server Database - SQL Server / TSQL Tutorial Part 94

Scenario:

You are working as SQL Server Developer, your company has the database with tons of tables. Each Table has some audit columns such as CreatedOn, CreatedBy. No default Constraints were ever created on them. You want to create the Default Constraint on CreatedOn Column for all the table with value getdate(), also you want to create Default Constraint for CreatedBy=SUSER_SNAME().
You may have another column of type string and you would like to generate scripts for that too.

Solution:

The below script can be used to generate Default Constraint for given column in entire database for all the tables. The name for Default Constraint will be DF_SchemaName_TableName_ColumnName.

Let's generate Default Constraint scripts for CreatedOn column with value Getdate() by using below script.


Declare @ColumnName VARCHAR(128)
Declare @DefaultValue VARCHAR(100)
SET @ColumnName='CreatedON'
SET @DefaultValue='Getdate()'

Select Schema_Name(Schema_id) as SchemaName,
t.Name as TableName,
C.Name as ColumnName,
'Alter Table ['+Schema_Name(Schema_id)
+'].['+t.Name+']'
+' Add Constraint DF_'+Schema_Name(schema_id)
    +'_'+t.name
    +'_'+c.name+''
    +' default '+@DefaultValue+' for '
    + @ColumnName as CreateDefaultConstraint
from sys.tables t
inner join sys.columns c
on t.object_id=c.object_id
and t.is_ms_shipped=0
and c.name=@ColumnName


Generate Scripts to Add Default Constraint to Column in Database in SQL Server

You only have to make change to @ColumnName and @DefaultValue variables as per your requirements. Let's use the same script to add Default Constraint to CreatedBy Column with value=SUSER_SNAME().

Declare @ColumnName VARCHAR(128)
Declare @DefaultValue VARCHAR(100)
SET @ColumnName='CreatedBy'
SET @DefaultValue='SUSER_SNAME()'

Select Schema_Name(Schema_id) as SchemaName,
t.Name as TableName,
C.Name as ColumnName,
'Alter Table ['+Schema_Name(Schema_id)
+'].['+t.Name+']'
+' Add Constraint DF_'+Schema_Name(schema_id)
    +'_'+t.name
    +'_'+c.name+''
    +' default '+@DefaultValue+' for '
    + @ColumnName as CreateDefaultConstraint
from sys.tables t
inner join sys.columns c
on t.object_id=c.object_id
and t.is_ms_shipped=0
and c.name=@ColumnName

How to generate script to add  Default Constraint on column in SQL Server Database


Now let's consider that you want to set default value to some string value as we want to create Default Constraint for Region column='USA' , When set the value of @DefaultValue variable, add single quotes as shown below.

Declare @ColumnName VARCHAR(128)
Declare @DefaultValue VARCHAR(100)
SET @ColumnName='Region'
SET @DefaultValue='''USA'''

Select Schema_Name(Schema_id) as SchemaName,
t.Name as TableName,
C.Name as ColumnName,
'Alter Table ['+Schema_Name(Schema_id)
+'].['+t.Name+']'
+' Add Constraint DF_'+Schema_Name(schema_id)
    +'_'+t.name
    +'_'+c.name+''
    +' default '+@DefaultValue+' for '
    + @ColumnName as CreateDefaultConstraint
from sys.tables t
inner join sys.columns c
on t.object_id=c.object_id
and t.is_ms_shipped=0
and c.name=@ColumnName


How to generate scripts to add Default Constraint on Column in multiple Tables in SQL Server Database



Video Demo : Generate Scripts to add Default Constraint to Column in Multiple Tables in SQL Server Database