SQL Server 数据库管理实用技巧-优快云博客

例28.1 查看数据库所在机器操作系统参数（xp_msver）
查看数据库所在机器操作系统参数的存储过程如下
EXEC master..xp_msver

例28.2 查看当前数据库服务器名（xp_getnetname、HOST_NAME()）
EXEC master..xp_getnetname
或者：
SELECT HOST_NAME()

例28.3 查看服务器上所有Windows本地组（xp_enumgroups）
查看服务器上所有Windows本地组的存储过程如下
EXEC master..xp_enumgroups

例28.4 查看服务器上安装的所有代码页（xp_enumcodepages）
查看服务器上安装的所有代码页的存储过程如下
EXEC master..xp_enumcodepages

嘿嘿,是不是报没开启错误啊~~没错误略过
先执行下面语句

USE master;

GO

EXEC sp_configure 'show advanced option', '1';

RECONFIGURE

EXEC sp_configure 'Web Assistant Procedures',1

RECONFIGURE

EXEC sp_configure

例28.5 查看指定目录的所有下一级子目录（xp_subdirs）
查看C:/WINDOWS/Drivers目录所有下一级子目录的存储过程如下

EXEC [master].[dbo].[xp_subdirs] 'C:/WINDOWS/Drivers'

查看磁盘信息的存储过程如下

EXEC [master].[dbo].[xp_availablemedia]

或者采用下列语句

EXEC master..xp_fixeddrives

请注意:其中，low free为以字节为单位的空闲空间，media type为驱动器类型，软驱为1，硬盘为2，CD-ROM为8。

例28.8 查看硬盘文件信息（xp_dirtree）

查看C盘文件信息的存储过程如下
EXEC master..xp_dirtree 'c:'

查看C盘深度为1的文件信息的存储过程如下

EXEC master..xp_dirtree 'c:',1

查看C盘深度为1、是否为的信息的存储过程如下

EXEC master..xp_dirtree 'c:',1,1

查看“C:/WINDOWS/Web”目录中的文件信息的存储过程如下

EXEC master..xp_dirtree 'C:/WINDOWS/Web'

例28.9 查看服务器提供的OLEDB程序（xp_enum_oledb_providers）

查看服务器提供的OLEDB程序的存储过程如下

EXEC master..xp_enum_oledb_providers

例28.10 查看数据库服务名（@@SERVERNAME）

print 'SQL Server数据库服务名: ' +

     convert(varchar(30),@@SERVERNAME)

查看结果如下：

SQL Server数据库服务名: SZX/SQLS2008

例28.11 查看数据库实例名（@@SERVICENAME）

PRINT 'SQL Server数据库实例名: ' +

    convert(varchar(30),@@SERVICENAME)

查看结果如下：

SQL Server数据库实例名: MSSQLSERVER

例28.12 查看DBMS数据库的版本（@@version、sp_msgetversion）
查看DBMS版本的命令如下
SELECT @@version SQL_Server版本
或者
EXEC master..sp_msgetversion

例28.13 查看数据库配置参数（sp_configure）
查看数据库配置参数的存储过程如下
EXEC sp_configure

例28.14 查看所有数据库名称、容量等信息（sp_helpdb、sp_databases）
sp_helpdb
或sp_databases
查看单个数据库的容量：如sql92_exam_xjgl
sp_helpdb sql92_exam_xjgl

例28.15 查看所有数据库名称及大小（sp_helpdb）

查看所有数据库名称及大小的存储过程如下
EXEC sp_helpdb

例28.16 修改数据库的名称（sp_renamedb）
EXEC sp_renamedb 'old_dbname', 'new_dbname'

例28.17 查看当前数据库的启动时间（sysprocesses）
SELECT convert(varchar(30),login_time,120)启动时间

FROM master..sysprocesses

WHERE spid=1

例28.18 查看当前数据库文件路径等信息（sp_helpfile）

sp_helpfile

例28.19 查看AdventureWorks数据库文件路径（sysfiles）
SELECT ltrim(rtrim(filename))

FROM AdventureWorks..sysfiles

WHERE charindex('MDF',filename)>0

查看结果如下：

C:/Program Files/Microsoft SQL Server/100/Tools/Samples/

AdventureWorks_Data.mdf

例28.20 查看当前数据库文件组名（sp_helpfilegroup）

sp_helpfilegroup

例28.21 查看当前数据库中所有的存储过程（sysobjects）

查看当前数据库中所有存储过程的命令如下

SELECT name as存储过程名称FROM sysobjects WHERE xtype='P'

例28.22 查看数据库里所有的存储过程和函数（sp_stored_procedures）
查看某个数据库里所有存储过程和函数的命令如下

use AdventureWorks

GO

EXEC sp_stored_procedures

例28.23 查看所有数据库日志文件大小（dbcc sqlperf()）

查看所有数据库日志文件大小的命令如下

dbcc sqlperf(logspace)

例28.24 查看SQL Server错误日志（xp_enumerrorlogs、xp_readerrorlog）

（1）查看SQL Server错误日志信息

EXEC master..xp_enumerrorlogs

（2）查看当前错误日志具体内容

