[SQL Server] adds the same column to each table in the database

Add the same columns to each table in the database, for example, "Creation Time"

If each table is modified, database operations on hundreds of tables are cumbersome.

In a more efficient way, we can use sp_msforeachtable of SQL Server to enumerate the information of each table, and then add the column script as the parameter. The problem is very simple.

 

Run T-SQL

Use adventureworks;
Exec sp_msforeachtable
' Print "alter table? Add date_created datetime default getutcdate ();" ' ;

 

After execution, a script is printed:

 

Code

Alter   Table   [ Production ] . [ Productproductphoto ]   Add Date_created Datetime   Default Getutcdate ();
Alter   Table   [ Sales ] . [ Storecontact ]   Add Date_created Datetime   Default Getutcdate ();
Alter   Table   [ Person ] . [ Address ]   Add Date_created Datetime   Default Getutcdate ();
Alter   Table   [ Production ] . [ Productreview ]   Add Date_created Datetime   Default Getutcdate ();
Alter   Table   [ Production ] . [ Transactionhistory ]   Add Date_created Datetime   Default Getutcdate ();
Alter   Table   [ Person ] . [ Addresstype ]   Add Date_created Datetime   Default Getutcdate ();
Alter   Table   [ Production ] . [ Productsubcategory ]   Add Date_created Datetime   Default Getutcdate ();
Alter   Table   [ DBO ] . [ Awbuildversion ]   Add Date_created Datetime   Default Getutcdate ();
Alter   Table   [ Production ] . [ Transactionhistoryarchive ]   Add Date_created Datetime   Default Getutcdate ();
Alter   Table   [ Purchasing ] . [ Productvendor ]   Add Date_created Datetime   Default Getutcdate ();
Alter   Table   [ Production ] . [ Billofmaterials ]   Add Date_created Datetime   Default Getutcdate ();
Alter   Table   [ Production ] . [ Unitmeasure ]   Add Date_created Datetime   Default Getutcdate ();
Alter   Table   [ Purchasing ] . [ Vendor ]   Add Date_created Datetime   Default Getutcdate ();
Alter   Table   [ Purchasing ] . [ Purchaseorderdetail ]   Add Date_created Datetime   Default Getutcdate ();