本文介绍了如何使用SQL Server 2005的存储过程来自动化修改表结构,实现数据库间表结构的同步,而不影响原始数据。作者分享了一个自创的存储过程,其功能类似于在企业管理器中手动操作,适用于需要频繁更新表结构的场景。文中还提到了可能遇到的问题,如外键、触发器和字段长度,并提醒读者根据实际需求调整。经过两个月的测试,该存储过程被证实可以稳定运行。
我们公司的数据库也真是多啊,大大小小的有30多个,大一点的分公司都有自己独立的数据库,然后有些库还需要有给客户和供应商以及自己内部新员工的 Test 库,还有要给开发部门的 develop 库,这些库的结构当然还要求是一样的,因为很多数据是需要上传到集团总部去的,特别是人事和财务方面的数据。而以前的设计跟现在的实际需求相差也有点大的,所以修改表结构的事情几乎每天都有发生,公司上层一个简单的决定真是难为死我这个做数据库维护的人了。
很多时候要增加的字段必须放在要求的位置上,不能放最后,有些字段一需要其他所有关联的字段也有修改,还是所有的数据库中有的都必须修改。当然这都不是很难,都是很简单的事情,可是一个个的改对我懒人高升来说真是莫大的痛苦,所以我又想出了一个偷懒的办法,写了一个修改表的存储过程,比原先写的那个各有各的好处,这个可是模仿 MS 在企业管理器中拖拖鼠标自动生成的代码写的。
USE msdb GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --建立人: 高升 --建立日期:2007/06/25 --修改日期:2007/08/01 --功能目的:复制源DB中某个表的结构到目标DB的同名表中,只改表结构、约束、索引等,不改目标DB的数据 --注意: 默认约束名是自动生成的新名字,如果和以前的名字有冲突请手工修改后运行, -- 新的默认约束名统一为 'DF_表名_列名' ,如果要使用原默认约束名需要修改代码 ALTERPROCEDURE[dbo].[AlterTableLayout] @sourceDB sysname, --源DB名 @targetDB sysname, --目标DB名 @schemaName sysname ='dbo',--架构名,此参数保留,未使用 @sourceTableName sysname, --源表名 @targetTableName sysname ='', --目标表名,默认与源表名相同 @enablebit=0--是否执行 WITH ENCRYPTION AS DECLARE@schema_idint--架构ID DECLARE@tmpTableNamevarchar(100) --临时表名 DECLARE@columnNamevarchar(100) --列名 DECLARE@d_namevarchar(100) --默认约束的约束名 DECLARE@definitionvarchar(100) --默认值 DECLARE@i_namevarchar(100) --索引名 DECLARE@is_keybit--是否主键 DECLARE@i_notinyInt--索引的序号 DECLARE@c_namevarchar(200) --索引所在的列名 DECLARE@i_typevarchar(60) --是否聚集 DECLARE@is_uniquevarchar(6) --是否唯一 DECLARE@is_unique_keybit--是否唯一键 DECLARE@cmd_allvarchar(max) --存放全部语句 DECLARE@cmd_tempnvarchar(max) --存放临时执行的语句 DECLARE@cmd_create_tablevarchar(5000) --存放创建 Table 的语句 DECLARE@cmd_drop_defaultnvarchar(max) --删除默认约束 DECLARE@cmd_add_defaultnvarchar(max) --添加默认约束 DECLARE@cmd_add_indexvarchar(2000) --添加索引 DECLARE@cmd_add_checkvarchar(2000) --添加 CHECK 约束 DECLARE@cmd_add_foreignvarchar(600) --添加外键约束 DECLARE@cmd_insertvarchar(max) --插入语句 DECLARE@c_name_Avarchar(4000) --INSERT语句用 DECLARE@c_name_Bvarchar(4000) --INSERT语句用 DECLARE@identity_onvarchar(60) --关闭自增长 DECLARE@identity_offvarchar(60) --开启自增长 DECLARE@cmd_create_triggernvarchar(max) --创建 TRIGGER 的语句 DECLARE@ismallInt--用于循环 SET NOCOUNT ON IF (@targetTableName='') SET@targetTableName=@sourceTableName SET@schema_id= SCHEMA_ID(@schemaName) SET@columnName='' SET@cmd_add_default='' SET@cmd_drop_default='' SET@cmd_add_index='' DECLARE@columnstable(c_no intidentity,c_name varchar(100))--存放表中所有的列名 DECLARE@indexNametable(i_no tinyIntIDENTITY(1,1), --存放该表中的索引名 i_name varchar(100),type_desc varchar(60),is_unique bit,is_key bit,is_unique_key bit) INSERTINTO@columnsEXEC('SELECT name FROM '+@sourceDB+'.sys.columns WHERE object_id = (SELECT object_id FROM '+@sourceDB+'.sys.tables WHERE name = '''+@sourceTableName+''')') INSERTINTO@indexNameEXEC('SELECT name,type_desc,is_unique,is_primary_key,is_unique_constraint FROM '+@sourceDB+'.sys.indexes WHERE object_id = (SELECT object_id FROM '+@sourceDB+'.sys.tables WHERE name = '''+@sourceTableName+''')') --生成中间过渡临时表的名字 SET@cmd_temp='DECLARE @i tinyInt SET @i = 1 SET @tmpTableName = ''Tmp_'+@targetTableName+''' WHILE (EXISTS (SELECT * FROM '+@targetDB+'.sys.objects WHERE NAME = @tmpTableName AND TYPE = ''U'')) BEGIN SET @tmpTableName = ''Tmp_'+@targetTableName+'_'' + RTRIM(@i) SET @i = @i + 1 END' EXECUTE sp_executesql @cmd_temp,N'@tmpTableName varchar(100) OUTPUT',@tmpTableName OUTPUT --生成创建表的代码 SET@cmd_temp='USE '+@sourceDB+' SET @cmd_create_table = ''CREATE TABLE '+@tmpTableName+'('' + char(13) SELECT @cmd_create_table = @cmd_create_table + ''['' + name + ''] '' + CASE is_computed WHEN 1 THEN (SELECT ''AS '' + definition + CASE is_persisted WHEN ''1'' THEN '' PERSISTED'' ELSE '''' END FROM sys.computed_columns WHERE name = sys.columns.name) ELSE (TYPE_NAME(system_type_id) + CASE WHEN system_type_id in (167,175) THEN ''('' + (CASE max_length WHEN -1 THEN ''MAX'' ELSE RTRIM(max_length) END) + '')'' WHEN system_type_id in (231,239) THEN ''('' + (CASE max_length WHEN -1 THEN ''MAX'' ELSE RTRIM(max_length / 2) END) + '')'' WHEN system_type_id in (106,108) THEN ''('' + RTRIM(precision) + '','' + RTRIM(scale) + '')'' ELSE '''' END + CASE is_nullable WHEN 0 THEN '' NOT NULL'' ELSE '''' END + CASE is_identity WHEN 1 THEN (SELECT TOP 1 '' IDENTITY('' + CAST(seed_value as varchar(10)) + '','' + CAST(increment_value as varchar(10)) + '')'' FROM sys.identity_columns WHERE name = sys.columns.name) ELSE '''' END) END + '','' + char(13) FROM sys.columns where object_id = OBJECT_ID('''+@sourceTableName+''') SET @cmd_create_table = SUBSTRING(@cmd_create_table,1,LEN(@cmd_create_table) - 2) + '') GO ''' EXEC sp_executesql @cmd_temp,N'@cmd_create_table varchar(5000) OUTPUT',@cmd_create_table OUTPUT --生成创建和删除默认约束的代码 SET@i=1 WHILE (@i<= (SELECTCOUNT(c_no) FROM@columns)) BEGIN SELECT@columnName= c_name FROM@columnsWHERE c_no =@i SET@cmd_temp='use '+@sourceDB+char(13) + 'SELECT @d_name = name,@definition = definition FROM sys.default_constraints WHERE object_id = (SELECT default_object_id FROM sys.columns WHERE object_id = OBJECT_ID('''+@sourceTableName+''') AND name = '''+@columnName+''')' SET@d_name='' EXEC sp_executesql @cmd_temp,N'@d_name varchar(60) output,@definition nvarchar(50) output',@d_name OUTPUT,@definition OUTPUT IF (@d_name!='') --此处使用了新默认约束名,原默认约束名保存在 @d_name 中没有使用 SET@cmd_add_default=@cmd_add_default+'ALTER TABLE ['+@tmpTableName+'] ADD CONSTRAINT [DF_'+@targetTableName+'_'+@columnName+'] DEFAULT '+@definition+' FOR ['+@columnName+'] GO ' SET@cmd_temp='use '+@targetDB+char(13) + 'SELECT @d_name = name FROM sys.default_constraints WHERE object_id = (SELECT default_object_id FROM sys.columns WHERE object_id = OBJECT_ID('''+@targetTableName+''') AND name = '''+@columnName+''')' SET@d_name='' EXEC sp_executesql @cmd_temp,N'@d_name varchar(60) output',@d_name OUTPUT IF (@d_name!='') SET@cmd_drop_default=@cmd_drop_default+'ALTER TABLE ['+@targetTableName+'] DROP CONSTRAINT ['+@d_name+'] GO ' SET@i=@i+1 END --生成创建索引的代码,没有考虑填充因子等选项,使用的是默认值 SET@i=1 WHILE (@i<= (SELECTCOUNT(i_no) FROM@indexName)) BEGIN SELECT@i_name= i_name,@i_type= type_desc,@is_unique= is_unique,@is_key= is_key FROM@indexNameWHERE i_no =@i IF (@i_nameisnull) --如果没有索引或键直接退出 BREAK; SET@cmd_temp='set @c_name = '''' SELECT @c_name = @c_name + '',['' + a.name + (CASE b.is_descending_key WHEN 1 THEN ''] DESC'' ELSE ''] ASC'' END) FROM '+@sourceDB+'.sys.columns a inner join '+@sourceDB+'.sys.index_columns b ON a.object_id = b.object_id AND a.column_id = b.column_id inner join '+@sourceDB+'.sys.indexes c ON b.object_id = c.object_id AND b.index_id = c.index_id WHERE a.object_id = (SELECT object_id FROM '+@sourceDB+'.sys.tables WHERE name = '''+@sourceTableName+''') AND c.name = '''+@i_name+'''' EXEC sp_executesql @cmd_temp,N'@c_name varchar(200) output',@c_name output IF (@is_key=1) --键和索引的创建方法不一样 SET@cmd_add_index=@cmd_add_index+'ALTER TABLE ['+@targetTableName+'] ADD CONSTRAINT ['+REPLACE(@i_name,@sourceTableName,@targetTableName) +'] PRIMARY KEY '+@i_type+char(13) +'('+SUBSTRING(@c_name,2,len(@c_name)) +')'+' GO ' ELSEIF (@is_unique_key=1) --唯一键 SET@cmd_add_index=@cmd_add_index+'ALTER TABLE ['+@targetTableName+'] ADD CONSTRAINT ['+REPLACE(@i_name,@sourceTableName,@targetTableName) +'] UNIQUE '+@i_type+char(13) +'('+SUBSTRING(@c_name,2,len(@c_name)) +')'+' GO ' ELSE--普通索引 SET@cmd_add_index=@cmd_add_index+'CREATE '+ (CASE@is_uniqueWHEN1THEN'UNIQUE'ELSE''END) +''+@i_type+' INDEX ['+REPLACE(@i_name,@sourceTableName,@targetTableName) +'] ON ['+@targetTableName+']'+char(13) +'('+SUBSTRING(@c_name,2,len(@c_name)) +')'+' GO ' SET@i=@i+1--循环下一个键或索引 END--end while --生成创建 CHECK 约束的代码 SET@cmd_temp='SET @cmd_add_check = '''' SELECT @cmd_add_check = @cmd_add_check + ''ALTER TABLE ['+@targetTableName+'] WITH NOCHECK ADD CONSTRAINT '' + name + '' CHECK '' + definition + '' GO '' FROM '+@sourceDB+'.sys.check_constraints WHERE parent_object_id = (SELECT object_id FROM '+@sourceDB+'.sys.tables WHERE name = '''+@sourceTableName+''')' EXEC sp_executesql @cmd_temp,N'@cmd_add_check varchar(2000) OUTPUT',@cmd_add_check OUTPUT --判断是否有自增长列 SET@cmd_temp='IF EXISTS (SELECT name FROM '+@sourceDB+'.sys.columns WHERE object_id = (SELECT object_id FROM '+@sourceDB+'.sys.tables WHERE name = '''+@sourceTableName+''') AND is_identity = 1) BEGIN SET @identity_on = ''SET IDENTITY_INSERT ['+@tmpTableName+'] ON GO '' SET @identity_off = ''SET IDENTITY_INSERT ['+@targetTableName+'] OFF GO '' END ELSE BEGIN SET @identity_on = '''' SET @identity_off = '''' END' EXEC sp_executesql @cmd_temp,N'@identity_on varchar(60) OUTPUT,@identity_off varchar(60) OUTPUT',@identity_on OUTPUT,@identity_off OUTPUT --生成创建外键约束的代码 SET@cmd_temp='SET @cmd_add_foreign = '''' SELECT @cmd_add_foreign = @cmd_add_foreign + ''ALTER TABLE '+@targetTableName+' ADD CONSTRAINT '' + a.name + '' FOREIGN KEY ('' + (SELECT name FROM '+@sourceDB+'.sys.columns WHERE OBJECT_ID = b.parent_object_id AND column_id = b.parent_column_id) + '') REFERENCES '' + (SELECT name FROM '+@sourceDB+'.sys.tables WHERE object_id = a.referenced_object_id) + ''('' + (SELECT name FROM '+@sourceDB+'.sys.columns WHERE OBJECT_ID = b.referenced_object_id AND column_id = b.referenced_column_id) + '') ON UPDATE '' + CASE update_referential_action WHEN 0 THEN ''SET NULL'' WHEN 1 THEN ''CASCADE'' WHEN 2 THEN ''NO ACTION'' ELSE ''SET DEFAULT'' END + '' ON DELETE '' + CASE delete_referential_action WHEN 0 THEN ''SET NULL'' WHEN 1 THEN ''CASCADE'' WHEN 2 THEN ''NO ACTION'' ELSE ''SET DEFAULT'' END + '' GO '' FROM '+@sourceDB+'.sys.foreign_keys a INNER JOIN '+@sourceDB+'.sys.foreign_key_columns b ON a.object_id = b.constraint_object_id WHERE a.parent_object_id = (SELECT object_id FROM '+@sourceDB+'.sys.tables WHERE name = '''+@sourceTableName+''')' EXEC sp_executesql @cmd_temp,N'@cmd_add_foreign varchar(500) OUTPUT',@cmd_add_foreign OUTPUT --生成 INSERT 语句 SET@cmd_temp='SELECT @c_A = '''', @c_B = '''' SELECT @c_A = @c_A + ''['' + a.name + ''],'',@c_B = @c_B + CASE WHEN b.name IS NOT NULL THEN ''['' + b.name + '']'' ELSE (CASE WHEN default_object_id != 0 THEN definition WHEN is_nullable = 0 THEN (CASE WHEN system_type_id in(48,52,56,59,60,62,106,108,122,127)THEN ''0'' ELSE '''''''''''' END) ELSE ''NULL'' END)END + '','' FROM (SELECT a.name,a.system_type_id,a.is_nullable,a.default_object_id,b.definition FROM '+@sourceDB+'.sys.columns a LEFT JOIN '+@sourceDB+'.sys.default_constraints b ON a.object_id = b.parent_object_id AND a.default_object_id = b.object_id WHERE a.object_id = (SELECT object_id from '+@sourceDB+'.sys.objects WHERE type = ''U'' AND name = '''+@sourceTableName+''' AND is_computed = 0))a LEFT JOIN (SELECT name FROM '+@targetDB+'.sys.columns WHERE object_id = (SELECT object_id from '+@targetDB+'.sys.objects WHERE type = ''U'' AND name = '''+@targetTableName+''' AND is_computed = 0))b ON a.name = b.name' EXEC sp_executesql @cmd_temp,N'@c_A varchar(4000) OUTPUT,@c_B varchar(4000) OUTPUT',@c_name_A OUTPUT,@c_name_B OUTPUT SET@cmd_insert=CAST('INSERT INTO ['asvarchar(max)) +@tmpTableName+']('+SUBSTRING(@c_name_A,1,LEN(@c_name_A) -1) +') SELECT '+SUBSTRING(@c_name_B,1,LEN(@c_name_B) -1) +' FROM ['+@targetTableName+'] WITH (HOLDLOCK TABLOCKX) GO ' --生成创建 TRIGGER 的语句 SET@cmd_temp='SET @cmd_create_trigger = '''' SELECT @cmd_create_trigger = @cmd_create_trigger + '' exec('''''' + REPLACE(definition,'''''''','''''''''''') + '' '''');'' FROM '+@targetDB+'.sys.sql_modules WHERE object_id in (SELECT object_id FROM '+@targetDB+'.sys.triggers WHERE parent_id = (SELECT object_id FROM '+@targetDB+'.sys.tables WHERE name = '''+@targetTableName+'''))' EXEC sp_executesql @cmd_temp,N'@cmd_create_trigger nvarchar(max) OUTPUT',@cmd_create_trigger OUTPUT --汇总所有的语句 SET@cmd_all='BEGIN TRANSACTION SET QUOTED_IDENTIFIER ON SET ARITHABORT ON SET NUMERIC_ROUNDABORT OFF SET CONCAT_NULL_YIELDS_NULL ON SET ANSI_NULLS ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON COMMIT USE ['+@targetDB+'] GO BEGIN TRANSACTION GO '+@cmd_drop_default+@cmd_create_table+@cmd_add_default+@identity_on+@cmd_insert+ 'DROP TABLE ['+@targetTableName+'] GO EXECUTE sp_rename N'''+@tmpTableName+''',N'''+@targetTableName+''',''OBJECT'' GO '+@cmd_add_index+@identity_off+'COMMIT' --执行或打印生成的语句 IF (@enable=1) BEGIN --批处理的时候去掉 @cmd_all 中的 'GO' 以后才能执行 SET@cmd_all=REPLACE(@cmd_all,('GO'+char(13)),'') BEGINTRANSACTION BEGIN TRY EXEC (@cmd_all) IF (@cmd_add_check+@cmd_add_foreign!='') BEGIN--这里有点麻烦,必须把上面的建好才能建约束,不知道为什么 SET@cmd_all='USE ['+@targetDB+']; '+@cmd_add_check+@cmd_add_foreign SET@cmd_all=REPLACE(@cmd_all,('GO'+char(13)),'') EXEC (@cmd_all) END IF (@cmd_create_trigger!='') --如果表有触发器则继续添加触发器 BEGIN SET@cmd_create_trigger='USE ['+@targetDB+']; '+@cmd_create_trigger EXEC (@cmd_create_trigger) END COMMIT END TRY BEGIN CATCH print (ERROR_MESSAGE()) ROLLBACK END CATCH END--end if ELSEIF (@enable=0) BEGIN SET@cmd_all='/*为了防止任何可能出现的数据丢失问题,您应该先仔细检查此脚本,然后再在数据库设计器的上下文之外运行此脚本。*/ '+@cmd_all+char(13) +@cmd_create_trigger PRINTSUBSTRING(@cmd_all,1,8000) -- PRINT 一次最多打印8000个字符 SET@i=0 WHILE (((LEN(@cmd_all) -@i*8000) /8000) >0) BEGIN--如果输出的字符串大于8000则循环打印出来 SET@i=@i+1 PRINTSUBSTRING(@cmd_all,@i*8000+1,8000) END--end while END--end else if ELSE--如果传入的是 null 则直接执行,抛出具体的错误信息,建议不要使用 BEGIN SET@cmd_all=REPLACE(@cmd_all,('GO'+char(13)),'') +@cmd_create_trigger EXEC (@cmd_all) END--end else GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO