sqlserver2008 常用方法

<!--defaultCSS-->

1、使用游标循环更改特定数据表的值

DECLARE tnames_cursor CURSOR 
FOR 
SELECT parentId 
FROM jhtdb.dbo.SC_TABLELIST 
OPEN tnames_cursor 
DECLARE @tablename int
FETCH NEXT FROM tnames_cursor INTO @tablename 
WHILE (@@FETCH_STATUS <> -1) 
BEGIN 
IF (@@FETCH_STATUS <> -2) 
BEGIN     
declare @id int
set @id = jhtdb.dbo.getIdByName(@tablename)
--print  @tablename
if(@id <> 0)
begin
update SC_TABLELIST set parentId=@id where parentId=@tablename
end
END 
FETCH NEXT FROM tnames_cursor INTO @tablename 
END 
CLOSE tnames_cursor 
DEALLOCATE tnames_cursor 

 2、强制类型转换

CAST(@tablename as int)  其中@tablename为需要被转换的值

 3、去除热备份数据库的只读/备用状态

use master
go
RESTORE DATABASE JHTDBTMP
WITH RECOVERY 
其中JHTDBTMP为数据库的名称

4、查看数据库的占用情况

 

sp_who            -- 查看所有用户连接
sp_who 'active'        -- 查看活动用户连接
sp_who 'sa'        -- 查看用户sa连接 

 

5、查看特定数据库的连接情况

SELECT * FROM 
[Master].[dbo].[SYSPROCESSES] WHERE [DBID] 
IN 
(
  SELECT 
   [DBID]
  FROM 
   [Master].[dbo].[SYSDATABASES] 
  WHERE 
   NAME='数据库名称'
) 

 6、强制关闭数据库连接

USE master
go 
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[p_killspid]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    DROP PROCEDURE [dbo].[p_killspid]
go
create   proc   p_killspid 
@dbname   varchar(200) --要关闭进程的数据库名
as     
declare   @programName     nvarchar(200), 
@spid   nvarchar(20) 
declare   cDblogin   cursor   for 
select   cast(spid   as   varchar(20))  AS spid   from   master..sysprocesses   where   dbid=db_id(@dbname) 
open   cDblogin
fetch   next   from   cDblogin   into   @spid 
while   @@fetch_status=0 
begin    
--防止自己终止自己的进程 
--否则会报错不能用KILL 来终止您自己的进程。  
IF    @spid <> @@SPID
    exec( 'kill   '+@spid) 
fetch   next   from  cDblogin   into   @spid 
end     
close   cDblogin 
deallocate   cDblogin
go 
调用方法：exec   p_killspid     'jhtdb' 

 7、MSSQL中常用的全局变量解析

@@FETCH_STATUS  返回语句的状态

0	FETCH statement was successful.
-1	FETCH statement failed or the row was beyond the result set.
-2	Row fetched is missing.

@@ROWCOUNT      返回上一语句影响的行数
@@SPID                返回当前用户进程回话的id
@@TRANCOUNT      返回当前连接中包含的事物数量
@@ERROR              返回上一语句执行时出错的数量