首先我要叫声：操阿 、操阿 、操阿 、操阿 、操阿 、操阿 、操阿 、操阿 、操阿 、操阿 、操阿 、操阿 、操阿 、操阿 、操阿 、操阿 、操阿 、操阿 、操阿 。。。
这几天碰到不开心的事情，但是这些不开心的事情暂时不能在这里论述，只能发泄一番 。。。
偶已经好几天没兑现我每天写博客技术文章的诺言了 。
今天要记录一下关于SQLSERVER2005中导表结构的方案，当然这里的结构是包含【键、索引、约束、触发器】等等一大堆东西的。
其实偶就是操了别人的存储过程罢了 ， 代码贴出来供自己以后回顾：
USE MobileCommunity
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--功能目的：复制源DB中某个表的结构到目标DB的同名表中，只改表结构、约束、索引等，不改目标DB的数据
--注意： 默认约束名是自动生成的新名字，如果和以前的名字有冲突请手工修改后运行，
-- 新的默认约束名统一为 'DF_表名_列名' ,如果要使用原默认约束名需要修改代码
CREATE PROCEDURE [dbo].[NovelTableLayout11]
@sourceDB sysname, --源DB名
@targetDB sysname, --目标DB名
@schemaName sysname,--架构名,此参数保留，未使用
@sourceTableName sysname, --源表名
@targetTableName sysname, --目标表名,默认与源表名相同
@enable bit = 0 --是否执行
WITH ENCRYPTION
AS
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) + '')
GO
'''
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 + ']
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 <= (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)) + ')' + '
GO
'
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)) + ')' + '
GO
'
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)) + ')' + '
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 [' 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)
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)),'')
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
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--功能目的：复制源DB中某个表的结构到目标DB的同名表中，只改表结构、约束、索引等，不改目标DB的数据
--注意： 默认约束名是自动生成的新名字，如果和以前的名字有冲突请手工修改后运行，
-- 新的默认约束名统一为 'DF_表名_列名' ,如果要使用原默认约束名需要修改代码
CREATE PROCEDURE [dbo].[NovelTableLayout11]
@sourceDB sysname, --源DB名
@targetDB sysname, --目标DB名
@schemaName sysname,--架构名,此参数保留，未使用
@sourceTableName sysname, --源表名
@targetTableName sysname, --目标表名,默认与源表名相同
@enable bit = 0 --是否执行
WITH ENCRYPTION
AS
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) + '')
GO
'''
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 + ']
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 <= (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)) + ')' + '
GO
'
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)) + ')' + '
GO
'
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)) + ')' + '
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 [' 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)
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)),'')
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
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
创建一个要备份的目标数据表：
CREATE TABLE Old_Novel_BookInfo
(
id int not null,
) --这里字段可以随意写，因为之后都会被新的结构覆盖
(
id int not null,
) --这里字段可以随意写，因为之后都会被新的结构覆盖
将数据导入备份目标表：
EXEC NovelTableLayout '源数据库' , '目标数据库' , '新约束前缀' ,'Novel_BookInfo' ,'Old_Novel_BookInfo' , '1'
最后一步就是完成将历史数据导入备份数据表：
INSERT INTO Old_Novel_BookInfo SELECT * FROM Novel_BookInfo
浙公网安备 33010602011771号