SQL server学习——两表对照
自己闲来无事的时候就写了一个存储过程，用来对比两张表的相同性。
1、这个存储过程主要是用来查看数据量大的前提下，在不知道删除了什么数据，想要恢复.
2、这个存储过程不考虑表中的数据是否有唯一id，没有id一样行的通
3、表中的列一般情况下不能少于两列，少于两列的没有测试可用性
4、那个恢复数据功能有一个小问题，还没解决掉
/**
*对比两张表的不同
*
**/
alter proc sys_tableVsfit
@tables_former varchar(1000),--原数据表
@tables_Delete varchar(1000),--删除数据表
@tables_Restore varchar(1000)='0' --默认不还原
as
begin
--去两边的空格
set @tables_former=ltrim(rtrim(@tables_former))
set @tables_Delete=ltrim(rtrim(@tables_Delete))
declare @table_nameFormer varchar(1000),
--建立所用到的变量
@table_nameDelete varchar(1000),@name varchar(1000),
@num varchar(1000),@tb varchar(1000),@addtb varchar(1000),
@tb_Formerid int,@table_Deleteid int,@tb_FormerCount int,@indexNo varchar(1000),
@table_DeleteCount int,@Formerlie varchar(1000)='',@Deletelie varchar(1000)=''
--过渡表
create table #tb_Former(
varid int primary key identity(1,1)
)
--真实数据表
create table #tb_FormerReal(
varid int primary key identity(1,1)
)
--删除表id
create table #tb_Delete(
varid int primary key identity(1,1)
)
--删除的数据
create table #tb_DeleteReal(
varid int primary key
)
--查询表中是否有传过来的表名
select @table_nameFormer=name,@tb_Formerid=id from sysobjects where name=@tables_former
select @table_nameDelete=name,@table_Deleteid=id from sysobjects where name=@tables_Delete
--根据表名id查询列名，是否数目相同
select @tb_FormerCount=count(name) from syscolumns where id=@tb_Formerid
select @table_DeleteCount=count(name) from syscolumns where id=@table_Deleteid
if isnull(@table_nameFormer,null)=null
begin
print '输入的原数据表名为空'
return
end
if isnull(@table_nameDelete,null)=null
begin
print '输入的删除数据表名为空'
return
end
if @tb_FormerCount<>@table_DeleteCount
begin
print '两表不相同，请核对'
return
end
---添加原数据据列，查询真正数据列名
declare sys_name cursor
for(
select name from syscolumns where id=@tb_Formerid)
open sys_name
--循环下一行游标
fetch next from sys_name into @name
while @@FETCH_STATUS=0
begin
--向临时表中添加真是数据库列名
set @addtb='alter table #tb_Former add ' +@name+ ' varchar(1000)'
--print @addtb
exec(@addtb)
set @addtb='alter table #tb_DeleteReal add ' +@name+ ' varchar(1000)'
--print @addtb
exec(@addtb)
--获取列名为后面进行比较
set @Formerlie += @name
--print @name
fetch next from sys_name into @name
end
--打印表1里面的列
--print @formerlie
close sys_name
deallocate sys_name
---查询删除数据列名
declare sys_name2 cursor
for(
select name from syscolumns where id=@table_Deleteid)
open sys_name2
--循环下一行游标
fetch next from sys_name2 into @name
while @@FETCH_STATUS=0
begin
--删除表数据、真实数据表 用作varid比较
set @addtb='alter table #tb_Delete add ' +@name+ ' varchar(1000)'
exec(@addtb)
set @addtb='alter table #tb_FormerReal add ' +@name+ ' varchar(1000)'
exec(@addtb)
--同理获取列名用作比较
set @Deletelie += @name
--print @name
fetch next from sys_name2 into @name
end
--打印表2里面的列
--print @Deletelie
close sys_name2
deallocate sys_name2
--把真实数据放到临时表里面
set @addtb='insert #tb_FormerReal select * from '+ @table_nameFormer
exec(@addtb)
--判断列名是否相同,(不区分大小写，需求可以改)，判断的是列名
set @indexNo = isnull(nullif(@Formerlie,@Deletelie),'NO')
if @indexNo<>'NO'
begin
print '两表不相同，请核对'
return
end
--添加过度列，作用是为了左连接判断删除数据的空值null
declare @l1 varchar(1000),@l2 varchar(1000),@l3 varchar(1000),@l4 varchar(1000),
@v1 nvarchar(1000)='',@v2 nvarchar(1000)='',@v3 nvarchar(1000)='',@v4 nvarchar(1000)='',
@numl int,@numn varchar(1000)='1',@insersql nvarchar(1000)
--设置一个计时器初始值为零
set @numl=0
declare sys_name1 cursor
for(
select name from syscolumns where id=@tb_Formerid)
open sys_name1
--循环下一行游标
fetch next from sys_name1 into @name
while @@FETCH_STATUS=0
begin
set @addtb='alter table #tb_Former add ' +@name+ '1 varchar(1000)'
--print @addtb
exec(@addtb)
--把列名提取出来
if @numl=1
begin
if isnull(@name,'1')<>'1'
begin
set @l1 = @name+@numn
set @v1 = @name
end
else set @l1='*'
end
else if @numl=2
begin
if isnull(@name,'1')<>'1'
begin
set @l2 = @name+'1'
set @v2 = @name
end
else set @l2='*'
end
else if @numl=3
begin
if isnull(@name,'1')<>'1'
begin
set @l3 = @name+@numn
set @v3 = @name
end
else set @l3='*'
end
--第一列一般都是id，但是考虑到没有id的情况，添加多个列名
else if @numl=0
begin
if isnull(@name,'1')<>'1'
begin
set @l4 = @name+@numn
set @v4 = @name
end
else set @l4='*'
end
--把计时器相加
set @numl+=1
fetch next from sys_name1 into @name
end
close sys_name1
deallocate sys_name1
--把两个表的值添加进去
--insert into #tb_Former select * from staff st left join staff1 sf on st.id=sf.id
--列名只有大于3列才有意义，小于3列一般都是id加值，所以直接确定id就可以了
set @insersql=
'insert into #tb_Former select * from '+@table_nameFormer+' st left join '+@table_nameDelete+' sf
on '+case when @tb_FormerCount>=3 then' st.'+@v4+'=sf.'+@v4+' and st.'+@v1 +'=sf.'+@v1+' and st.'+@v2+'=sf.'+@v2
else ' st.'+@v4+'=sf.'+@v4 end
exec(@insersql)
--循环查询里面的数据
declare @tb_count int, @n1 Nvarchar(1000),@n2 Nvarchar(1000),@n3 Nvarchar(1000),@n4 Nvarchar(1000),
--设置默认没有被删除数据
@sql Nvarchar(1000),@varid int,@index1 varchar(10)='false'
--获取里面值的总数
select @tb_count=count(varid) from #tb_Former
--往删除表里添加删除的id，主要是为了把删除的那些值的varid存进去为以后连表查询做准备
alter table #tb_Delete add Delete_id int
while @tb_count >0
begin
--查询删除表的数据，为空赋值为'false'
set @sql='select @varid=varid,'+case when @tb_FormerCount>=3 then '@n1=isnull(cast('+@l1+' as varchar),''false''),@n2=isnull(cast('+@l2+' as varchar),''false''),
@n3=isnull(cast('+@l3+' as varchar),''false'')'
else '@n1=isnull(cast('+@l4+' as varchar),''false''),@n2=isnull(cast('+@l1+' as varchar),''false'')' end +' from #tb_Former where varid=@tb_count'
--这个执行过程是为了把变量里面的值拿出来
exec sp_executesql @sql,N'@n1 varchar(1000) out,@n2 varchar(1000) out,@n3 varchar(1000) out,@varid int out,@tb_count int',@n1 out,@n2 out,@n3 out,@varid out,@tb_count
--都等于false说明是删除的数据，前面的三个空值就可以判定它是删除数据了，
--前面的三个值是空的，有几率 但是基本不可能，前面的数据一般都是有值的
if (@n1='false'and @n2='false' and @n3='false')
begin
--把查出来删除的varid放到事先准备好的Delete_id里面
insert into #tb_Delete(Delete_id)
select varid from #tb_Former where varid=@varid
--进来就说明里面有删除的数据
set @index1='true'
end
set @tb_count -= 1
end
--查询被删除的值
if @index1<>'true'
begin
print '抱歉，你没有删除任何数据'
end
if @index1='true'
begin
--把删除的varid和真是数据varid关联查询出被删的数据，添加到删除数据的临时表中
insert into #tb_DeleteReal
select * from #tb_FormerReal st where st.varid in(
select sf.Delete_id from #tb_Delete sf
)
--查询被删的数据
set @sql='select * from #tb_DeleteReal'
exec(@sql)
end
--还原数据(bug)暂时不可用
/*if @tables_Restore='1' or @tables_Restore like'%还原%'
begin
--打开标识列
set @sql='SET IDENTITY_INSERT '+@table_nameDelete+' ON'
exec(@sql)
set @sql ='insert into '+ @table_nameDelete+' select * from #tb_FormerReal st where st.varid in(select sf.Delete_id from #tb_Delete sf)'
exec(@sql)
--关闭标识列
set @sql='SET IDENTITY_INSERT '+@table_nameDelete+' OFF'
exec(@sql)
end*/
end
/**
*对比两张表的不同
*
**/
alter proc sys_tableVsfit
@tables_former varchar(1000),--原数据表
@tables_Delete varchar(1000),--删除数据表
@tables_Restore varchar(1000)='0' --默认不还原
as
begin
--去两边的空格
set @tables_former=ltrim(rtrim(@tables_former))
set @tables_Delete=ltrim(rtrim(@tables_Delete))
declare @table_nameFormer varchar(1000),
--建立所用到的变量
@table_nameDelete varchar(1000),@name varchar(1000),
@num varchar(1000),@tb varchar(1000),@addtb varchar(1000),
@tb_Formerid int,@table_Deleteid int,@tb_FormerCount int,@indexNo varchar(1000),
@table_DeleteCount int,@Formerlie varchar(1000)='',@Deletelie varchar(1000)=''
--过渡表
create table #tb_Former(
varid int primary key identity(1,1)
)
--真实数据表
create table #tb_FormerReal(
varid int primary key identity(1,1)
)
--删除表id
create table #tb_Delete(
varid int primary key identity(1,1)
)
--删除的数据
create table #tb_DeleteReal(
varid int primary key
)
--查询表中是否有传过来的表名
select @table_nameFormer=name,@tb_Formerid=id from sysobjects where name=@tables_former
select @table_nameDelete=name,@table_Deleteid=id from sysobjects where name=@tables_Delete
--根据表名id查询列名，是否数目相同
select @tb_FormerCount=count(name) from syscolumns where id=@tb_Formerid
select @table_DeleteCount=count(name) from syscolumns where id=@table_Deleteid
if isnull(@table_nameFormer,null)=null
begin
print '输入的原数据表名为空'
return
end
if isnull(@table_nameDelete,null)=null
begin
print '输入的删除数据表名为空'
return
end
if @tb_FormerCount<>@table_DeleteCount
begin
print '两表不相同，请核对'
return
end
---添加原数据据列，查询真正数据列名
declare sys_name cursor
for(
select name from syscolumns where id=@tb_Formerid)
open sys_name
--循环下一行游标
fetch next from sys_name into @name
while @@FETCH_STATUS=0
begin
--向临时表中添加真是数据库列名
set @addtb='alter table #tb_Former add ' +@name+ ' varchar(1000)'
--print @addtb
exec(@addtb)
set @addtb='alter table #tb_DeleteReal add ' +@name+ ' varchar(1000)'
--print @addtb
exec(@addtb)
--获取列名为后面进行比较
set @Formerlie += @name
--print @name
fetch next from sys_name into @name
end
--打印表1里面的列
--print @formerlie
close sys_name
deallocate sys_name
---查询删除数据列名
declare sys_name2 cursor
for(
select name from syscolumns where id=@table_Deleteid)
open sys_name2
--循环下一行游标
fetch next from sys_name2 into @name
while @@FETCH_STATUS=0
begin
--删除表数据、真实数据表 用作varid比较
set @addtb='alter table #tb_Delete add ' +@name+ ' varchar(1000)'
exec(@addtb)
set @addtb='alter table #tb_FormerReal add ' +@name+ ' varchar(1000)'
exec(@addtb)
--同理获取列名用作比较
set @Deletelie += @name
--print @name
fetch next from sys_name2 into @name
end
--打印表2里面的列
--print @Deletelie
close sys_name2
deallocate sys_name2
--把真实数据放到临时表里面
set @addtb='insert #tb_FormerReal select * from '+ @table_nameFormer
exec(@addtb)
--判断列名是否相同,(不区分大小写，需求可以改)，判断的是列名
set @indexNo = isnull(nullif(@Formerlie,@Deletelie),'NO')
if @indexNo<>'NO'
begin
print '两表不相同，请核对'
return
end
--添加过度列，作用是为了左连接判断删除数据的空值null
declare @l1 varchar(1000),@l2 varchar(1000),@l3 varchar(1000),@l4 varchar(1000),
@v1 nvarchar(1000)='',@v2 nvarchar(1000)='',@v3 nvarchar(1000)='',@v4 nvarchar(1000)='',
@numl int,@numn varchar(1000)='1',@insersql nvarchar(1000)
--设置一个计时器初始值为零
set @numl=0
declare sys_name1 cursor
for(
select name from syscolumns where id=@tb_Formerid)
open sys_name1
--循环下一行游标
fetch next from sys_name1 into @name
while @@FETCH_STATUS=0
begin
set @addtb='alter table #tb_Former add ' +@name+ '1 varchar(1000)'
--print @addtb
exec(@addtb)
--把列名提取出来
if @numl=1
begin
if isnull(@name,'1')<>'1'
begin
set @l1 = @name+@numn
set @v1 = @name
end
else set @l1='*'
end
else if @numl=2
begin
if isnull(@name,'1')<>'1'
begin
set @l2 = @name+'1'
set @v2 = @name
end
else set @l2='*'
end
else if @numl=3
begin
if isnull(@name,'1')<>'1'
begin
set @l3 = @name+@numn
set @v3 = @name
end
else set @l3='*'
end
--第一列一般都是id，但是考虑到没有id的情况，添加多个列名
else if @numl=0
begin
if isnull(@name,'1')<>'1'
begin
set @l4 = @name+@numn
set @v4 = @name
end
else set @l4='*'
end
--把计时器相加
set @numl+=1
fetch next from sys_name1 into @name
end
close sys_name1
deallocate sys_name1
--把两个表的值添加进去
--insert into #tb_Former select * from staff st left join staff1 sf on st.id=sf.id
--列名只有大于3列才有意义，小于3列一般都是id加值，所以直接确定id就可以了
set @insersql=
'insert into #tb_Former select * from '+@table_nameFormer+' st left join '+@table_nameDelete+' sf
on '+case when @tb_FormerCount>=3 then' st.'+@v4+'=sf.'+@v4+' and st.'+@v1 +'=sf.'+@v1+' and st.'+@v2+'=sf.'+@v2
else ' st.'+@v4+'=sf.'+@v4 end
exec(@insersql)
--循环查询里面的数据
declare @tb_count int, @n1 Nvarchar(1000),@n2 Nvarchar(1000),@n3 Nvarchar(1000),@n4 Nvarchar(1000),
--设置默认没有被删除数据
@sql Nvarchar(1000),@varid int,@index1 varchar(10)='false'
--获取里面值的总数
select @tb_count=count(varid) from #tb_Former
--往删除表里添加删除的id，主要是为了把删除的那些值的varid存进去为以后连表查询做准备
alter table #tb_Delete add Delete_id int
while @tb_count >0
begin
--查询删除表的数据，为空赋值为'false'
set @sql='select @varid=varid,'+case when @tb_FormerCount>=3 then '@n1=isnull(cast('+@l1+' as varchar),''false''),@n2=isnull(cast('+@l2+' as varchar),''false''),
@n3=isnull(cast('+@l3+' as varchar),''false'')'
else '@n1=isnull(cast('+@l4+' as varchar),''false''),@n2=isnull(cast('+@l1+' as varchar),''false'')' end +' from #tb_Former where varid=@tb_count'
--这个执行过程是为了把变量里面的值拿出来
exec sp_executesql @sql,N'@n1 varchar(1000) out,@n2 varchar(1000) out,@n3 varchar(1000) out,@varid int out,@tb_count int',@n1 out,@n2 out,@n3 out,@varid out,@tb_count
--都等于false说明是删除的数据，前面的三个空值就可以判定它是删除数据了，
--前面的三个值是空的，有几率 但是基本不可能，前面的数据一般都是有值的
if (@n1='false'and @n2='false' and @n3='false')
begin
--把查出来删除的varid放到事先准备好的Delete_id里面
insert into #tb_Delete(Delete_id)
select varid from #tb_Former where varid=@varid
--进来就说明里面有删除的数据
set @index1='true'
end
set @tb_count -= 1
end
--查询被删除的值
if @index1<>'true'
begin
print '抱歉，你没有删除任何数据'
end
if @index1='true'
begin
--把删除的varid和真是数据varid关联查询出被删的数据，添加到删除数据的临时表中
insert into #tb_DeleteReal
select * from #tb_FormerReal st where st.varid in(
select sf.Delete_id from #tb_Delete sf
)
--查询被删的数据
set @sql='select * from #tb_DeleteReal'
exec(@sql)
end
--还原数据(bug)暂时不可用
/*if @tables_Restore='1' or @tables_Restore like'%还原%'
begin
--打开标识列
set @sql='SET IDENTITY_INSERT '+@table_nameDelete+' ON'
exec(@sql)
set @sql ='insert into '+ @table_nameDelete+' select * from #tb_FormerReal st where st.varid in(select sf.Delete_id from #tb_Delete sf)'
exec(@sql)
--关闭标识列
set @sql='SET IDENTITY_INSERT '+@table_nameDelete+' OFF'
exec(@sql)
end*/
end在这里注释就不写了，写的可能有点乱
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
