create table #t(req_spid int,obj_name sysname) declare @s nvarchar(4000) ,@rid int,@dbname sysname,@id int,@objname sysname declare tb cursor for select distinct req_spid,dbname=db_name(rsc_dbid),rsc_objid from master..syslockinfo where rsc_type in(4,5) open tb fetch next from tb into @rid,@dbname,@id while @@fetch_status=0 begin set @s='select @objname=name from ['+@dbname+']..sysobjects where id=@id' exec sp_executesql @s,N'@objname sysname out,@id int',@objname out,@id insert into #t values(@rid,@objname) fetch next from tb into @rid,@dbname,@id end close tb deallocate tb select 进程id=a.req_spid ,数据库=db_name(rsc_dbid) ,类型=case rsc_type when 1 then 'NULL 资源(未使用)' when 2 then '数据库' when 3 then '文件' when 4 then '索引' when 5 then '表' when 6 then '页' when 7 then '键' when 8 then '扩展盘区' when 9 then 'RID(行 ID)' when 10 then '应用程序' end ,对象id=rsc_objid ,对象名=b.obj_name ,rsc_indid from master..syslockinfo a left join #t b on a.req_spid=b.req_spid go drop table #t 2.杀掉相应数据库的进程代码来源:http://www.cnblogs.com/LCX/archive/2008/12/03/1346924.html Create Proc Sp_KillAllProcessInDB @DbName VarChar(100)asif db_id(@DbName) = Null beginPrint 'DataBase dose not Exist'endelse BeginDeclare @spId Varchar(30) DECLARE TmpCursor CURSOR FORSelect 'Kill ' + convert(Varchar, spid) as spIdfrom master..SysProcesseswhere db_Name(dbID) = @DbNameand spId <> @@SpIdand dbID <> 0OPEN TmpCursor FETCH NEXT FROM TmpCursorINTO @spId WHILE @@FETCH_STATUS = 0 BEGIN Exec (@spId) FETCH NEXT FROM TmpCursorINTO @spId END CLOSE TmpCursorDEALLOCATE TmpCursor end GO--To ExecuteExec dbo.Sp_KillAllProcessInDB 'DBname' 然后就可以刷新数据库的表与存储过程了。
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货