怎么用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