Alter DATABASE ATE SET ENABLE_BROKER

select databasepropertyex('ATE','isbrokerenabled')

------------------------------------------------------------------------------------------------------------------------------

--开启xp_cmdshell部分
--------------------------------------------------
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO


--通过xp_cmdshell执行shell命令的部分
--------------------------------------------------
Exec xp_cmdshell 'bcp '
GO


--关闭xp_cmdshell部分
-----------------------------------------------------
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 0
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO


------------------------------------------------------------------------------------------------------------------------------


if object_id('p_getlinkinfo','P')is not null drop proc p_getlinkinfo
go
create proc p_getlinkinfo   
@dbname sysname=null, --要查詢的數據庫名,默認表示所有  
@includeip bit=0     --是否顯示IP信息  
as   
  begin
    declare @dbid int   
    set @dbid=db_id(@dbname)
    if object_id('tempdb..#tb')is not null drop table #tb
    if object_id('tempdb..#ip')is not null drop table #ip    
    create table #tb
       (id int identity(1,1),
        dbname sysname,
        hostname nchar(128),
        loginname nchar(128),
        net_address nchar(12),
        net_ip nvarchar(15),
        prog_name   nchar(128))   
    insert into #tb(hostname,dbname,net_address,loginname,prog_name)   
    select distinct hostname,
         db_name(dbid),
         net_address,
         loginame,
         program_name
    from master..sysprocesses   
    where hostname!=''and(@dbid is null or dbid=@dbid)   
    if @includeip=0 goto lb_show --不顯示IP   
    declare @sql varchar(500),@hostname nchar(128),@id int   
    create table #ip(hostname nchar(128),a varchar(200))   
    declare tb cursor local for select distinct hostname from #tb   
    open tb   
    fetch next from tb into @hostname   
    while @@fetch_status=0   
    begin   
     set @sql='ping   '+@hostname+'   -a   -n   1   -l   1'   
     insert #ip(a) exec master..xp_cmdshell @sql   
     update #ip    set  hostname=@hostname where hostname is null   
     fetch next from tb into @hostname   
    end   
    update #tb set net_ip=left(a,patindex('%:%',a)-1)   
    from #tb a inner join 
    (select hostname,a=substring(a,patindex('Ping statistics for %:%',a)+20,20) 
    from #ip   
    where a like'Ping statistics for %:%')b
    on a.hostname=b.hostname   
lb_show:   
    select   id,
           dbname,
           hostname,
           loginname,
           net_address,
           net_ip,
           prog_name   
    from #tb   
  end  
  go     
exec p_getlinkinfo @dbname='ATE',@includeip=1


------------------------------------------------------------------------------------------------------------------------------


if object_id('sp_who_lock','P')is not null drop proc sp_who_lock
go
CREATE procedure sp_who_lock    
as      
begin      
   declare @spid int      
   declare @blk int      
   declare @count int      
   declare @index int      
   declare @lock tinyint       
   set @lock=0       
   create table #temp_who_lock       
 (       
  id int identity(1,1),       
  spid int,       
  blk int      
 )       
 if @@error<>0 return @@error       
 insert into #temp_who_lock(spid,blk)       
 select 0 ,blocked        
 from (select * from master..sysprocesses where blocked>0)a       
 where not exists(select * from  master..sysprocesses where a.blocked =spid and blocked>0)       
 union select spid,blocked from  master..sysprocesses where blocked>0       
 if @@error<>0 return @@error       
 select @count=count(*),@index=1 from #temp_who_lock       
 if @@error<>0 return @@error       
 if @count=0       
 begin      
  select '没有阻塞和死锁信息'      
  return 0       
 end      
 while @index<=@count       
 begin      
  if exists(select 1 from #temp_who_lock a where id>@index and exists(select 1 from #temp_who_lock where id<=@index and a.blk=spid))       
  begin      
   set @lock=1       
   select @spid=spid,@blk=blk from #temp_who_lock where id=@index      
   select '引起数据库死锁的是: '+ CAST(@spid AS VARCHAR(10)) + '进程号,其执行的SQL语法如下'      
   select  @spid, @blk     
   dbcc inputbuffer(@spid)       
   dbcc inputbuffer(@blk)       
  end      
  set @index=@index+1       
 end      
 if @lock=0        
 begin      
  set @index=1       
  while @index<=@count       
  begin      
   select @spid=spid,@blk=blk from #temp_who_lock where id=@index      
   if @spid=0       
    select '引起阻塞的是:'+cast(@blk as varchar(10))+ '进程号,其执行的SQL语法如下'      
   else       
    select '进程号SPID：'+ CAST(@spid AS VARCHAR(10))+ '被' + '进程号SPID：'+ CAST(@blk AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下'      
   dbcc inputbuffer(@spid)     
   dbcc inputbuffer(@blk)       
   set @index=@index+1       
  end      
 end      
 drop table #temp_who_lock       
 return 0       
end 
GO

EXEC sp_who_lock  
--kill


------------------------------------------------------------------------------------------------------------------------------