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