系统存储过程

一、一般存储过程
1、信息查看类
1.1、查看系统对象
过程名
系统表或视图
描述

sp_helpserver
sysservers
查看所有服务器信息

sp_helpdb
sysdatabases
查看所有数据库信息，如：名称、大小

sp_helplogins
syslogins
查看所有数据库用户登录信息

sp_helpuser
sysusers
查看当前数据库用户和角色信息

sp_helprole
sysusers
查看当前数据库用户和角色信息

sp_helprolemember
sysmembers
返回有关当前数据库中某个角色的成员的信息

sp_helpsrvrolemember
spt_values
固定服务器角色成员的信息

sp_helpremotelogin
sysremotelogins
查看远端数据库用户登录信息

sp_helplinkedsrvlogin
sysservers
查看链接服务器的登录

sp_helprotect
sysprotects
查看当前数据库下，对象级用户权限

sp_helpindex
sysindexes
查看当前数据库下某个数据对象的索引信息

sp_helpconstraint
sysconstraints
查看当前数据库下某个数据对象的的约束信息

sp_stored_procedures
sysobjects
查看当前数据库里所有的存储过程和函数

sp_tables
sysobjects
查看当前数据库下所有的表

sp_helptext
syscomments
查看当前存储过程和函数的源代码

sp_configure
sysconfigures
用户设置的每个配置选项在表中各占一行

sp_dboption
spt_values
显示或更改数据库选项

sp_helpfile
sysfiles
数据库中的每个文件在表中占一行

sp_helpfilegroup
sysfilegroups
数据库中的每个文件组在表中占一行

注：
（1）几乎所有sp_help系列的存储过程都有对应的系统表或视图，往往通过过程返回的结果集比系统表或视图更直观，但存储过程返回的结果集并不方便使用select操作；
（2）在调用sp打头的存储过程时，SQL SERVER会默认先去master库中查找，如果不存在再回到当前库中找，这就是为什么当前库中没有以上过程，而我们仍然可以直接运行，同样在自定义存储过程时，最好不要以sp打头，因为这样就多了一次到master中查找的运行成本；
（3）SQL SERVER服务相关的系统过程都在master库中，如：各种ADD、DROP、CHANGE、HELP类的系统过程，另外master库中还有基于DLL实现的扩展存储过程；
SQL Agent服务相关的系统过程都在msdb库中，如：作业、维护计划、日志传送、复制等。

1.2、查看某数据库下表的行数和使用空间信息
sp_spaceused @objname
注：
（1）该过程根据sysindexes表中的信息返回结果，但由于统计信息的更新不及时，可能会有不精确的地方，可以DBCC UPDATEUSAGE来更新sysindexes表中的统计；

1.3、查看数据库里用户和进程的信息
sp_who/sp_who2
注：
（1）查看SQL Server数据库里的活动用户和进程的信息
sp_who 'active'
（2）推荐使用网友改进的sp_who3过程可以直接看到进程运行的SQL语句，当然也可自己在sysprocesses中根据进程ID查看
（3）SPID 50以内是SQL Server系统内部用的,进程号大于50的才是用户的连接进程

1.4、查看SQL Server数据库里的锁的情况
sp_lock
注：
（1）推荐使用网友改进的sp_who_lock过程检查死锁信息，结合了sysprocesses的进程信息和syslockinfo的锁定信息

1.5、查看 SQL Server、数据库网关或基础数据源的特性名和匹配值的列表。
sp_server_info

2、数据库维护类
2.1、重命名数据库对象
sp_rename ObjectName,NewObjectName –-数据库对象
sp_rename [tableName.colName],'NewColName'--字段

2.2、重命名数据库
exec sp_renamedb 'oldname','newname'
或
alter database dbname modify name='newdbname'
注：
用SQL语句进行数据库重命名时，需要设置数据库为单用户模式，设置详见《SQL SERVER 2000 管理——设置数据库选项》。

2.3、扩展属性（注释）
sp_addextendedproperty
--adds a new extended property to a database object

sp_dropextendedproperty
--removes an extended property from a database object

sp_updateextendedproperty
--updates the value of an existing extended property

