增删可以判断到 在master数据库里创建如下存储过程. 1.操作之前,运行一次该存储过程,取初始状态 2.操作完毕,再次运行该存储过程,即可获取此次数据库的变化状态,如果创建表/删除表,创建数据库/删除数据库,增删表中的记录. set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER proc [dbo].[pCompareTwoStatus] as set nocount on begin /* Author: Vince.Tu */--Step 1: Get the all Database Name if object_id('tempdb..#dbName') is not null drop table #dbNamecreate table #dbName ( dbName varchar(50), dbSize int, dbRes varchar(1000) ) insert into #dbname exec master..sp_databases--Step 2: Record the old info or new info --Notice: temp table with long name declare @oldOrNew varchar(50)if object_id('tempdb..##oldInfo') is null begin create table ##oldInfo ( dbName varchar(50), tbName varchar(8000), rowCnt bigint ) set @oldOrNew = '##oldInfo' select '第一步已执行完毕' endelsebegin create table ##newInfo ( dbName varchar(50), tbName varchar(8000), rowCnt bigint ) set @oldOrNew = '##newInfo' select '第二步已执行完毕' end--Step 3: Using Cursor to Record the old info or new info declare @selectSql varchar(8000) declare @dbName varchar(50) declare curDBName cursor for select dbName from #dbName open curDBName set @selectSql = ' insert into '+ @oldOrNew +' ' fetch next from curDBName into @dbName while @@fetch_status=0 begin set @selectSql = @selectSql + ' select ''' + @dbName + ''' as dbname, b.name as tbname ,a.rowcnt ' set @selectSql = @selectSql + ' from ' + @dbName + '.dbo.sysindexes a join ' + @dbName + '.dbo.sysobjects b on a.id = b.id ' set @selectSql = @selectSql + ' where b.xtype = ''u'' and indid in (0,1) ' fetch next from curDBName into @dbName --Append 'union all', but not in the last row if @@FETCH_STATUS =0 set @selectSql = @selectSql + ' union all 'end close curDBName deallocate curDBNameexec(@selectSql) --print @selectSql --Step 3: Compare the oldInfo and the newInfo, Drop two ##table when the newInfo exists if object_id('tempdb..##newInfo') is not null begin --Step 3.1: --inner join,为增删数据记录的情况,不涉及到增删表及增删数据库 select * from ( select 'select * from '+b.dbname+'..'+b.tbname as selectSql, b.dbname,b.tbname, a.rowcnt as oldrowcnt, b.rowcnt as newrowcnt, status = case when b.rowcnt-a.rowcnt>0 then '+'+convert(varchar(10),b.rowcnt-a.rowcnt) when b.rowcnt-a.rowcnt<0 then '-'+convert(varchar(10),b.rowcnt-a.rowcnt) else 'No Modified' end from ##oldInfo a join ##newInfo b on a.dbname = b.dbname and a.tbname=b.tbname where a.rowcnt<>b.rowcnt union all --Step 3.2: --增删数据库的情况 select 'select * from '+isnull(b.dbname,a.dbname)+'..'+isnull(b.tbname,a.tbname) as selectSql, isnull(b.dbname,a.dbname) as dbname, isnull(b.tbname,a.tbname) as tbname, isnull(a.rowcnt,0) as oldrowcnt, isnull(b.rowcnt,0) as newrowcnt, status = case when a.dbname is null then '+ (Database/Table)' when b.dbname is null then '- (Database/Table)' end from ##oldInfo a full outer join ##newInfo b on a.dbname = b.dbname where a.dbname is null or b.dbname is null union all --Step 3.3: --增删表,在不增删数据的情况下 select 'select * from '+isnull(b.dbname,a.dbname)+'..'+isnull(b.tbname,a.tbname) as selectSql, isnull(b.dbname,a.dbname) as dbname, isnull(b.tbname,a.tbname) as tbname, isnull(a.rowcnt,0) as oldrowcnt, isnull(b.rowcnt,0) as newrowcnt, status = case when a.tbname is null then '+ Table' when b.tbname is null then '- Table' end from ##oldInfo a full join ##newInfo b on a.dbname = b.dbname and a.tbname=b.tbname where isnull(b.dbname,a.dbname) in (select distinct a.dbname from ##oldInfo a join ##newInfo b on a.dbname = b.dbname where a.dbname = b.dbname ) and (a.tbname is null or b.tbname is null) ) aa where aa.dbname <>'tempdb' drop table ##newInfo drop table ##oldInfo end --SP end end
在master数据库里创建如下存储过程.
1.操作之前,运行一次该存储过程,取初始状态
2.操作完毕,再次运行该存储过程,即可获取此次数据库的变化状态,如果创建表/删除表,创建数据库/删除数据库,增删表中的记录.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[pCompareTwoStatus]
as
set nocount on
begin
/*
Author: Vince.Tu
*/--Step 1: Get the all Database Name
if object_id('tempdb..#dbName') is not null
drop table #dbNamecreate table #dbName
(
dbName varchar(50),
dbSize int,
dbRes varchar(1000)
)
insert into #dbname exec master..sp_databases--Step 2: Record the old info or new info
--Notice: temp table with long name
declare @oldOrNew varchar(50)if object_id('tempdb..##oldInfo') is null
begin
create table ##oldInfo
(
dbName varchar(50),
tbName varchar(8000),
rowCnt bigint
) set @oldOrNew = '##oldInfo'
select '第一步已执行完毕'
endelsebegin
create table ##newInfo
(
dbName varchar(50),
tbName varchar(8000),
rowCnt bigint
) set @oldOrNew = '##newInfo'
select '第二步已执行完毕'
end--Step 3: Using Cursor to Record the old info or new info
declare @selectSql varchar(8000)
declare @dbName varchar(50)
declare curDBName cursor for
select dbName from #dbName
open curDBName
set @selectSql = ' insert into '+ @oldOrNew +' '
fetch next from curDBName into @dbName
while @@fetch_status=0
begin
set @selectSql = @selectSql + ' select ''' + @dbName + ''' as dbname, b.name as tbname ,a.rowcnt '
set @selectSql = @selectSql + ' from ' + @dbName + '.dbo.sysindexes a join ' + @dbName + '.dbo.sysobjects b on a.id = b.id '
set @selectSql = @selectSql + ' where b.xtype = ''u'' and indid in (0,1) '
fetch next from curDBName into @dbName
--Append 'union all', but not in the last row
if @@FETCH_STATUS =0
set @selectSql = @selectSql + ' union all 'end
close curDBName
deallocate curDBNameexec(@selectSql)
--print @selectSql
--Step 3: Compare the oldInfo and the newInfo, Drop two ##table when the newInfo exists
if object_id('tempdb..##newInfo') is not null
begin
--Step 3.1:
--inner join,为增删数据记录的情况,不涉及到增删表及增删数据库
select * from
(
select
'select * from '+b.dbname+'..'+b.tbname as selectSql,
b.dbname,b.tbname,
a.rowcnt as oldrowcnt,
b.rowcnt as newrowcnt,
status =
case
when b.rowcnt-a.rowcnt>0 then
'+'+convert(varchar(10),b.rowcnt-a.rowcnt)
when b.rowcnt-a.rowcnt<0 then
'-'+convert(varchar(10),b.rowcnt-a.rowcnt)
else
'No Modified'
end
from ##oldInfo a join ##newInfo b on a.dbname = b.dbname and a.tbname=b.tbname
where a.rowcnt<>b.rowcnt
union all --Step 3.2:
--增删数据库的情况
select
'select * from '+isnull(b.dbname,a.dbname)+'..'+isnull(b.tbname,a.tbname) as selectSql,
isnull(b.dbname,a.dbname) as dbname,
isnull(b.tbname,a.tbname) as tbname,
isnull(a.rowcnt,0) as oldrowcnt,
isnull(b.rowcnt,0) as newrowcnt,
status =
case
when a.dbname is null then
'+ (Database/Table)'
when b.dbname is null then
'- (Database/Table)'
end
from ##oldInfo a full outer join ##newInfo b on a.dbname = b.dbname
where a.dbname is null or b.dbname is null
union all --Step 3.3:
--增删表,在不增删数据的情况下
select
'select * from '+isnull(b.dbname,a.dbname)+'..'+isnull(b.tbname,a.tbname) as selectSql,
isnull(b.dbname,a.dbname) as dbname,
isnull(b.tbname,a.tbname) as tbname,
isnull(a.rowcnt,0) as oldrowcnt,
isnull(b.rowcnt,0) as newrowcnt,
status =
case
when a.tbname is null then
'+ Table'
when b.tbname is null then
'- Table'
end
from ##oldInfo a full join ##newInfo b on a.dbname = b.dbname and a.tbname=b.tbname
where
isnull(b.dbname,a.dbname) in
(select distinct a.dbname from ##oldInfo a join ##newInfo b on a.dbname = b.dbname where a.dbname = b.dbname )
and
(a.tbname is null or b.tbname is null)
) aa
where aa.dbname <>'tempdb'
drop table ##newInfo
drop table ##oldInfo
end --SP end
end