数据库常用管理语句

KILL数据库进程，常用于需要单独操作的情况下，也用于死锁情况。

代码段3：强制断开用户连接进程(也常用于死锁）。
--kill数据库的连接进程.
DECLARE @dbname sysname 
SET @dbname='adirectory' --要关闭进程的数据库名
declare @s nvarchar(1000)
declare tb cursor local for
select 'kill '+cast(spid as varchar)
from master..sysprocesses 
where dbid=db_id(@dbname)
open tb 
fetch next from tb into @s
while @@fetch_status=0
BEGIN
--PRINT @s
exec(@s)
fetch next from tb into @s
end
close tb
deallocate tb
go

--2 kill 数据库进程,需要复制生成的文本来执行。
select COALESCE('kill ','')+cast(spid as varchar)
from master..sysprocesses 
where dbid=db_id(@dbname)

4、查看数据库死锁进程。

DECLARE @spid int,@bl int
DECLARE s_cur CURSOR FOR 
select  0 ,blocked
from (select * from master..sysprocesses where  blocked>0 ) a 
where not exists(select * from (select * from master..sysprocesses where  blocked>0 ) b 
where a.blocked=spid)
union select spid,blocked from master..sysprocesses where  blocked>0
OPEN s_cur
FETCH NEXT FROM s_cur INTO @spid,@bl
WHILE @@FETCH_STATUS = 0
begin
if @spid =0 
  select '引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10)) + ' 进程号,其执行的SQL语法如下'
else
  select '进程号SPID：'+ CAST(@spid AS VARCHAR(10))+ ' 被进程号SPID：'+ CAST(@bl AS VARCHAR(10)) +' 阻塞,其当前进程执行的SQL语法如下'
DBCC INPUTBUFFER (@bl )
FETCH NEXT FROM s_cur INTO @spid,@bl
end
CLOSE s_cur
DEALLOCATE s_cur
3、更改数据库排序.更改数据库排序规则（不会改变原已有的数据）--更改为中文排序

ALTER DATABASE dbName COLLATE Chinese_PRC_CI_AS

--查看所有排序

SELECT * FROM fn_helpcollations()

 

2、查看数据库连接进程信息

select name,count(0)as conn,
hostname,program_name,loginame,
s.login_time,s.net_address,nt_domain,s.cmd
from master.dbo.sysprocesses s join master.dbo.sysdatabases d
on s.dbid=d.dbid and d.name in (select name from master.dbo.sysdatabases)
group by name,hostname,program_name,loginame,s.login_time,s.net_address,nt_domain,s.cmd 
order by name
1、当前数据库在做什么？for SQL 2005

use master
select sys.dm_exec_sessions.session_id,
sys.dm_exec_sessions.host_name,
sys.dm_exec_sessions.program_name,
sys.dm_exec_sessions.client_interface_name,
sys.dm_exec_sessions.login_name,
sys.dm_exec_sessions.nt_domain,
sys.dm_exec_sessions.nt_user_name,
sys.dm_exec_connections.client_net_address,
sys.dm_exec_connections.local_net_address,
sys.dm_exec_connections.connection_id,
sys.dm_exec_connections.parent_connection_id,
sys.dm_exec_connections.most_recent_sql_handle,
(select text from master.sys.dm_exec_sql_text(sys.dm_exec_connections.most_recent_sql_handle )) as sqlscript,
(select db_name(dbid) from master.sys.dm_exec_sql_text(sys.dm_exec_connections.most_recent_sql_handle )) as databasename,
(select object_id(objectid) from master.sys.dm_exec_sql_text(sys.dm_exec_connections.most_recent_sql_handle )) as objectname
from sys.dm_exec_sessions inner join sys.dm_exec_connections
on syssys.dm_exec_connections.session_id=sys.dm_exec_sessions.session_id
0、获取数据库的物理路径：

CREATE FUNCTION dbo.ufnGetSysDBPath(
    @dbName VARCHAR(100)
) RETURNS VARCHAR(200)
AS
BEGIN
    DECLARE @returnValue VARCHAR(200)
IF SERVERPROPERTY ('BuildClrVersion') is not null
    BEGIN 
    --PRINT 'Sql Server 2005 '
SET @returnValue= (SELECT SUBSTRING(physical_name, 1, CHARINDEX(@dbName+'.mdf', LOWER(physical_name)) - 1) FROM master.sys.master_files WHERE database_id = DB_ID(@dbName) AND file_id = 1)
    END 
ELSE 
    BEGIN 
    --PRINT 'Sql Server 2000'
SET @returnValue= (SELECT SUBSTRING(filename, 1, CHARINDEX(@dbName+'.mdf', LOWER(filename)) - 1) FROM master..sysaltfiles WHERE dbid = DB_ID(@dbName) AND fileid = 1)    
    END 
RETURN @returnValue     
END
使用： 注意项: (默认情况下,数据库名与数据库文件相同名) 如有不同,可修改部分代码后运行

SELECT dbo.ufnGetSysDBPath('master')

 

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/zhou__zhou/archive/2007/09/01/1768008.aspx