Sql Server 2005 自动生成修改表的代码(存储过程)

最新推荐文章于 2021-11-01 22:37:29 发布

原创最新推荐文章于 2021-11-01 22:37:29 发布 · 5.2k 阅读

8 ·

CC 4.0 BY-SA版权

文章标签：

#存储 #sql server #cmd #output #object #table

SQL 专栏收录该内容

8 篇文章

订阅专栏

本文介绍了如何使用SQL Server 2005的存储过程来自动化修改表结构，实现数据库间表结构的同步，而不影响原始数据。作者分享了一个自创的存储过程，其功能类似于在企业管理器中手动操作，适用于需要频繁更新表结构的场景。文中还提到了可能遇到的问题，如外键、触发器和字段长度，并提醒读者根据实际需求调整。经过两个月的测试，该存储过程被证实可以稳定运行。

我们公司的数据库也真是多啊，大大小小的有30多个，大一点的分公司都有自己独立的数据库，然后有些库还需要有给客户和供应商以及自己内部新员工的 Test 库，还有要给开发部门的 develop 库，这些库的结构当然还要求是一样的，因为很多数据是需要上传到集团总部去的，特别是人事和财务方面的数据。而以前的设计跟现在的实际需求相差也有点大的，所以修改表结构的事情几乎每天都有发生，公司上层一个简单的决定真是难为死我这个做数据库维护的人了。

很多时候要增加的字段必须放在要求的位置上，不能放最后，有些字段一需要其他所有关联的字段也有修改，还是所有的数据库中有的都必须修改。当然这都不是很难，都是很简单的事情，可是一个个的改对我懒人高升来说真是莫大的痛苦，所以我又想出了一个偷懒的办法，写了一个修改表的存储过程，比原先写的那个各有各的好处，这个可是模仿 MS 在企业管理器中拖拖鼠标自动生成的代码写的。

原先那个存储过程：http://blog.youkuaiyun.com/hb_gx/archive/2007/06/18/1655990.aspx

测试的效果还是很不错的，高兴！虽然又浪费我一个双休，可是今天上班真是轻松啊，旁边那个高级一点的DBA自己点鼠标点的只响也不怕麻烦，想我高升这辈子偷懒也是偷出了点小名堂的。：）

本存储过程能把某个数据库的表结构复制到指定的数据库同名表中，但是不改目标数据库中的数据，原始数据还是保留的，跟数据库复制是有区别的，如果你有两个数据库是一样的，只是数据不同，那么当其中一个修改了结构后可以使用本存储过程帮你自动更新另一个结构。其实就是和在企业管理器中拖拖鼠标一样的，只是可以换到别的数据库生成。

USE msdb

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- 建立人：高升

-- 建立日期：2007/06/25

-- 修改日期：2007/08/01

-- 功能目的：复制源DB中某个表的结构到目标DB的同名表中，只改表结构、约束、索引等，不改目标DB的数据

-- 注意：默认约束名是自动生成的新名字，如果和以前的名字有冲突请手工修改后运行，

-- 新的默认约束名统一为 'DF_表名_列名' ,如果要使用原默认约束名需要修改代码

ALTER PROCEDURE [ dbo ] . [ AlterTableLayout ]

@sourceDB sysname, -- 源DB名

@targetDB sysname, -- 目标DB名

@schemaName sysname = ' dbo ' , -- 架构名,此参数保留，未使用

@sourceTableName sysname, -- 源表名

@targetTableName sysname = '' , -- 目标表名,默认与源表名相同

@enable bit = 0 -- 是否执行

WITH ENCRYPTION

DECLARE @schema_id int -- 架构ID

DECLARE @tmpTableName varchar ( 100 ) -- 临时表名

DECLARE @columnName varchar ( 100 ) -- 列名

DECLARE @d_name varchar ( 100 ) -- 默认约束的约束名

DECLARE @definition varchar ( 100 ) -- 默认值

DECLARE @i_name varchar ( 100 ) -- 索引名

DECLARE @is_key bit -- 是否主键

DECLARE @i_no tinyInt -- 索引的序号

DECLARE @c_name varchar ( 200 ) -- 索引所在的列名

DECLARE @i_type varchar ( 60 ) -- 是否聚集

DECLARE @is_unique varchar ( 6 ) -- 是否唯一

DECLARE @is_unique_key bit -- 是否唯一键

DECLARE @cmd_all varchar ( max ) -- 存放全部语句

DECLARE @cmd_temp nvarchar ( max ) -- 存放临时执行的语句

DECLARE @cmd_create_table varchar ( 5000 ) -- 存放创建 Table 的语句

DECLARE @cmd_drop_default nvarchar ( max ) -- 删除默认约束

DECLARE @cmd_add_default nvarchar ( max ) -- 添加默认约束

DECLARE @cmd_add_index varchar ( 2000 ) -- 添加索引

DECLARE @cmd_add_check varchar ( 2000 ) -- 添加 CHECK 约束

DECLARE @cmd_add_foreign varchar ( 600 ) -- 添加外键约束

DECLARE @cmd_insert varchar ( max ) -- 插入语句

DECLARE @c_name_A varchar ( 4000 ) -- INSERT语句用

DECLARE @c_name_B varchar ( 4000 ) -- INSERT语句用

DECLARE @identity_on varchar ( 60 ) -- 关闭自增长

