利用游标操作多个数据库

来源：互联网发布：python 数据库编程编辑：程序博客网时间：2024/06/18 13:01

CREATE DATABASE USER01
ON ( NAME = Sales_dat,
   FILENAME = 'c:/program files/microsoft sql server/mssql/data/USERO1dat.mdf',
   SIZE = 10,
   MAXSIZE = 50,
   FILEGROWTH = 5 )
LOG ON
( NAME = 'Sales_log',
   FILENAME = 'c:/program files/microsoft sql server/mssql/data/USERO1Log.ldf',
   SIZE = 5MB,
   MAXSIZE = 25MB,
   FILEGROWTH = 5MB )

GO

USE USER01
GO

CREATE TABLE z_qx(MM VARCHAR(40))
GO

USE MASTER
GO

DECLARE @SQL VARCHAR(8000),@DATABASE VARCHAR(100)

DECLARE CR CURSOR FOR
SELECT NAME FROM SYSDATABASES WHERE NAME LIKE 'USER%'

OPEN CR

FETCH NEXT FROM CR INTO @DATABASE

WHILE @@FETCH_STATUS=0
BEGIN

    SET @SQL='ALTER TABLE '+@DATABASE +'..z_qx ALTER COLUMN MM VARCHAR(254)'
    EXEC(@SQL)

    SET @SQL='
      IF NOT EXISTS(SELECT 1 FROM '+@DATABASE +'..SYSCOLUMNS WHERE ID=OBJECT_ID(''z_qx'') AND NAME=''q90'')
          ALTER TABLE '+@DATABASE +'..z_qx ADD q90 INT NULL'
    EXEC(@SQL)

    --...

    SET @SQL='
      IF NOT EXISTS(SELECT 1 FROM '+@DATABASE +'..SYSCOLUMNS WHERE ID=OBJECT_ID(''z_qx'') AND NAME=''authenname'')
          ALTER TABLE '+@DATABASE +'..z_qx ADD authenname VARCHAR(64) NULL'
    EXEC(@SQL)

    FETCH NEXT FROM CR INTO @DATABASE
END

CLOSE CR
DEALLOCATE CR
GO

USE USER01
GO

SELECT * FROM z_qx
GO

DROP TABLE z_qx
GO

USE MASTER
GO

DROP DATABASE USER01
GO