Database Experts:Add New Column in All Tables that don' t have column with specific data types

Sometimes we need some columns that usually used by for row insert and update information.

For E.g.

CreatedBy,ModifiedBy etc..

Just Execute below Stored Procedure to Add column to all tables that don't have this column.



/******************************************************************************/
/* Copyright: 2012 Database Experts
*
* Name :[Add_New_Column]
* Version : $Revision: 1
* Created By : $Author: Database Experts
* Modified By : $Author:
* Script : This Procedure will create new column with specific data type.
* Input: 1. @Column_Name,@Data_Type
*
* Output:
* Version Info:
* No.                 Author                            Date                                                           Comments
* --- ------ ---- --------
*/
/*****************************************************************************/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Procedure [dbo].[Add_New_Column]
    @Column_Name varchar(50),
    @Data_Type varchar(50)
   
as

Begin

Select Distinct object_name(c.OBJECT_ID) Table_Name into #ExistedIn
from sys.columns c
Where name in (@Column_Name)
and object_name(c.OBJECT_ID) not like 'sys%'

Declare  @SQL varchar(1000)
Declare  @Table_Name varchar(1000)
Declare  @NewColumn varchar(1000)

--Drop table #temp

Select name into #NotExist from sys.tables where name not in (select Table_Name From #ExistedIn )

DECLARE db_cursor CURSOR FOR SELECT name FROM #NotExist

OPEN db_cursor 
FETCH NEXT FROM db_cursor INTO @Table_Name 

WHILE @@FETCH_STATUS = 0 

BEGIN 

SET @SQL='ALTER TABLE '+ @Table_Name+' ADD '+@Column_Name+' '+@Data_Type

Print @SQL

Exec(@SQL)

Print 'Done For'+@Table_Name+':'+@Column_Name

FETCH NEXT FROM db_cursor INTO @Table_Name 

End


end