实例解析：如何在数据库中更改每个表格

新客网 XKER.COM 2007-08-28 来源： limeinan 收藏本文

所有程序开发人员都会时不时地出现错误。有时候，错误的发生是因为我们没有建立明显但却没有指明的请求。

这里有一个例子：你的数据库运行良好，但数据接口出现很多错误，而且数据更新时出现新的请求：给每个表格添加两列（LastUpdated和UpdatedBy）。这里有数百个表格，因此手工操作不可能完成这个任务。

很显然，这是一个可重用代码块，因此你一定会希望只编写一次，就可以确保它能够在每一数据库中使用。(可能你需要给每个新的数据库稍做改进。例如，改变列表名。但主要的思想是，你希望让一个程序在一个数据库中的所有表格中运行，并且可以添加一个或更多列表。)

获得用户表格中的列表相当容易：

SELECT Name FROM sysobjects WHERE Type = 'U' ORDER BY Name

结果集可以作为一个用户自定义函数而更加容易处理，这一自定义函数返回一个表格：

CREATE FUNCTION dbo.UserTables_fnt
()
RETURNS TABLE
AS
RETURN
  (SELECT TOP 100 PERCENT name
  FROM dbo.sysobjects
  WHERE type = 'U')
  ORDER BY name
  )

假设你想给数据库中的每个表格添加

假设你想给数据库中的每个表格添加一个名为LastUpdated (TimeStamp类型)的列表。为了在任一给定的表格中添加一个列，例如Customers，你的命令可以如下所示：

ALTER TABLE MyDB.dbo.Customers ADD LastUpdated TimeStamp NULL

现在，你建立一个请求(查看, 存储过程, UDF)，这一请求构成了你所需要完成任务的语句：

SELECT
'ALTER TABLE NorthwindTest.dbo.
[' + name + '] ADD LastUpdated TimeStamp NULL'
AS CommandText
FROM dbo.UserTables_fnt() 
假如你拷贝了名为Northwind的NorthwindTest范例数据库，
并运行这一代码，其结果如下所示：
ALTER TABLE NorthwindTest.dbo.
[Categories] ADD LastUpdated TimeStamp NULL
ALTER TABLE NorthwindTest.dbo.
[CustomerCustomerDemo] ADD LastUpdated TimeStamp
NULL
ALTER TABLE NorthwindTest.dbo.
[CustomerDemographics] ADD LastUpdated TimeStamp
NULL
ALTER TABLE NorthwindTest.dbo.
[Customers] ADD LastUpdated TimeStamp NULL
ALTER TABLE NorthwindTest.dbo.
[dtproperties] ADD LastUpdated TimeStamp NULL
ALTER TABLE NorthwindTest.dbo.
[Employees] ADD LastUpdated TimeStamp NULL
ALTER TABLE NorthwindTest.dbo.
[EmployeeTerritories] ADD LastUpdated TimeStamp
NULL
ALTER TABLE NorthwindTest.dbo.
[Order Details] ADD LastUpdated TimeStamp NULL
ALTER TABLE NorthwindTest.dbo.
[Orders] ADD LastUpdated TimeStamp NULL
ALTER TABLE NorthwindTest.dbo.
[Products] ADD LastUpdated TimeStamp NULL
ALTER TABLE NorthwindTest.dbo.
[Region] ADD LastUpdated TimeStamp NULL
ALTER TABLE NorthwindTest.dbo.
[Shippers] ADD LastUpdated TimeStamp NULL
ALTER TABLE NorthwindTest.dbo.
[Suppliers] ADD LastUpdated TimeStamp NULL
ALTER TABLE NorthwindTest.dbo.
[Territories] ADD LastUpdated TimeStamp NULL

我使用方括号以圈住表格名，因为它们能够保护有潜在错误的表格名：Order Details。对于没有空格的情况，剖析器不会考虑到方括号，然而对于有空格情况，生成的SQL代码将会产生错误。

你可以用各种方法来处理这种结果集，这些方法包括将其粘贴到Query Analyzer并运行，并且将它转化成储存程序，或者转化成一个可修改的视图。对于“一次性”特性，我喜欢使用第一个方法。

我喜欢用已经编写好的代码来编写程序，因为那样我就不需要重新编写——而且它不会有任何拼写错误。你可以扩展这一思想以执行任何DML操作。

如果你想试用这一技巧，我强烈推荐你先建立一个SELECT请求，这一请求可以构建所需要的DML。因此，在运行之前你可以检查，并校对它的语法。

上一篇：带你轻松接触ICX数据库软件的十个特性下一篇：实例解析：数据库与数据仓库的内在区别

【收藏】【评论】【推荐】【投稿】【打印】【关闭】