DECLARE @identity_off varchar ( 60 ) -- 开启自增长

DECLARE @cmd_create_trigger nvarchar ( max ) -- 创建 TRIGGER 的语句

DECLARE @i smallInt -- 用于循环

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 @columns table (c_no int identity ,c_name varchar ( 100 )) -- 存放表中所有的列名

DECLARE @indexName table (i_no tinyInt IDENTITY ( 1 , 1 ), -- 存放该表中的索引名

i_name varchar ( 100 ),type_desc varchar ( 60 ),is_unique bit ,is_key bit ,is_unique_key bit )

INSERT INTO @columns EXEC ( ' SELECT name FROM ' + @sourceDB + ' .sys.columns WHERE object_id = (SELECT object_id FROM ' + @sourceDB + ' .sys.tables WHERE name = ''' + @sourceTableName + ''' ) ' )

INSERT INTO @indexName EXEC ( ' 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) + '' )

'''

EXEC sp_executesql @cmd_temp ,N ' @cmd_create_table varchar(5000) OUTPUT ' , @cmd_create_table OUTPUT

-- 生成创建和删除默认约束的代码

SET @i = 1

WHILE ( @i <= ( SELECT COUNT (c_no) FROM @columns ))

BEGIN

SELECT @columnName = c_name FROM @columns WHERE 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 + ' ]

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 + ' ]

SET @i = @i + 1

END

-- 生成创建索引的代码，没有考虑填充因子等选项，使用的是默认值

SET @i = 1

WHILE ( @i <= ( SELECT COUNT (i_no) FROM @indexName ))

BEGIN

SELECT @i_name = i_name, @i_type = type_desc, @is_unique = is_unique, @is_key = is_key FROM @indexName WHERE i_no = @i

IF ( @i_name is null ) -- 如果没有索引或键直接退出

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 )) + ' ) ' + '

ELSE IF ( @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 )) + ' ) ' + '

ELSE -- 普通索引

SET @cmd_add_index = @cmd_add_index + ' CREATE ' + ( CASE @is_unique WHEN 1 THEN ' UNIQUE ' ELSE '' END ) + ' ' + @i_type + ' INDEX [ ' + REPLACE ( @i_name , @sourceTableName , @targetTableName ) + ' ] ON [ ' + @targetTableName + ' ] ' + char ( 13 ) + ' ( ' + SUBSTRING ( @c_name , 2 , len ( @c_name )) + ' ) ' + '

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 + ''

'' 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

SET @identity_off = '' SET IDENTITY_INSERT [ ' + @targetTableName + ' ] OFF

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 + ''

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 [ ' as varchar ( 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)

-- 生成创建 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 + ' ]

BEGIN TRANSACTION

' + @cmd_drop_default + @cmd_create_table + @cmd_add_default + @identity_on + @cmd_insert +

' DROP TABLE [ ' + @targetTableName + ' ]

EXECUTE sp_rename N ''' + @tmpTableName + ''' ,N ''' + @targetTableName + ''' , '' OBJECT ''

' + @cmd_add_index + @identity_off + ' COMMIT '

-- 执行或打印生成的语句

IF ( @enable = 1 )

BEGIN

-- 批处理的时候去掉 @cmd_all 中的 'GO' 以后才能执行

SET @cmd_all = REPLACE ( @cmd_all ,( ' GO ' + char ( 13 )), '' )

BEGIN TRANSACTION

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

ELSE IF ( @enable = 0 )

BEGIN

SET @cmd_all = ' /*为了防止任何可能出现的数据丢失问题，您应该先仔细检查此脚本，然后再在数据库设计器的上下文之外运行此脚本。*/

' + @cmd_all + char ( 13 ) + @cmd_create_trigger

PRINT SUBSTRING ( @cmd_all , 1 , 8000 ) -- PRINT 一次最多打印8000个字符

SET @i = 0

WHILE ((( LEN ( @cmd_all ) - @i * 8000 ) / 8000 ) > 0 )

BEGIN -- 如果输出的字符串大于8000则循环打印出来

SET @i = @i + 1

PRINT SUBSTRING ( @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

SET ANSI_NULLS OFF

SET QUOTED_IDENTIFIER OFF

最后想了一想，发现外键这个东西不好加的，我一直不喜欢用，我们公司里所有的表都没有建立外键关系的，所以没有怎么测试的，使用的时候注意一下。还有如果表有触发器，修改的时候注意触发器中某些特定字符，可能会导致执行失败。另外定义的那些字段的长度并不是太好指定，如果全部都用varchar(max)好像也没那个必要的，如果遇到某些错误，可能是字段定义的长度问题，这个还是要根据实际情况了决定，定义多了也是浪费。个人认为一个表内字段可能有很多，相应的默认约束也会很多，但INDEX和CHECK约束通就常不会有很多了。

建立一个测试环境，测试一下，看看存储过程的效果：

-- 创建第一个测试库和一个表，有很多的类型和计算列约束之类的东东

CREATE DATABASE Test1

USE Test1

CREATE TABLE dbo.T1

(

t2 int NULL ,

t1 uniqueidentifier NOT NULL ,

t3 char ( 4 ) NULL ,

t4 varchar ( 8 ) NOT NULL ,

t5 decimal ( 6 , 2 ) NOT NULL ,