::fn_listextendedproperty
/*retrieves the value of an extended property or the list of all extended properties from a database object*/
注：
（1）关于注释这个问题，之前没用SQL语句去添加过，都是在Enterprise Manager里面添加的：
查了一下资料，得知SQL Server 2000中引入了扩展属性，用户可在各种自定义数据库对象上定义这些属性。这些扩展属性可用于存储与数据库对象有关的应用程序或站点特有的信息。
（2）在Oracle中可用COMMENT语句给栏位加注释，如下：
COMMENT ON COLUMN employees.job_id
   IS 'abbreviated job title';
删除注释：
COMMENT ON COLUMN employees.job_id IS ' ';
2.4、刷新以优化或防止失效
sp_refreshview
--解决重建视图的问题，还可以通过重建，发现系统中视图的语法错误。
sp_recompile   
--若调整了表结构或索引，使存储过程和触发器在下次运行时重新编译。

2.5、消除孤立用户
一般登录被删除，数据库用户会被级联删除，只有在登录被非正常删除时才会存在孤立用户。
sp_change_users_login 'Report'
--列出当前数据库中未链接到任何登录的用户
sp_change_users_login 'Auto_Fix','samenamelogin'
--将当前数据库的 sysusers 表中的用户条目链接到 syslogins 中同名的登录上。sp_change_users_login 'Update_One', 'user', 'login'
--将当前数据库中指定的 user 链接到 login。login 必须已经存在。

2.6、更改数据所有者（数据库级所有者）
大家可能会经常碰到一个数据库备份还原到另外一台机器结果导致所有的表都不能打开了，原因是建表的时候采用了当时的数据库用户。如果想使用当前用户，则可通过如下方法更改所有者。
--更改当前数据库的所有者。
exec sp_changedbowner 'newLogin'
--更改表、存储、试图对象的所有者，'dbo'处可以使用其他的数据库账号，但非系统账号
exec sp_changeobjectowner 'objectname','newowner'
--批量更改
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch
@OldOwner as NVARCHAR(128),
@NewOwner as NVARCHAR(128)
AS
DECLARE @Name   as NVARCHAR(128)
DECLARE @Owner as NVARCHAR(128)
DECLARE @OwnerName as NVARCHAR(128)
DECLARE curObject CURSOR FOR
select 'Name'   = name,
'Owner'   = user_name(uid)
from sysobjects
where user_name(uid)=@OldOwner
order by name
OPEN curObject
FETCH NEXT FROM curObject INTO @Name, @Owner
WHILE(@@FETCH_STATUS=0)
BEGIN  
if @Owner=@OldOwner
begin
set @OwnerName = @OldOwner + '.' + rtrim(@Name)
exec sp_changeobjectowner @OwnerName, @NewOwner
end
-- select @name,@NewOwner,@OldOwner
FETCH NEXT FROM curObject INTO @Name, @Owner
END
close curObject
deallocate curObject
3、其他
3.1、动态SQL语句
在T-SQL 应用中，有时我们无法立即确定字段名或表名，我们希望字段名或表名能像变量一样自由变换，如：
declare @s varchar(255)
select @s = 'username'
select @s from tablename
上述代码试图用@s 变量代替字段名userName，但在实际使用中却会换错，同样在表名也无法直接用变量，要解决此问题，我们可以用存储过程：sp_executesql，如下：示例：
declare @s nvarchar(255)
declare @sql nvarchar(255)
select @s = 'userName'
select @sql = 'select ' + @s + ' from tablename'
exec sp_executesql @sql
exec()可以实现与sp_executesql同样的功能，只是后者还支持传参数。如下：
exec sp_executesql N'select * from authors where [email=state=@state]state=@state[/email] and [email=contract=@contract]contract=@contract'[/email], [email=N]N'@state[/email] char(2), @contract bit', 'CA', 1
注：
（1）在拼完串的时候不要急着exec这个存储过程，而是应该先select @SQLStatement或print @SQLStatement，以检查语句正确性；
（2）对于在语句中需要用到的某些符号要利用转义字符进行转换，比如'号；
（3）本该存在的空格也要作为字符来处理，不能少，防止因为空格而引起语句的不合法；
（4）该过程不支持表变量参数类型；
（5）动态的DDL语法不支持传参，只能像在exec()中一样，通过转化为字符串类型用+连接；
（5）除了@statement外，参数定义也必须是可以隐式转换成ntext 的Unicode 常量或变量，所以这里字符串前面的N 不能省略。
3.2、循环执行
sp_MSForEachDB对服务器上的每个数据库执行三条命令：
@command1：第一个执行的命令
@replacechar：用另一个占位赋替换“?”
@command2：第二个执行的命令
@command3：第三个执行的命令
@precommand：进入循环前执行的命令
@postcommand：循环结束后执行的命令
exec sp_MSForEachDB @command1 = 'use ? exec sp_SpaceUsed'

