给数据库的每一个表添加相同的列,比如“创建时间”
如果每一个表都去修改,在上百个表的数据库操作,将是一件繁琐的工作。
更快捷的方法,利用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一段脚本:
代码
<!--Code highlighting produced by Actipro CodeHighlighter (freeware)http://www.CodeHighlighter.com/-->
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();
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16436858/viewspace-631516/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/16436858/viewspace-631516/