`

SQL Server初始化表:删除数据及主键复位(二)

 
阅读更多

上一篇用的是临时表,执行之后特殊应用都会消失。后面有讲到SQL Server本身只怎么操作的,就模拟了一个——添加触发器特殊应用。

 

1、创建S_ENTITY表和insert测试数据上一篇有这一篇就不写了。

 

2、创建Table2表并添加测试触发器:

--创建表:
CREATE TABLE [dbo].[table2_2](
	[field1] [int] IDENTITY(1,1) NOT NULL,
	[field2] [int] NULL,
	[field3] [varchar](100) COLLATE Chinese_PRC_CI_AS NULL,
	[field4] [varchar](300) COLLATE Chinese_PRC_CI_AS NULL,
	[field10] [int] NULL,
	[field5] [varchar](200) COLLATE Chinese_PRC_CI_AS NULL,
	[field6] [decimal](30, 2) NULL,
	[field7] [decimal](30, 2) NULL,
	[field8] [decimal](30, 2) NULL,
	[field9] [decimal](20, 3) NULL,
 CONSTRAINT [PK_table2_2] PRIMARY KEY CLUSTERED 
(
	[field1] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

--添加测试触发器
-- =============================================
-- Author:		<wjl>
-- Create date: <2015-5-13>
-- Description:	<测试触发器>
-- =============================================
ALTER TRIGGER [TestTrigger] 
   ON  [dbo].[table2]
   AFTER DELETE
AS 
BEGIN
	
	SET NOCOUNT ON;
	
	print '****************************************************'
END

 

3、先查看一下SQL Server对于表中插入一列的处理过程:在table2添加一列field10,在保存之前“生成更改脚本”,以下为其脚本信息:

/*
   2015年5月13日11:01:49
   用户: sa
   服务器: 192.168.0.110
   数据库: ac
   应用程序: 
*/

/* 为了防止任何可能出现的数据丢失问题,您应该先仔细检查此脚本,然后再在数据库设计器的上下文之外运行此脚本。*/
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
BEGIN TRANSACTION
GO
--1、创建表
CREATE TABLE dbo.Tmp_table2
	(
	field1 int NOT NULL IDENTITY (1, 1),
	field2 int NULL,
	field3 varchar(100) NULL,
	field4 varchar(300) NULL,
	field10 int NULL,
	field5 varchar(200) NULL,
	field11 int NULL,
	field6 decimal(30, 2) NULL,
	field7 decimal(30, 2) NULL,
	field8 decimal(30, 2) NULL,
	field9 decimal(20, 3) NULL
	)  ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_table2 ON
GO
--2、insert表数据
IF EXISTS(SELECT * FROM dbo.table2)
	 EXEC('INSERT INTO dbo.Tmp_table2 (field1, field2, field3, field4, field10, field5, field6, field7, field8, field9)
		SELECT field1, field2, field3, field4, field10, field5, field6, field7, field8, field9 FROM dbo.table2 WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_table2 OFF
GO
--3、删除原始表
DROP TABLE dbo.table2
GO
--4、重命名
EXECUTE sp_rename N'dbo.Tmp_table2', N'table2', 'OBJECT' 
GO
--5、添加主键信息
ALTER TABLE dbo.table2 ADD CONSTRAINT
	PK_table2 PRIMARY KEY CLUSTERED 
	(
	field1
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
--6、添加触发器信息
-- =============================================
-- Author:		<wjl>
-- Create date: <2015-5-13>
-- Description:	<测试触发器>
-- =============================================
CREATE TRIGGER TestTrigger 
   ON  dbo.table2
   AFTER DELETE
AS 
BEGIN
	
	SET NOCOUNT ON;
	
	print '****************************************************'
END
GO
COMMIT

 以上1234的注释为个人所加,生成脚本时并没有这些。

 

 4、个人模拟:

-- =============================================
-- Author:		<wjl>
-- Create date: <2015-5-13>
-- Description:	<初始化业务表中的数据,包括删除业务表数据并复位主键>
-- =============================================
ALTER PROCEDURE [dbo].[initData2] 
	
AS
BEGIN
	SET NOCOUNT ON;

	--1、从实体表中获取业务数据表:以table开头
	declare @count int;--记录符合条件的表的数量
	select @count = count(S_ID) from S_ENTITY where ENTITYNAME like '%table%' 
	print @count;
	if(@count>0)--说明有符合条件的table
	begin
		declare @c int;
		declare @tableName varchar(100);--表名
		declare @tempTableName varchar(100);--临时物理表名称
		declare @PKColumn varchar(50);--主键所在列名称
		declare @sql varchar(200);--拼接的SQL语句
		declare @triggerText varchar(4000);--触发器定义信息
		set @c = 1;
		while(@c<=@count)--依次循环各个表
			begin
				--获取表名称 
				select @tableName = ENTITYNAME from (
				        select ENTITYNAME,row_number() over(order by S_ID) as fn from S_ENTITY 
						where ENTITYNAME like '%table%') 
				as a where fn=@c;
				print @tableName;
				
				--2、将表结构复制到临时物理表中
				set @tempTableName = 'TEMP_TAB_'+@tableName;
				print @tempTableName;
				--判断临时物理表是否存在
				if object_id(N''+@tempTableName+'',N'U') is not null
				begin
					print '临时物理表存在';
					set @sql = 'drop table '+@tempTableName+';'--若存在则删除
					exec(@sql);
				end
				--判断原来的物理表是否存在
				if object_id(N''+@tableName+'',N'U') is not null
				print '物理表存在'
				else
				begin
				print @tableName;
				print '物理表不存在'
				set @c=@c+1;--循环数加1,否则会死循环
				continue;--不存在跳出当前循环,进行下一个循环
				end

				set @sql = 'SELECT * INTO '+@tempTableName+' FROM '+@tableName+ ' where 1=2';--只复制结构,不复制数据
				print @sql;
				exec(@sql);
				
				--3、获取主键所在列:			
				SELECT @PKColumn=COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME=@tableName; 
				print @PKColumn;
				
				--4、获取当前表的触发器信息
				select @triggerText = c.text  
				FROM sysobjects a  
					INNER JOIN sys.triggers b  
						ON b.object_id=a.id  
					INNER JOIN syscomments c  
						ON c.id=a.id  
				WHERE a.xtype='tr' and (a.parent_obj=object_id(@tableName));
				
				--5、删除原有表
				set @sql = 'drop table '+@tableName+';'
				print @sql;
				exec(@sql);
		
				--5、将临时物理表更名为原来物理表名称
				set @sql = 'EXECUTE sp_rename N'''+@tempTableName+''', N'''+@tableName+''', ''OBJECT'' ';
				print @sql;
				exec(@sql);
			
				--6、为新表添加主键
				set @sql ='alter table '+@tableName+' Add  Constraint  PK_'+@tableName+'  Primary  Key ('+@PKColumn+')';
				print @sql;
				exec(@sql);--执行SQL

				--7、为新表添加触发器信息
				if(@triggerText is not null and @triggerText<>'')--说明有触发器信息
				begin
				print @triggerText;
				exec(@triggerText);--执行触发器信息

				end
			set @c=@c+1;
			end
	end

END

 

 5、以物理表table2_2进行测试,执行initData2存储过程之后控制台输出:

1
table2_2
TEMP_TAB_table2_2
物理表存在
SELECT * INTO TEMP_TAB_table2_2 FROM table2_2 where 1=2
field1
drop table table2_2;
EXECUTE sp_rename N'TEMP_TAB_table2_2', N'table2_2', 'OBJECT' 
警告: 更改对象名的任一部分都可能会破坏脚本和存储过程。
alter table table2_2 Add  Constraint  PK_table2_2  Primary  Key (field1)
-- =============================================
-- Author:		<wjl>
-- Create date: <2015-5-13>
-- Description:	<测试触发器>
-- =============================================
CREATE TRIGGER [TestTrigger2] 
   ON  [dbo].[table2_2]
   AFTER DELETE
AS 
BEGIN
	
	SET NOCOUNT ON;
	
	print '****************************************************'
END

 

6、查询table2的触发器信息:

SELECT  
    object_name(a.parent_obj) as tableName 
    ,a.name as triggerName 
    ,(case when b.is_disabled=0 then '启用' else '禁用' end) as status  
    ,b.create_date as createDate
    ,b.modify_date as modifyDate  
    ,c.text as triggerText  
FROM sysobjects a  
    INNER JOIN sys.triggers b  
        ON b.object_id=a.id  
    INNER JOIN syscomments c  
        ON c.id=a.id  
WHERE a.xtype='tr' and (a.parent_obj=object_id('table2'))
ORDER BY tableName

 输出结果:

 

 

  • 大小: 75.7 KB
分享到:
评论

相关推荐

    SqlServer数据库OGG安装部署及数据初始化.docx

    在这个场景中,我们关注的是如何在SQL Server数据库环境下安装配置Oracle GoldenGate(OGG),并将数据从SQL Server实时同步到Oracle数据库。以下是对整个过程的详细说明: 首先,确保你的SQL Server是企业版,并且...

    SqlServer删除所有表数据语句

    Sql Server 删除所有表数据语句 Sql Server 是一种关系型数据库管理系统,广泛应用于企业级应用程序中。在实际开发和测试中,我们常常需要删除数据库中的所有表数据,而保持表结构不变。下面我们将介绍如何使用一条...

    导出sqlserver 2000的表结构

    在IT领域,特别是数据库管理与开发中,导出SQL Server 2000的表结构是一项常见且重要的任务。这不仅对于数据迁移、备份、分析或文档化数据库结构至关重要,而且也是确保数据完整性和可移植性的关键步骤。下面将详细...

    Microsoft SQL Server 2005技术内幕:T-SQ程序设计 【中文版】 1

    Microsoft SQL Server 2005技术内幕: T-SQ程序设计 中文版本的 第一部分 第二部分地址:http://download.csdn.net/source/2680866

    解决Sqlserver2008(sql2008)修改数据表数据类型或主键后,提示无法保存,失败的问题!.doc

    在SQL Server 2008 (包括R2版本)中,有时用户在尝试修改数据表的数据类型或主键设置后,会遇到一个错误提示,表明无法保存所做的更改,导致操作失败。这个问题通常与数据库设计者的一项默认设置有关,即“阻止保存...

    Microsoft SQL Server 2005技术内幕:存储引擎(中文).pdf

    SQL Server 2005微软官方权威参考书.  公球公认SQL Server 2005 经典著作..  数据库“铁人”、微软MVP胡百敬先生鼎力推荐  微软SQL Server 总部Principal Group 项目经理朱凌志鼎力推荐  本书详细介绍了数据...

    sqlserver在有数据情况下修改主键为自增1

    sqlserver在有数据情况下修改主键为自增 网上方法都有缺陷 自己写了一个版本,欢迎大家参考,不好用吗,大家都不评价

    SQLServer数据库表中数据导出成SQL语句工具

    在SQL Server数据库管理中,有时候我们需要将数据库表中的数据导出为SQL语句,以便于备份、迁移或者在其他环境中重建相同的数据结构和内容。针对这个需求,存在一种名为“SQLServer数据库表中数据导出成SQL语句工具...

    SqlServer转Oracle工具(支持表结构、主键及表数据导出)

    运行环境:.net4.5,原创工具 导出后无需修改sql即可执行导入。 SqlServer转Oracle工具 支持表结构、主键及表数据导出

    Microsoft_SQL_Server_2005技术内幕:T-SQL查询.pdf

    本书及其续篇——《Microsoft SQL Server 2005技术内幕:T-SQL程序设计》介绍了SQL Server 2005中高级T-SQL查询、查询优化及编程相关的知识。这两本书侧重于解决实践中的常见问题,并讨论了解决这些问题的方法。它们...

    深入探讨SQL_Server_表的主键问题

    在SQL Server中,主键是表设计中的核心概念,它用于唯一标识表中的每一行记录。主键的设计和选择对数据库的性能、可维护性和扩展性有着深远影响。本文主要探讨了在MS SQL Server中遇到的主键设计问题及其解决方案。 ...

    sqlserver 导出表及字段说明脚本.rar

    这个压缩包"sqlserver 导出表及字段说明脚本.rar"包含了一个名为"sqlserver 导出表及字段说明脚本.sql"的文件,这通常是一个用于生成SQL语句的脚本,用于描述数据库中的表结构以及各字段的详细信息。以下是对这个...

    40集SQL Server 基础入门视频教程 SQL Server 数据库基础入门必备课程

    10.SQL Server 2014定义表主键、外键.mp4 11.SQL Server 2014新增表记录.mp4 12.SQL Server 2014查询表记录.mp4 13.SQL Server 2014修改表记录.mp4 14.SQL Server 2014删除表记录.mp4 15.SQL Server 2014条件...

    Navicat 实现同步sqlserver表结构到mysql操作手册1

    本文将详细介绍如何使用Navicat工具实现SQL Server表结构到MySQL的同步操作,这对于那些需要在不同数据库系统间进行数据迁移的开发者来说尤其有用。 Navicat是一款强大的数据库管理工具,支持多种数据库类型,包括...

    SqlServer查看表结构

    本篇文章将深入解析“SqlServer查看表结构”的方法,以及如何利用SQL语句来获取详细的表结构信息,这对于数据库管理员(DBA)、数据分析师、软件开发者等专业人士来说尤为关键。 ### SQL Server查看表结构的重要性 ...

    根据SQLServer数据表生成C#实体类。生成数据库模型.zip

    本压缩包"根据SQLServer数据表生成C#实体类"提供了相关的工具和源码,旨在帮助开发者快速创建与数据库表结构相对应的C#类。SQLHelper是一个常见的辅助类,用于简化SQL操作,避免了大量的硬编码SQL语句,使得数据库...

    SQL语句查询数据表主键和所有字段

    在数据库管理中,了解如何通过SQL语句查询数据表的主键和所有字段是一项基本而重要的技能。这不仅有助于数据库管理员或开发人员更好地理解数据库结构,还能在进行数据操作、优化查询性能或解决数据完整性问题时提供...

    sqlserver数据库主键

    SQL Server 数据库主键约束详解 在关系型数据库中,主键(Primary Key)是表中的一个或多个字段,它们的值可以唯一地标识表中的每一行记录。主键约束是数据库中的一种约束,它确保了表中的每一行记录的唯一性。 在...

    sql表定时自动清理

    综上所述,通过合理利用 SQL Server 代理及其提供的作业功能,用户可以轻松实现对数据表的定期自动清理,同时结合维护计划功能,还能实现数据备份等其他自动化操作,极大地提高了数据管理的效率和安全性。

    SQL Server数据导入SQLite工具

    2. **数据库结构**:SQL Server与SQLite的表结构可能存在差异,例如字段类型、主键约束、外键约束等。工具需要处理这些差异,确保转换后的SQLite数据库能正确反映原始SQL Server的结构。 3. **数据类型映射**:SQL ...

Global site tag (gtag.js) - Google Analytics