停止数据库的用户连接代码,MSSQL代码库,德仔网

代码语言
代码分类
【MSSQL】停止数据库的用户连接
作者:Dezai.CN / 发布于2011/6/30/ 603
<div><span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)"><strong>停止数据库的用户连接</strong></span><span style="color: rgb(0,128,128)"> 
</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,0,255)">create</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">proc</span><span style="color: rgb(0,0,0)"> killspid (</span><span style="color: rgb(0,128,0)">@dbname</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">varchar</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(128,0,0); font-weight: bold">50</span><span style="color: rgb(0,0,0)">)) 
</span><span style="color: rgb(0,0,255)">as</span><span style="color: rgb(0,0,0)"> 
</span><span style="color: rgb(0,0,255)">declare</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,128,0)">@sql</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">nvarchar</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(128,0,0); font-weight: bold">1000</span><span style="color: rgb(0,0,0)">), </span><span style="color: rgb(0,128,0)">@spid</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">int</span><span style="color: rgb(0,0,0)"> 
</span><span style="color: rgb(0,0,255)">declare</span><span style="color: rgb(0,0,0)"> getspid </span><span style="color: rgb(0,0,255)">cursor</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">for</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">select</span><span style="color: rgb(0,0,0)"> spid </span><span style="color: rgb(0,0,255)">from</span><span style="color: rgb(0,0,0)"> sysprocesses 
</span><span style="color: rgb(0,0,255)">where</span><span style="color: rgb(0,0,0)"> dbid</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,255)">db_id</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(0,128,0)">@dbname</span><span style="color: rgb(0,0,0)">) 
</span><span style="color: rgb(0,0,255)">open</span><span style="color: rgb(0,0,0)"> getspid 
</span><span style="color: rgb(0,0,255)">fetch</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">next</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">from</span><span style="color: rgb(0,0,0)"> getspid </span><span style="color: rgb(0,0,255)">into</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,128,0)">@spid</span><span style="color: rgb(0,0,0)"> 
</span><span style="color: rgb(0,0,255)">while</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,128,0); font-weight: bold">@@fetch_status</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(128,0,0); font-weight: bold">0</span><span style="color: rgb(0,0,0)"> 
</span><span style="color: rgb(0,0,255)">begin</span><span style="color: rgb(0,0,0)"> 
</span><span style="color: rgb(0,0,255)">exec</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">kill </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(128,128,128)">+</span><span style="color: rgb(0,128,0)">@spid</span><span style="color: rgb(0,0,0)">) 
</span><span style="color: rgb(0,0,255)">fetch</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">next</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">from</span><span style="color: rgb(0,0,0)"> getspid </span><span style="color: rgb(0,0,255)">into</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,128,0)">@spid</span><span style="color: rgb(0,0,0)"> 
</span><span style="color: rgb(0,0,255)">end</span><span style="color: rgb(0,0,0)"> 
</span><span style="color: rgb(0,0,255)">close</span><span style="color: rgb(0,0,0)"> getspid 
</span><span style="color: rgb(0,0,255)">deallocate</span><span style="color: rgb(0,0,0)"> getspid 
</span><span style="color: rgb(0,0,255)">go</span><span style="color: rgb(0,0,0)"> 

</span><span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">用法 </span><span style="color: rgb(0,128,128)">
</span><span style="color: rgb(0,0,255)">use</span><span style="color: rgb(0,0,0)"> master 
</span><span style="color: rgb(0,0,255)">exec</span><span style="color: rgb(0,0,0)"> killspid </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">数据库名</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)"> 
</span></div>
试试其它关键字
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)