sp_MSForEachTable对数据库上的每个表执行三条命令：
@command1：第一个执行的命令
@replacechar：用另一个占位符替换“？”
@command2：第二个执行的命令
@command3：第三个执行的命令
@whereand：Where条件语句 (或 Order By 语句)
@precommand：进入循环前执行的命令
@postcommand：循环结束后执行的命令
exec sp_MSForEachTable
@command1 = "Print '?'",
@command2 = "select count(*) from ?",
@whereand = "ORDER BY 1"
注：
其实，这两个过程都是用的游标，完全可以自己实现。
二、扩展存储过程
注：
（1）不推荐在开发的应用程序中使用未公布的扩展存储过程，因为在以后的SQL SERVER版本中可能会被删除，如果真的要在数据库端实现，可以自己封装COM标准的DLL，建立扩展存储过程，但是要在能保证兼容性与执行效率的前提下；
（2）即便是公布了的扩展存储过程，也要慎用，尤其是要考虑到其安全性。
1、查看信息
1、查看数据库所在机器操作系统参数
exec master..xp_msver

2、获得SQL SERVER的版本号
exec master..sp_MSgetversion

3、得到当前SQL SERVER服务器的计算机名称
execute master..xp_getnetname

4、查看硬盘上的文件、目录、分区及服务器的驱动器
--参数说明:目录名,目录深度,是否显示文件
execute master..xp_dirtree 'c:'
execute master..xp_dirtree 'c:',1
execute master..xp_dirtree 'c:',1,1

--列出指定目录的所有下一级子目录
exec master..xp_subdirs 'C:/'

--查看硬盘分区及可用空间
exec master..xp_fixeddrives

--列出驱动器信息
exec master..xp_availablemedia

name
low free
high free
media type

C:/
2846720
1
2

D:/
-156913664
0
2

E:/
654663680
0
2

F:/
854589440
0
2

G:/
0
0
8

H:/
0
0
8

以字节为单位的空闲空间

软驱(1),硬盘(2),cd-rom(8)

 

5、列出服务器上安装的所有OLEDB提供的程序
exec master..xp_enum_oledb_providers

6、列出服务器上安装的所有代码页
exec master..xp_enumcodepages

7、列出服务器上配置的dsn（odbc数据源名称）
exec master..xp_enumdsn

8、列出sql server错误日志列表,最后更新时间
exec master..xp_enumerrorlogs

9、列出服务器上所有windows本地组
exec master..xp_enumgroups

10、列出SQL SERVER错误日志的具体内容
exec master..xp_readerrorlog

11、检测文件存在性
execute master..xp_fileexist 'c:/a.bak'

declare @flag int
exec master..xp_fileexist 'c:/abc.bak',@flag out
if @flag=1
begin
print 'exist'
end else
begin
print 'no exist'
end
2、注册表   
注：
SQL Server中包含了几个可以访问注册表的扩展存储过程，而且很多人在利用SQL Server进行系统攻击时，往往都会用到这几个扩展存储过程，所以最好在SQL Server中禁用掉它们。

1、读取指定键的值
使用方法：
xp_regread 根键,子键,键值名
实例：
use master
exec xp_regread 'HKEY_LOCAL_MACHINE',
'software/Microsoft/Windows/CurrentVersion','CommonFilesDir'

2、写入指定键的值
使用方法：
xp_regwrite 根键,子键, 值名, 值类型, 值
实例：
use master
exec xp_regwrite 'HKEY_LOCAL_MACHINE',
'software/Microsoft/Windows/CurrentVersion','TestValueName','reg_sz','hello'
--注意值类型有两种：REG_SZ 表示字符型,REG_DWORD 表示整型

3、删除某个值
使用方法：
xp_regdeletevalue 根键,子键,值名
实例：
use master
exec xp_regdeletevalue 'HKEY_LOCAL_MACHINE',
'software/Microsoft/Windows/CurrentVersion','TestValueName'