EXEC [master].[dbo].[xp_readerrorlog]

例28.25 查看数据库角色信息（sp_helpRole）

查看数据库角色信息的存储过程如下

EXEC sp_helpRole

例28.26 查看所有数据库用户所属的角色信息（sp_helpsrvrolemember）

查看所有数据库用户所属角色信息的存储过程如下

EXEC sp_helpsrvrolemember

例28.27 查看角色成员用户信息（sp_helpRoleMember）

查看角色成员用户信息的存储过程如下

EXEC sp_helpRoleMember 'db_owner'

例28.28 查看当前数据库所有用户信息（sp_helpUser）

查看数据库用户信息的存储过程如下

EXEC sp_helplogins

例28.29 查看本地数据库用户登录信息（sp_helplogins）

查看本地数据库用户登录信息的存储过程如下

EXEC sp_helplogins

例28.30 查看远端数据库用户登录信息（sp_helpremotelogin ）

查看远端数据库用户登录信息的存储过程如下

EXEC sp_helpremotelogin

例28.31 查看链接服务器的登录信息（sp_helplinkedsrvlogin）

查看链接服务器信息的存储过程如下

EXEC sp_helplinkedsrvlogin

例28.32 将用户账户添加到数据库中（sp_grantdbaccess）

将guest用户账户添加到数据库中的命令如下：

USE sql92_exam_xjgl

GO

EXEC sp_grantdbaccess guest

例28.33 查看数据库下对象级用户权限（sp_helprotect）

查看数据库下对象级用户权限的存储过程如下

EXEC sp_helprotect
例28.34 获得当前数据库所有用户表的名称（sysobjects）

获得当前数据库所有用户表名称的命令如下

SELECT OBJECT_NAME (id)

FROM sysobjects

WHERE xtype = 'U' AND OBJECTPROPERTY (id, 'IsMSShipped') = 0

例28.36 查询单个用户创建的所有数据库（sysdatabases）

查询sa用户创建的所有数据库的命令如下

SELECT name,dbid,filename

FROM master..sysdatabases D

WHERE sid not in(SELECT sid FROM master..syslogins WHERE name='sa')

或者通过以下命令实现：

SELECT dbid, name AS DB_NAME,filename

FROM master..sysdatabases

WHERE sid <> 0x01

例28.37 查看一个表上相关的对象（sysobjects、syscomments）

查询一个表上相关对象的命令如下

SELECT a.* FROM sysobjects a, syscomments b

WHERE a.id = b.id and b.text like '%teacher%'

例28.38 获取某一个表的所有字段（syscolumns、sp_mshelpcolumns）

获取某一个表的所有字段的命令如下

SELECT name FROM syscolumns WHERE id=object_id('teacher')

或者通过以下存储过程实现

EXEC sp_mshelpcolumns 'teacher'

例28.39 获得指定表上所有的索引名称（sysindexes）

通过系统表sysindexes获得指定表上所有索引名称的命令如下

SELECT name FROM sysindexes

WHERE id = OBJECT_ID ('dept') AND indid > 0

例28.40 查询某一个表的字段和数据类型（information_schema.columns）

查询某一个表的字段和数据类型的命令如下

SELECT column_name,data_type

FROM information_schema.columns

WHERE table_name = N'teacher'

例28.41 获取某一个表的基本信息（sp_MShelpcolumns）

获取某一个表的基本信息的存储过程如下

EXEC sp_MShelpcolumns N'teacher'

例28.42 获取某一个表的主键、外键信息（sp_pkeys、sp_fkeys）

获取表teacher的主键信息的存储过程如下

EXEC sp_pkeys N'teacher'

获取表teacher的外键信息的存储过程如下：

EXEC sp_fkeys   N'teacher'

例28.43 判断某一个表是否存在某一字段（syscolumns）

通过系统表syscolumn判断某一个表是否存在某一字段的命令如下：

if exists(SELECT 1 FROM syscolumns

            WHERE id=object_id(N'teacher') and name=N'tname')

    print N'存在'

else

    print N'不存在'

例28.44 查看单个存储过程和函数的源代码（sp_helptext）

查看单个存储过程和函数源代码的存储过程如下

USE AdventureWorks

GO

EXEC sp_helptext 'dbo.uspLogError'

例28.45 查看数据库里用户和进程的信息（sp_who）

（1）查看数据库里用户和进程信息

EXEC sp_who

其中，spid是进程编号，进程号1--50是SQL Server系统内部用的，进程号大于50的是用户的连接进程。

（2）查看活动用户和进程信息的存储过程如下

EXEC sp_who 'active'

例28.46 清除sleeping进程（Kill__Sleeping_Processes）

Sleeping进程是系统中处于睡眠状态尚未活动的进程，这部分进程可以杀掉。

CREATE PROCEDURE dbo.Kill__Sleeping_Processes @dbname varchar(50)

AS

BEGIN

SET NOCOUNT ON

DECLARE @spid INT,

@cnt INT,

@sql VARCHAR(255)

SELECT @spid = MIN(spid),@cnt = COUNT(*)

FROM master..sysprocesses

