[Sql Server] 给数据库的每一个表添加相同的列
给数据库的每一个表添加相同的列，比如“创建时间”
如果每一个表都去修改，在上百个表的数据库操作，将是一件繁琐的工作。
更快捷的方法，利用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博客 ” ，链接：https://blog.itpub.net/16436858/viewspace-631516/ ，如需转载，请注明出处，否则将追究法律责任。
下一篇：
SQL语句 实现自动编号功能
请登录后发表评论
登录
全部评论
