怎么用sql语句判断一个表正在进行操作，比如增、删、改
怎么用sql语句判断一个表正在进行操作，比如增、删、改
[解决办法]
增、删、改的时候都会有锁
判断这个当前有没有锁就能知道表是不是正在操作
Sql2000用 master.dbo.syslockinfo + master.dbo.spt_values 查看
Sql2005用动态管理视图 sys.dm_tran_locks 查看
[解决办法]
都是瞬间的，很难去判断，个人观点！
[解决办法]
很简单啊，你只要创建插入、删除、更新操作所对应的触发器就可以了。
[解决办法]
增删可以判断到
在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 #dbName
create table #dbName
(
dbName varchar(50),
dbSize int,
dbRemarks 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 '第一步已执行完毕 '
end
else
begin
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 curDBName
exec(@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
[解决办法]
以上ALTER改为Create
