系列文章
C#底层库--SQLBuilder脚本构建类(select、insert、update、in、带条件的SQL自动生成)
本文链接:[https://blog.csdn.net/youcheng_ge/article/details/129179216](https://blog.csdn.net/youcheng_ge/article/details/129179216)
C#底层库--MySQL数据库操作辅助类(推荐阅读)
本文链接:[https://blog.csdn.net/youcheng_ge/article/details/126886379](https://blog.csdn.net/youcheng_ge/article/details/126886379)
C#底层库--SQLite的使用(小型、本地数据库)
本文链接:[https://blog.csdn.net/youcheng_ge/article/details/123666958](https://blog.csdn.net/youcheng_ge/article/details/123666958)
【提高编程效率】Excel数据批量导入数据库
本文链接:[https://blog.csdn.net/youcheng_ge/article/details/126427323](https://blog.csdn.net/youcheng_ge/article/details/126427323)
【Oracle】数据库服务器限制ip访问
本文链接:[https://blog.csdn.net/youcheng_ge/article/details/122220930](https://blog.csdn.net/youcheng_ge/article/details/122220930)
【Oracle】Excel导入数据教程
本文链接:[https://blog.csdn.net/youcheng_ge/article/details/118722756](https://blog.csdn.net/youcheng_ge/article/details/118722756)
【Oracle】数据库还原教程_数据泵
本文链接:[https://blog.csdn.net/youcheng_ge/article/details/118054855](https://blog.csdn.net/youcheng_ge/article/details/118054855)
【SQL】如何查询表字段并识别主键
本文链接:[https://blog.csdn.net/youcheng_ge/article/details/110820405](https://blog.csdn.net/youcheng_ge/article/details/110820405)
【SQL】outer apply的用法
本文链接:[https://blog.csdn.net/youcheng_ge/article/details/79903489](https://blog.csdn.net/youcheng_ge/article/details/79903489)
【SQL】多表连接重复数据处理
本文链接:[https://blog.csdn.net/youcheng_ge/article/details/79903619](https://blog.csdn.net/youcheng_ge/article/details/79903619)
MySQL安装教程(详细)
本文链接:[https://blog.csdn.net/youcheng_ge/article/details/126037520](https://blog.csdn.net/youcheng_ge/article/details/126037520)
MySQL卸载教程(详细)
本文链接:[https://blog.csdn.net/youcheng_ge/article/details/129279265](https://blog.csdn.net/youcheng_ge/article/details/129279265)
【MySQL】group by分类汇总,如何增加“总计”字段?
本文链接:[https://blog.csdn.net/youcheng_ge/article/details/128217837](https://blog.csdn.net/youcheng_ge/article/details/128217837)
【MySQL】WITH CHECK OPTION的用法
本文链接:[https://blog.csdn.net/youcheng_ge/article/details/128147196](https://blog.csdn.net/youcheng_ge/article/details/128147196)
【MySQL】使用存储过程插入千万级数据如何提升效率?
本文链接:[https://blog.csdn.net/youcheng_ge/article/details/77728189](https://blog.csdn.net/youcheng_ge/article/details/77728189)
【MySQL】数据库表行列转置的实现
本文链接:[https://blog.csdn.net/youcheng_ge/article/details/77625052](https://blog.csdn.net/youcheng_ge/article/details/77625052)
【MySQL】查询中,NULL值转换为空字符串
本文链接:[https://blog.csdn.net/youcheng_ge/article/details/130381878](https://blog.csdn.net/youcheng_ge/article/details/130381878)
前言
本专栏为【数据库】,主要介绍SQL的功能与特点、SQL数据定义语言(表、视图、索引、约束)、SQL数据操作语言(数据检索、数据插入、数据删除、数据更新)、创建与删除触发器、SQL数据控制语言(安全性和授权、事务处理)以及嵌入式SQL。
如果你对本专辑感兴趣,持续关注吧。大家有任何问题,也可以评论区反馈,私信我。
一、技术介绍
任务单--数据库变更审核
二、解决方案
/*
功能:任务单--数据库变更审核
创建人:gyc
创建时间:2020-11-05
备注:进入系统 922
PSysAlterIndex
*/
CREATE PROCEDURE PSysAlterIndex(@strTable varchar(40),@strIndexName varchar(100),@IsCluster int=0,
@Field1 varchar(1000),@Field2 varchar(50)='',@Field3 varchar(50)='',
@Field4 varchar(50)='',@Field5 varchar(50)='',@Field6 varchar(50)='')
AS
--删除原主键
declare @Index as varchar(100)
declare @IndexDrop as varchar(100)
declare @s_DropSQl as varchar(1000)
declare @NewIndexSQL as varchar(2000)
declare @OldIndex as varchar(1000)
declare @NewIndex as varchar(1000)
declare @Cluster as varchar(400)
Declare @OldIsClusted as int
Declare @IsReCreate as int
Select @OldIndex='',@NewIndex='',@IsReCreate=0
--1 获取原先的索引名称 和 索引属性(是否聚集索引)
SELECT @Index=si.name,@OldIsClusted=si.indid
FROM sysindexes si
Where object_name(si.id)=@strTable and (si.name=@strIndexName)
--2 获取原先索引的字段
SELECT @OldIndex=@OldIndex+','+sc.name
FROM sysindexes si
Inner Join sysindexkeys sik On si.indid=sik.indid and si.id=sik.id
Inner Join syscolumns sc On sik.colid=sc.colid and si.id=sc.id
Where object_name(si.id)=@strTable and (si.name=@strIndexName)
Order By sc.colid
--3 获取新索引的字段
Declare @Fields table(field varchar(100),id int)
Insert @Fields(field,id)
Select @Field1,1
Union All Select @Field2,2
Union All Select @Field3,3
Union All Select @Field4,4
Union All Select @Field5,5
Union All Select @Field6,6
Select @NewIndex=@NewIndex+','+field
From @Fields
Where Rtrim(field)>''
Order By id
--4 判定重建索引时是否 为聚集索引
Select @Cluster=' NONCLUSTERED '
if @IsCluster=1 Set @Cluster=' CLUSTERED '
--5 判定原先的索引为聚集索引,后面重建时需要根据此判断
if IsNull(@OldIsClusted,0)<>1 Select @OldIsClusted=0
--5.1 是否使用Drop_Existing重建索引,取决于原先索引是否存在
Select @IndexDrop=''
if @Index is not null Select @IndexDrop=' WITH DROP_EXISTING '
--5.2 如果变聚集索引为非聚集索引则 删除原先的索引重建
if @OldIsClusted=1 and @IsCluster=0
Begin
Exec(' Drop Index '+@strTable+'.'+@Index)
Select @IndexDrop=''
end
--6 开始判断是否需要建索引
if @NewIndex>'' Select @NewIndex=SubString(@NewIndex,2,1000)
if @OldIndex>'' Select @OldIndex=SubString(@OldIndex,2,1000)
-- 聚集标记,索引名称 不一致时,才需要
if (@OldIsClusted<>@IsCluster Or @OldIndex<>@NewIndex)
Begin
Select @IsReCreate=1
End
--7 如果需要把原先的非聚集索引改为聚集索引,则要把原先存在的聚集索引改为非聚集索引
Declare @ClusterIndex as varchar(40),@ClusterIndexfield as varchar(1000),@PkName as varchar(100)
Select @ClusterIndexfield=''
SELECT @ClusterIndex=si.name,@ClusterIndexfield=@ClusterIndexfield+','+sc.name
FROM sysindexes si
Inner Join sysindexkeys sik On si.indid=sik.indid and si.id=sik.id
Inner Join syscolumns sc On sik.colid=sc.colid and si.id=sc.id
Where object_name(si.id)=@strTable and si.indid=1
Order By sc.colid
Select @PkName=name
From sysobjects
Where xtype='PK' and object_name(parent_obj) =@strTable
Select @ClusterIndexfield=SubString(@ClusterIndexfield,2,1000)
if (@IsCluster=1 and @ClusterIndex is not null and @ClusterIndex<>@strIndexName) and (@PkName<>@ClusterIndex)
exec('PSysAlterIndex '''+@strTable+''','''+@ClusterIndex+''',0,'''+@ClusterIndexfield+'''')
---8 开始重建索引
if @NewIndex>'' and IsNull(@PkName,'')<>@strIndexName and @IsReCreate=1
Begin
Select @NewIndexSQL='CREATE '+@Cluster +' INDEX ['+@strIndexName+'] ON '+@strTable+Char(13)+Char(10)
+ '('+@NewIndex+') ' +Char(13)+Char(10)
+@IndexDrop +' ON [PRIMARY] '
Exec(@NewIndexSQL)
print @NewIndexSQL
End