最近很是郁闷,总公司要求把所有分公司的DB统一管理,以前都是各顾各的,什么添加字段修改数据类型都各做各的。现在要求统一很是麻烦,如果其中一个DB的某字段修改那么其他所有DB里面只要有这个表的都要改。
唉!以前很轻松的只要按需求点点鼠标就改一个DB就好的事情,现在要点60多个数据库去完成操作,而且还有很多DB不在一个SERVER上面,想我高升一向都是很懒的人,这种事情肯定是不愿意做的,可以这种事情低级DBA不做你要谁去做啊,于是一恨心放弃了一个宝贵的双休写了一个统一修改列属性的存储过程,使用代码更改列的数据类型,不知道算不算是远程修改了。以下示例将 AdventureWorks 数据的 Employee 表 Title 列的长度改成55。
ALTER COLUMN Title nvarchar ( 55 )
ALTER TABLE 通过更改、添加或删除列和约束,重新分配分区,或者启用或禁用约束和触发器,从而修改表的定义。修改一个表的定义这个语法每个初学数据库的人都是知道的,不过很多情况下我们并不能直接修改,比如以下几种情况:
- 用于索引的列。
- 用于 CHECK、FOREIGN KEY、UNIQUE 或 PRIMARY KEY 约束的列。
- 与默认值(由 DEFAULT 关键字定义)相关联的列,或绑定到默认对象的列。
- 绑定到规则的列。
在实际应用中,如果我们要修改的某一列上面建了索引或者建有约束的话,那个这条语句很可能是要报错的,当然这个和数据类型也有关系,具体的原理是什么我也不是很清楚。可以试一下将 Title 列加上一个索引,然后修改一下试试看。
CREATE NONCLUSTERED INDEX IX_Employee ON HumanResources.Employee
(
Title
) WITH ( STATISTICS_NORECOMPUTE = OFF , IGNORE_DUP_KEY = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [ PRIMARY ]
GO
ALTER COLUMN Title nvarchar (60 )
现在执行这句话不出意外应该抱错了,如果上面有约束也是一样的会报错,不过这和数据类型有关,具体有哪些情况下会出现我也需要有人帮我解惑。
写点基本语法,多温习一下基础知识还是必要的,添加和删除默认约束,因为有时候用代码去修改列的时候有默认约束也是不能修改的,所以要删了重新建。CHECK 约束的建法差不多的不想写了。
DF_Employee_Title DEFAULT '' FOR Title
ALTER TABLE HumanResources.Employee
DROP CONSTRAINT DF_Employee_Title
由于要修改的列可能已经加上了索引或者约束,这就为我现在使用代码修改列的属性带来了一定的麻烦,所以我才花两天时间写这个存储过程,其实关键代码就只有几句。我建到了msdb数据库下面,还加了密。
GO
-- 建立人: 高升
-- 建立日期:2007/06/16
-- 修改日期:
-- 功能目的:主要就是修改列的属性,不过是可以执行以后修改N多DB的,算不算远程修改列?
-- 注意: 就我可以用,因为用的临时表太多了,汗!
CREATE PROCEDURE dbo.ModifyColumn
@dbName sysname = '' , -- 要修改的数据库名,不写就是所以的数据库
@tableName sysname = '' , -- 要修改的表
@columnName sysname = '' , -- 要修改的列
@type varchar ( 2 ), -- M修改A新增
@dataType nvarchar ( 128 ) = '' , -- 数据类型
@constraint varchar ( 100 ) = '' , -- 约束名,新增的时候写
@is_exec bit = 0 , -- 是打印是生成的代码还是直接执行
@custom varchar ( max ) = ''
WITH ENCRYPTION
AS
IF ( SUSER_SNAME () != ' GaoS ' ) -- 我的登陆名,sa都不能执行我的东西
RAISERROR ( ' The user does not have permission to perform this action. ' , 16 , 1 )
DECLARE @exec varchar ( max ) -- 存放执行的语句
DECLARE @name sysname -- 当前要执行的数据库名
DECLARE @server_id varchar ( 4 ) -- 那个Server
DECLARE @exec1 varchar ( 100 ) -- 临时执行的语句
DECLARE @exec2 varchar ( 100 ) --
DECLARE @exec3 varchar ( 300 ) --
DECLARE @constraintName varchar ( 100 ) -- 默认约束的约束名
DECLARE @definition nvarchar ( 50 ) -- 默认值
DECLARE @sno smallInt -- 用于循环
IF ( @is_exec <> 1 ) SET @is_exec = 0 -- 该值只能是0或1
IF exists ( SELECT * FROM tempdb.sys.objects WHERE name = ' ##myDB ' )
BEGIN
PRINT ( ' There is already an object named '' ModifyColumn '' in the database. ' )
RETURN ;
END
IF ( @dbName = '' ) -- 查询所有要执行的DB,除去系统和临时DB
BEGIN
INSERT ##myDB select name, 1 FROM master.sys.databases
WHERE database_id > 4 and name not like ' %temp% ' ORDER BY name
INSERT ##myDB select name, 2 FROM SHDB2.master.sys.databases
WHERE database_id > 4 and name not like ' %temp% ' ORDER BY name
INSERT ##myDB select name, 3 FROM TWDB.master.sys.databases
WHERE database_id > 4 and name not like ' %temp% ' ORDER BY name
INSERT ##myDB select name, 4 FROM GZDB.master.sys.databases
WHERE database_id > 4 and name not like ' %temp% ' ORDER BY name
END
ELSE -- 如果指定了要修改的数据库名
BEGIN
DECLARE @getDBName varchar ( 500 )
SET @getDBName = ' INSERT ##myDB select name,1 FROM master.sys.databases
WHERE name in ( ''' + @dbName + ''' ) ORDER BY name '
EXEC ( @getDBName )
SET @getDBName = ' INSERT ##myDB select name,2 FROM SHDB2.master.sys.databases
WHERE name in ( ''' + @dbName + ''' ) ORDER BY name '
EXEC ( @getDBName )
SET @getDBName = ' INSERT ##myDB select name,3 FROM TWDB.master.sys.databases
WHERE name in ( ''' + @dbName + ''' ) ORDER BY name '
EXEC ( @getDBName )
SET @getDBName = ' INSERT ##myDB select name,4 FROM GZDB.master.sys.databases
WHERE name in ( ''' + @dbName + ''' ) ORDER BY name '
EXEC ( @getDBName )
END
SET @sno = 1
WHILE ( @sno <= ( SELECT COUNT (sno) FROM ##MyDB))
BEGIN -- 终于可以开始了,一个DB一个DB的循环吧,不喜欢用游标
SELECT @name = name, @server_id = server_id FROM ##MyDB WHERE sno = @sno
IF ( @server_id = 2 ) -- 如果是本地的服务器2
BEGIN
EXEC SHDB2.msdb.dbo.ModifyColumn --其他Server上的这个存储过程比这个简单多了
@dbName = @name ,
@type = @type ,
@tableName = @tableName ,
@columnName = @columnName ,
@dataType = @dataType ,
@constraint = @constraint ,
@is_exec = @is_exec ,
@custom = @custom
SET @exec = ''
END
ELSE IF ( @server_id = 3 ) -- 如果是台湾的服务器
BEGIN
EXEC TWDB.msdb.dbo.ModifyColumn
@dbName = @name ,
@type = @type ,
@tableName = @tableName ,
@columnName = @columnName ,
@dataType = @dataType ,
@constraint = @constraint ,
@is_exec = @is_exec ,
@custom = @custom
SET @exec = ''
END
ELSE IF ( @server_id = 4 ) -- 如果是广州的服务器
BEGIN
EXEC GZDB.msdb.dbo.ModifyColumn
@dbName = @name ,
@type = @type ,
@tableName = @tableName ,
@columnName = @columnName ,
@dataType = @dataType ,
@constraint = @constraint ,
@is_exec = @is_exec ,
@custom = @custom
SET @exec = ''
END
ELSE IF ( @type = ' M ' ) -- 修改某个字段,这里才是重点
BEGIN
DECLARE @exec4 varchar ( 500 ) -- 删除索引或键
DECLARE @exec5 varchar ( 500 ) -- 添加索引或键
DECLARE @exec6 varchar ( 500 ) -- 临时用用为了得到几个参数
DECLARE @i_name sysname -- 索引名
DECLARE @is_key bit -- 是否主键
DECLARE @i_no tinyInt -- 索引的序号
DECLARE @c_name varchar ( 100 ) -- 索引所在的列名
DECLARE @i_type varchar ( 60 ) -- 是否聚集
DECLARE @is_unique varchar ( 6 ) -- 是否唯一
SET @exec4 = ''
SET @exec5 = ''
SET @i_name = ''
IF EXISTS ( SELECT * FROM tempdb.sys.objects WHERE name = ' ##Temp_dafalit ' )
DROP TABLE ##Temp_dafalit -- 放默认值的
IF EXISTS ( SELECT * FROM tempdb.sys.objects WHERE name = ' ##temp_indexName ' )
DROP TABLE ##temp_indexName -- 放索引名的
SET @constraintName = '' -- 约束名
SET @definition = '' -- 默认值
SET @exec3 = ' use ' + @name + ' ; ' + char ( 13 ) +
' SELECT name,definition INTO ##Temp_dafalit FROM sys.default_constraints
WHERE object_id = (SELECT default_object_id FROM sys.columns WHERE object_id = OBJECT_ID( ''' + @tableName + ''' ) AND name = ''' + @columnName + ''' ) '
EXEC ( @exec3 )
SELECT @constraintName = name, @definition = definition FROM ##Temp_dafalit
IF ( @constraintName != '' ) -- 如果该列有默认约束。修改前先删除,改好后还原。
BEGIN
SET @exec1 = ' ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @constraintName + char ( 13 )
SET @exec2 = ' ALTER TABLE ' + @tableName + ' ADD CONSTRAINT ' + @constraintName + ' DEFAULT ' + @definition + ' FOR ' + @columnName + char ( 13 )
END
ELSE
BEGIN
SET @exec1 = ''
SET @exec2 = ''
END
SET @exec6 = ' use ' + @name + ' ; ' + char ( 13 ) +
' SELECT IDENTITY(tinyInt,1,1)i_no,c.name,c.is_primary_key INTO ##temp_indexName
FROM sys.columns a inner join sys.index_columns b
ON a.object_id = b.object_id AND a.column_id = b.column_id inner join sys.indexes c
ON b.object_id = c.object_id AND b.index_id = c.index_id
WHERE a.object_id = OBJECT_ID( ''' + @tableName + ''' ) AND a.name = ''' + @columnName + ''''
EXEC ( @exec6 )
SET @i_no = 1 -- 此处的循环很无奈,因为有的列上可能有两个索引,也不知道设计有没问题
WHILE ( @i_no <= ( SELECT COUNT (i_no) FROM ##temp_indexName))
BEGIN
SELECT @i_name = name , @is_key = is_primary_key FROM ##temp_indexName WHERE i_no = @i_no
IF ( @i_name != '' ) -- 如果该列有索引。修改前先删除,改好后还原。
BEGIN
IF ( @is_key = 0 ) -- 判断是否主键,主键的索引和非主键索引删除方法不一样
SET @exec4 = @exec4 + ' DROP INDEX [ ' + @i_name + ' ] ON [ ' + @tableName + ' ] WITH ( ONLINE = OFF ) ' + char ( 13 )
ELSE
SET @exec4 = @exec4 + ' ALTER TABLE [ ' + @tableName + ' ] DROP CONSTRAINT [ ' + @i_name + ' ] ' + char ( 13 )
IF EXISTS ( SELECT * FROM tempdb.sys.objects WHERE name = ' ##temp_indexinfo ' )
DROP TABLE ##temp_indexinfo -- 存放临时的索引信息
SET @exec6 = ' use ' + @name + ' ; ' + char ( 13 ) +
' SELECT * INTO ##temp_indexinfo FROM
(SELECT a.name,b.is_descending_key,c.type_desc, c.is_unique,is_primary_key
FROM sys.columns a inner join sys.index_columns b
ON a.object_id = b.object_id AND a.column_id = b.column_id inner join sys.indexes c
ON b.object_id = c.object_id AND b.index_id = c.index_id
WHERE a.object_id = OBJECT_ID( ''' + @tableName + ''' ) AND c.name = ''' + @i_name + ''' )a '
EXEC ( @exec6 )
SET @c_name = ''
SELECT @i_type = type_desc, @is_unique = ( CASE is_unique WHEN 1 THEN ' UNIQUE ' ELSE '' END ), @is_key = is_primary_key FROM ##temp_indexinfo
SELECT @c_name = @c_name + ' ,[ ' + name + ( CASE is_descending_key WHEN 1 THEN ' ] DESC ' ELSE ' ] ASC ' END ) FROM ##temp_indexinfo
IF ( @is_key = 0 ) -- 主键的索引和非主键索引创建方法不一样
SET @exec5 = @exec5 + ' CREATE ' + @is_unique + ' ' + @i_type + ' INDEX [ ' + @i_name + ' ] ON [ ' + @tableName + ' ] ( ' + SUBSTRING ( @c_name , 2 , len ( @c_name )) + ' ) ' + char ( 13 )
ELSE
SET @exec5 = @exec5 + ' ALTER TABLE [ ' + @tableName + ' ] ADD CONSTRAINT [ ' + @i_name + ' ] PRIMARY KEY ' + @i_type + ' ( ' + SUBSTRING ( @c_name , 2 , len ( @c_name )) + ' ) ' + + char ( 13 )
END
ELSE
BEGIN
SET @exec4 = ''
SET @exec5 = ''
END
SET @i_no = @i_no + 1 -- 下一个索引
END -- while
SET @exec =
' IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID( ''' + @tableName + ''' ) AND type = '' U '' )BEGIN ' + char ( 13 ) +
' IF ((SELECT is_nullable FROM sys.columns WHERE object_id = OBJECT_ID( ''' + @tableName + ''' ) AND name = ''' + @columnName + ''' ) = 0) ' + char ( 13 ) +
' ALTER TABLE ' + @tableName + ' ALTER COLUMN ' + @columnName + ' ' + @dataType + ' not null ' + @constraint + char ( 13 ) +
' ELSE ALTER TABLE ' + @tableName + ' ALTER COLUMN ' + @columnName + ' ' + @dataType + ' ' + @constraint + char ( 13 ) +
' PRINT ''' + @name + ' 的 ' + @tableName + ' 表修改 ' + @columnName + ' 列成功 '' END ' + char ( 13 ) +
' ELSE PRINT ''' + @name + ' 中没有 ' + @tableName + ' 表 ''' + char ( 13 )
SET @exec = ' use ' + @name + ' ; ' + char ( 13 ) + @exec1 + @exec4 + @exec + @exec2 + @exec5
IF EXISTS ( SELECT * FROM tempdb.sys.objects WHERE name = ' ##Temp_dafalit ' )
DROP TABLE ##Temp_dafalit
IF EXISTS ( SELECT * FROM tempdb.sys.objects WHERE name = ' ##temp_indexName ' )
DROP TABLE ##temp_indexName
IF EXISTS ( SELECT * FROM tempdb.sys.objects WHERE name = ' ##temp_indexinfo ' )
DROP TABLE ##temp_indexinfo
-- 居然用了3个临时表,还好这个不需要考虑效率问题
END
ELSE IF ( @type = ' A ' ) -- 增加某个字段
BEGIN
SET @exec = ' use ' + @name + ' ; ' + char ( 13 ) +
' IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID( ''' + @tableName + ''' ) AND type = '' U '' ) ' + char ( 13 ) +
' BEGIN ALTER TABLE ' + @tableName + ' ADD ' + @columnName + ' ' + @dataType + ' ' + @constraint + char ( 13 ) +
' PRINT ''' + @name + ' 的 ' + @tableName + ' 表增加 ' + @columnName + ' 列成功 '' END ' + char ( 13 ) +
' ELSE PRINT ''' + @name + ' 中没有 ' + @tableName + ' 表 ''' + char ( 13 )
END
ELSE IF ( @type = ' D ' ) -- 为列添加默认值
BEGIN
SET @exec =
' IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID( ''' + @tableName + ''' ) AND type = '' U '' )BEGIN ' + char ( 13 ) +
' ALTER TABLE ' + @tableName + ' ADD CONSTRAINT DF_ ' + @tableName + ' _ ' + @columnName + ' DEFAULT ( ' + @constraint + ' ) FOR ' + @columnName + char ( 13 ) +
' PRINT ''' + @name + ' 的 ' + @tableName + ' 表为 ' + @columnName + ' 列添加默认值成功 '' END ' + char ( 13 ) +
' ELSE PRINT ''' + @name + ' 中没有 ' + @tableName + ' 表 ''' + char ( 13 )
IF exists ( SELECT * FROM tempdb.sys.objects WHERE name = ' ##Temp_dafalit ' )
DROP TABLE ##Temp_dafalit
SET @constraintName = ''
SET @definition = ''
SET @exec3 = ' use ' + @name + ' ; ' + char ( 13 ) +
' SELECT name,definition INTO ##Temp_dafalit FROM sys.default_constraints
WHERE object_id = (SELECT default_object_id FROM sys.columns WHERE object_id = OBJECT_ID( ''' + @tableName + ''' ) AND name = ''' + @columnName + ''' ) '
EXEC ( @exec3 )
SELECT @constraintName = name, @definition = definition FROM ##Temp_dafalit
IF ( @constraintName != '' )
BEGIN
SET @exec1 = ' use ' + @name + ' ; ' + char ( 13 ) + ' alter table ' + @tableName + ' drop constraint ' + @constraintName
SET @exec = @exec1 + char ( 13 ) + @exec + char ( 13 )
END
ELSE
SET @exec = ' use ' + @name + ' ; ' + char ( 13 ) + @exec
IF EXISTS ( SELECT * FROM tempdb.sys.objects WHERE name = ' ##Temp_dafalit ' )
DROP TABLE ##Temp_dafalit
END
ELSE IF ( @type = ' Z ' ) -- 执行自定义动作
BEGIN
set @exec = ' use ' + @name + ' ; ' + char ( 13 ) +
' IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID( ''' + @tableName + ''' ) AND type = '' U '' )BEGIN ' + char ( 13 ) +
@custom + char ( 13 ) + ' print '' 在 ' + @name + ' 中执行成功 '' END ' + char ( 13 ) +
' ELSE print '' 在 ' + @name + ' 中没有这个表 ''' + char ( 13 )
END
BEGIN try -- 开始执行
IF ( @is_exec = 1 ) EXEC ( @exec )
ELSE PRINT ( @exec )
END try
BEGIN catch
DECLARE @error nvarchar ( 500 )
SET @error = @name + ' 的 ' + @tableName + ' 中失败,原因: ' + char ( 13 ) + ERROR_MESSAGE()
RAISERROR ( @error , 16 , 1 ) -- 抛出错误信息
END catch
SET @sno = @sno + 1
END ; -- 循环结束
IF exists ( SELECT * FROM tempdb.sys.objects WHERE name = ' ##myDB ' )
DROP TABLE ##myDB
GO
写的太复杂了,没办法服务器就要4个,以后可能会更多,暂时就60多个数据库,你说我不怎样写以后改某个字段的时候不是要累死吗?本来的设计就不是很好,增加字段修改数据类型是常有的事情。痛苦啊!
其实这个存储过程很简单,就是在修改某一列的时候先判断一下又没索引,有就先自动生成这个索引的删除和添加语句。然后是默认约束,也是一样有就生成删除的和添加的语句。CHECK约束我没写,一个是很复杂,另一个是我们的所有DB总共不到10个这样的约束,没那必要了。这些语句都生成完后,执行修改前先执行删除的,修改完了执行添加的,这样就一切OK了。
测试了没问题,以后看看能不能继续加点东西上去,还有我加密的主要原因是不想公司的那些高级DBA笑话,写的太幼稚了,效率不好,不过总算是能给我减轻工作压力了,高兴!
这篇Blog的地址:http://blog.csdn.net/hb_gx/archive/2007/06/18/1655990.aspx
我高升也只是个初学者,还是非常希望能得到网络上众多高手的指定的,欢迎各种批评!另外说句实话我这篇文章写的真的很烂!