4、删除键,包括该键下所有值
使用方法：
xp_regdeletekey 根键,子键
实例：
use master
exec xp_regdeletekey 'HKEY_LOCAL_MACHINE',
'software/Microsoft/Windows/CurrentVersion/Testkey'
--如果没有Testkey这个注册表项，则会提示：系统找不到指定的文件。

5、写入多字符串值（即多行的字符串值）
使用方法：
xp_regaddmultistring 根键,子键,值名,值
--不需要写值类型，固定为REG_MULTI_SZ
实例：
use master
exec xp_regaddmultistring 'HKEY_LOCAL_MACHINE',
'software/Microsoft/Windows/CurrentVersion','test','hello'

6、删除多字符串值，但不删除这个键值
使用方法：
xp_regremovemultistring 根键,子键,值名,值
实例：
use master
exec xp_regremovemultistring 'HKEY_LOCAL_MACHINE',
'software/Microsoft/Windows/CurrentVersion','test','hello'
--删除多字符串值
exec xp_regdeletevalue 'HKEY_LOCAL_MACHINE',
'software/Microsoft/Windows/CurrentVersion','test'
--删除该键值

7、以多个记录集方式返回当前键下所有键值
使用方法：
xp_regenumvalues 注册表根键,项
实例：
use master
exec xp_regenumvalues 'HKEY_LOCAL_MACHINE',
'software/Microsoft/Windows/CurrentVersion/run'

8、以行集方式返回当前键下的所有子键
使用方法：
xp_regenumkeys 注册表根键,项
实例：
use master
exec xp_regenumkeys 'HKEY_LOCAL_MACHINE',
'software/Microsoft/Windows/CurrentVersion/run'
3、OLE自动化
注：
SQL SERVER存在着非常严重的安全隐患。sa的密码就像Administrator的密码一样重要。
假如别人获得了数据库超级用户sa的密码，那么就可以在查询分析器里使用下面的SQL语句轻易在服务器上创建一个超级用户：
declare @object int
--创建WScript.Shell
EXEC sp_OACreate 'WScript.Shell',@object OUT
--运行net user iamhacker opendoor /add
--添加一个名字为iamhacker,密码为opendoor的用户
EXEC sp_OAMethod @object,'Run',NULL,'net user iamhacker opendoor /add'
--把iamhacker添加到Administrators组中
EXEC sp_OAMethod @object,'Run',NULL,'net localgroup Administrators iamhacker /add'
--把iamhacker从User组中删除
EXEC sp_OAMethod @object,'Run',NULL,'net localgroup Users iamhacker /del'
其实现方法是SQL SERVER调用COM Automation的强大功能。再配合WSH中的一个对象WScript.Shell的一个方法：Run。
一般SQL SERVER都是配置在SYSTEM的权限下运行的。也就是说，Run所调用的程序也是具有SYSTEM的权限。用Run调用一下NOTEPAD，那么任务管理器下将有用户为SYSTEM的NOTEPA.EXE进程存在。
注：
ActiveX自动脚本的存储过程如下：
sp_OACreate
sp_OADestroy
sp_OAMethod
sp_OAGetProperty
sp_OASetProperty
sp_OAGetErrorInfo
sp_OAStop
另外还有一个扩展过程xp_cmdshell，也存在同样的BUG，建议全部删除。
4、维护扩展存储过程
1、删除扩展存储过程
exec master..sp_dropextendedproc 'xp_cmdshell'

2、新建扩展存储过程
exec master..sp_addextendedproc 'xp_cmdshell', 'xplog70.dll'

3、查看扩展存储过程信息
exec master..sp_helpextendedproc 'xp_cmdshell'

xp_cmdshell屏蔽的方法为：
sp_dropextendedproc 'xp_cmdshell'
如果需要的话，再进行恢复：
sp_addextendedproc 'xp_cmdshell', 'xpsql70.dll'
如果你不知道xp_cmdshell使用的是哪个.dll文件的话：
sp_helpextendedproc 'xp_cmdshell'
所以，将xp_cmdshell屏蔽后，我们还需要做的是将xpsql70.dll文件进行改名，以防止获得sa的攻击者将它进行恢复。