给数据库的每一个表添加相同的列,比如“创建时间”
如果每一个表都去修改,在上百个表的数据库操作,将是一件繁琐的工作。
更快捷的方法,利用Sql server的sp_msforeachtable枚举每一个表的信息,然后参数添加列的脚本,问题就很简单了。
运行T-sql
USE AdventureWorks;
EXEC sp_msforeachtable
' PRINT "ALTER TABLE ? ADD Date_Created DATETIME DEFAULT GETUTCDATE();" ' ;
EXEC sp_msforeachtable
' PRINT "ALTER TABLE ? ADD Date_Created DATETIME DEFAULT GETUTCDATE();" ' ;
执行后会print一段脚本:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
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();
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();