SQL Server: Tips and Tricks - 2 (2017)
How to Generate Add Column Statement for all the tables in a Database in SQL Server
You are working in Auto Insurance Company as SQL Server Developer. Your company has a database in which they have more than 300 Tables. In initial design, they forgot to add Audit columns such as CreatedBy and CreatedOn. They want you to generate Add Column CreatedBy and CreatedOn for all the Tables in a Database, How would you do that?
There are many ways to generate the SQL Statements for above requirement, we are going to keep it simple and quick. We will use Select Query to generate Add Column Statements for all the tables.
Let's generate the scripts in a way so we have to make very small changes every time and we can use anytime to add new column. I have declared two variables, @ColumnName and @ColumnDataType. Once we will provide the values for variables. The Select query is going to check all the tables and if column does not exist for the table, it will generate Add Column statement.
--Declare Variables for Column Name and Data Type
Declare @ColumnName VARCHAR(100)
Declare @ColumnDataType VARCHAR(50)
--Set the Values for Variables
--Run the Query and copy results and paste in new window to run.
Select 'ALTER Table ['+Table_Schema+'].['+Table_Name+'] '
+'Add '+@ColumnName+' '+@ColumnDataType AS AddColumnQuery
from Information_Schema.Tables T
where T.Table_Type='BASE TABLE'
And Not exists (
Select 1 from INFORMATION_SCHEMA.COLUMNS C
WHERE C.TABLE_Name=T.Table_Name and Column_Name=@ColumnName)
Run above query and copy the results to new Query Windows, Select the Database on which you would like to run and execute.