将列添加到架构中的多个表

发布于 2024-09-10 09:24:56 字数 347 浏览 3 评论 0原文

我需要更改模式中的多个表，对于所有以某些内容为前缀的表，例如：

ALTER TABLE "SCHEMA"."TABLE1"
ADD ( "COLUMN1"   CHARACTER(4)  NOT NULL    DEFAULT 'DATA',
    "COLUMN2"   VARCHAR(16)     NOT NULL    DEFAULT 'MORE_DATA',
);

我需要它来迭代多个表，例如 SCHEMA.table1、SCHEMA.table2...等等。该架构中有大约 800 个表。

我想知道是否可以使用某种通配符？我正在考虑从系统表创建游标，但确信一定有一种更简单的方法。

原文

I need to alter multiple tables in a schema, for all tables prefixed with something, for example:

ALTER TABLE "SCHEMA"."TABLE1"
ADD ( "COLUMN1"   CHARACTER(4)  NOT NULL    DEFAULT 'DATA',
    "COLUMN2"   VARCHAR(16)     NOT NULL    DEFAULT 'MORE_DATA',
);

I need this to iterate over multiple tables, like SCHEMA.table1, SCHEMA.table2... and so on. There is around 800 tables in the schema.

I was wondering if there is some kind of wildcard that I could use? I was looking at creating a cursor from the System tables, but was sure there must be an easier way.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

池木 2024-09-17 09:24:56

在这些情况下，如果 DBMS 没有提供简单的方法来执行此操作，我倾向于做的就是简单地编写一个脚本来为我执行此操作。类似于：

db2 "select tbname from sysibm.systables where schema = 'SCHEMA'" >tblist
# Edit tblist here to remove headers and such.
for t in $(cat tblist) ; do
    db2 "alter table SCHEMA.${t} add ..."
done

这是 bash 类型格式，您需要适应您使用的任何脚本工具。

What I tend to do in those cases, if the DBMS doesn't provide an easy way to do it, is to simply write a script that will do it for me. Something like:

db2 "select tbname from sysibm.systables where schema = 'SCHEMA'" >tblist
# Edit tblist here to remove headers and such.
for t in $(cat tblist) ; do
    db2 "alter table SCHEMA.${t} add ..."
done

That's bash type format, you'll need to adapt for whatever scripting tool you use.

回复收藏 0 原文

子栖 2024-09-17 09:24:56

感谢大家的输入，

在使用 CURSOR 和 while 循环创建了（我认为）奇特的过程来迭代表之后，我决定，因为这是一次从 select 语句为每个表构建 ALTER 查询的过程：

SELECT DISTINCT 'ALTER TABLE '
          || 'CTP0610'
          || '.'
          || name
          || ' ADD COLUMN SOURCE_SYSTEM_CODE CHAR(4) NOT NULL DEFAULT ''CCR'' '
          || ' ADD COLUMN RECORD_TYPE VARCHAR(16) NOT NULL DEFAULT ''INSERT'' '
          || ' ADD COLUMN COMMIT_TIMESTAMP TIMESTAMP NOT NULL DEFAULT '
          || ' ADD COLUMN EXTRACT_TIMESTAMP TIMESTAMP NOT NULL DEFAULT; '
    FROM sysibm.systables
    WHERE (NAME LIKE 'CCTL_%')
    OR (NAME LIKE 'CCX_%')
    OR (NAME LIKE 'CC_%');

我将生成的查询粘贴到查询窗口中并运行它，结果大约有 1500 个表。我想有时最不优雅的解决方案就足够了:-)

Thanks for everyone's input,

After creating what was (I thought) fancy procedure using a CURSOR and while loop to iterate through the tables, I decided since it is a once off to build an ALTER query for each of the tables from a select statement thus:

SELECT DISTINCT 'ALTER TABLE '
          || 'CTP0610'
          || '.'
          || name
          || ' ADD COLUMN SOURCE_SYSTEM_CODE CHAR(4) NOT NULL DEFAULT ''CCR'' '
          || ' ADD COLUMN RECORD_TYPE VARCHAR(16) NOT NULL DEFAULT ''INSERT'' '
          || ' ADD COLUMN COMMIT_TIMESTAMP TIMESTAMP NOT NULL DEFAULT '
          || ' ADD COLUMN EXTRACT_TIMESTAMP TIMESTAMP NOT NULL DEFAULT; '
    FROM sysibm.systables
    WHERE (NAME LIKE 'CCTL_%')
    OR (NAME LIKE 'CCX_%')
    OR (NAME LIKE 'CC_%');

I pasted the resultant queries into a query window and ran it, it turned out to be around 1500 tables. I guess sometimes the least elegant solution is good enough :-)

回复收藏 0 原文

恰似旧人归 2024-09-17 09:24:56

~~使用未记录的sp_MSforeachTable。您会在 Google 上找到很多相关帮助。~~

OP 做到了原来没有指定DB2。因此我的回答是多余的。

回复收藏 0 原文

破晓 2024-09-17 09:24:56

下面将向特定数据库（例如，MyDatabase）内具有特定架构（例如，MySchema）的所有表添加一列

declare @tablename nvarchar(max)
declare @sqlstring nvarchar(max)
declare cur cursor for 
select 
    [TABLE_SCHEMA] + '.' + [TABLE_NAME] from MyDatabase.INFORMATION_SCHEMA.Tables 
where 
    [TABLE_SCHEMA] = 'MySchema'

open cur
    fetch next from cur into @tablename
    while @@fetch_status=0
    begin
        set @sqlstring = 'ALTER TABLE ' + @tablename + ' ADD MyColumn DateTime NOT NULL'

        exec sp_executesql @sqlstring

        fetch next from cur into @tablename
    end

close cur
deallocate cur

The following will add a column to all tables with a specific schema (e.g., MySchema) within a specific database (e.g., MyDatabase)

declare @tablename nvarchar(max)
declare @sqlstring nvarchar(max)
declare cur cursor for 
select 
    [TABLE_SCHEMA] + '.' + [TABLE_NAME] from MyDatabase.INFORMATION_SCHEMA.Tables 
where 
    [TABLE_SCHEMA] = 'MySchema'

open cur
    fetch next from cur into @tablename
    while @@fetch_status=0
    begin
        set @sqlstring = 'ALTER TABLE ' + @tablename + ' ADD MyColumn DateTime NOT NULL'

        exec sp_executesql @sqlstring

        fetch next from cur into @tablename
    end

close cur
deallocate cur

回复收藏 0 原文

~没有更多了~