WHERE dbid = DB_ID(@dbname) AND spid != @@SPID AND status = 'sleeping'

WHILE @spid IS NOT NULL

BEGIN

SET @sql = 'KILL ' + RTRIM(@spid)

EXEC ( @sql )

SELECT @spid = MIN(spid),@cnt = COUNT(*)

FROM master..sysprocesses

WHERE dbid = DB_ID(@dbname) AND spid != @@SPID AND status = 'sleeping'

END

END

GO

运行改存储过程后杀掉sleeping进程：

Kill__Sleeping_Processes SQLS2008

清除前后可以用EXEC sp_who 'active '进行查看。

例28.47 查看进程正在执行的SQL语句（dbcc inputbuffer ()）

查看进程正在执行的SQL语句：dbcc inputbuffer (spid)

其中spid是用sp_who查询出来的spid号，如：56。

查看56号进程正在执行的SQL语句实例如下

dbcc inputbuffer (56)

例28.48 查看数据库锁（sp_lock）

查看SQL Server数据库里锁的情况的存储过程如下

EXEC sp_lock

其中，spid是进程编号，dbid是数据库编号，Objid是数据对象编号。

例28.49 查看数据库死锁的进程（proc_who_lock）

用proc_ find _lock过程查看数据库死锁的进程。

if exists (SELECT *

           FROM dbo.sysobjects

           WHERE id = object_id(N'[dbo].[proc_find_lock]')

           and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[proc_find_lock]

GO

use master

go

create procedure proc_find_lock

as

begin

declare @spid int,@bl int,

@intTransactionCountOnEntry int,

         @intRowcount int,

         @intCountProperties int,

         @intCounter int

create table #temp_lock (

id int identity(1,1),

spid smallint,

bl smallint)

IF @@ERROR<>0 RETURN @@ERROR

insert into #temp_lock(spid,bl)

   SELECT   0 ,blocked

   FROM (SELECT * FROM sysprocesses WHERE   blocked>0 ) a

   WHERE not exists(SELECT *

                    FROM (SELECT *

                          FROM sysprocesses

                          WHERE   blocked>0 ) b

                    WHERE a.blocked=spid)

   union SELECT spid,blocked

         FROM sysprocesses

         WHERE   blocked>0

IF @@ERROR<>0 RETURN @@ERROR

   SELECT @intCountProperties = Count(*),@intCounter = 1 --找到临时表的记录数

   FROM #temp_lock

IF @@ERROR<>0 RETURN @@ERROR

   if @intCountProperties=0

      SELECT '没有阻塞和死锁信息' as 提示

while @intCounter <= @intCountProperties

begin

SELECT @spid = spid,@bl = bl

FROM #temp_lock WHERE Id = @intCounter

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 )

end

set @intCounter = @intCounter + 1

end

drop table #temp_lock

return 0

end

运行过程proc_find_lock

proc_find_lock

例28.50 备份当前数据库（backup database）

（1）完全备份

declare @a sysname,@s nvarchar(4000)

SELECT @a=db_name(),@s='e:/sql_db_bak_full' backup database @a to disk=@s WITH format

已为数据库'sql92_exam_xjgl'，文件'sql92_example'(位于文件1上)处理了240页。

已为数据库'sql92_exam_xjgl'，文件'sql92_example_log'(位于文件1上)处理了1页。

BACKUP DATABASE成功处理了241页，花费0.101秒(19.547 MB/秒)。

（2）差异备份

declare @a sysname,@s nvarchar(4000)

SELECT @a=db_name(),@s='e:/sql_db_bak_diff' backup database @a to disk=@s WITH DIFFERENTIAL,format

已为数据库'sql92_exam_xjgl'，文件'sql92_example' (位于文件1上)处理了40页。

已为数据库'sql92_exam_xjgl'，文件'sql92_example_log' (位于文件1上)处理了1页。

BACKUP DATABASE WITH DIFFERENTIAL成功处理了41页，花费0.095秒(3.530 MB/秒)。

差异备份要在至少有一次完全备份成功之后才能实施，否则出错。

例28.51 从磁带中恢复完整的数据库备份

从磁带中恢复sql92_exam_xjgl的完整数据库备份。

USE master

GO

RESTORE DATABASE sql92_exam_xjgl

   FROM TAPE = '//./Tape0'

GO

例28.52 在脱机状态下进行数据库备份和恢复

在脱机状态下对sql92_exam_xjgl数据库进行备份和恢复。

步骤如下：

卸载需要进行备份恢复的sql92_exam_xjgl数据库。

在master数据库下新建下列查询分析语句：

Sp_detach_db 'sql92_exam_xjgl'

运行结果为：命令已成功完成。

复制sql92_exam_xjgl 库的sql92_example.mdf文件和sql92_example_log.ldf文件到E:/下。　

启动数据库服务器进行恢复。

sp_attach_db 'sql92_exam_xjgl',

'E:/sql92_example.mdf', 'E:/sql92_example_log.ldf'

运行结果为：命令已成功完成。

当恢复单个mdf文件采用下列语句：

sp_attach_single_file_db 'sql92_exam_xjgl','E:/sql92_example.mdf''