<!--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 返回上一语句执行时出错的数量