SQL Server 2008 系统存储过程手册(sp_ / xp_ 分类速查,适配 10.0 / 10.5)

0 阅读6分钟

说明:

  1. 系统存储过程存放在 master.sys,前缀 sp_;扩展存储过程 xp_(DLL实现,2008支持,新版逐步废弃)
  2. 标注【已弃用】:2008还能跑,微软推荐改用标准T-SQL语法
  3. 查看本机全部系统存储过程:
> USE master;
> SELECT name FROM sys.procedures WHERE name LIKE 'sp_%' OR name LIKE 'xp_%' ORDER BY name;
> ```
> 4. 查看单个sp源码(部分系统sp加密无法查看):
> ```sql
> sp_helptext 'sp_help';
> ```

一、服务器配置 & 实例管理

存储过程功能示例
sp_configure查看/修改服务器全局配置(高级选项需 show advanced options)sp_configure 'show advanced options',1; RECONFIGURE;
sp_addserver定义远程/本地服务器别名
sp_dropserver删除远程服务器定义
sp_helpserver列出所有远程服务器
sp_addlinkedserver创建链接服务器(跨库查询)
sp_dropsrvrolemember从服务器角色移除登录
sp_addsrvrolemember添加登录到服务器角色
sp_helplogins查询所有登录账号信息
sp_addlogin创建SQL登录【弃用,CREATE LOGIN】
sp_droplogin删除登录【弃用,DROP LOGIN】
sp_password修改登录密码【弃用,ALTER LOGIN】

二、数据库附加/分离、数据库管理

存储过程功能
sp_attach_db附加数据库【弃用 → CREATE DATABASE … FOR ATTACH】
sp_attach_single_file_db单文件附加库【弃用】
sp_detach_db分离数据库
sp_renamedb修改数据库名【弃用 → ALTER DATABASE MODIFY NAME】
sp_helpdb查询指定/全部数据库信息、文件、大小
sp_databases返回实例所有数据库列表
sp_resetstatus重置置疑库标记【弃用,ALTER DATABASE EMERGENCY】
sp_add_data_file_recover_suspect_db置疑库恢复时增加数据文件
sp_add_log_file_recover_suspect_db置疑库恢复时增加日志文件

三、元数据/目录信息查询(开发DBA高频)

存储过程功能示例
sp_help查看对象(表/视图/存储过程)基本信息、列、约束、索引sp_help 'dbo.table1'
sp_helpindex查看表索引清单sp_helpindex 'dbo.table1'
sp_helpconstraint查看表所有约束sp_helpconstraint 'dbo.table1'
sp_pkeys返回表主键sp_pkeys 'table1'
sp_fkeys返回表外键关系sp_fkeys 'table1'
sp_columns返回表字段信息sp_columns 'table1'
sp_tables返回当前库表、视图
sp_stored_procedures返回当前库存储过程
sp_sproc_columns返回存储过程参数列表
sp_statistics返回表索引统计信息
sp_rename修改对象名(表、列、索引)⚠️ 会破坏依赖脚本sp_rename 'old','new'
sp_validname校验标识符名称是否合法

四、权限、用户、角色(数据库级别)

存储过程功能
sp_adduser添加数据库用户【弃用 CREATE USER】
sp_dropuser删除库用户【弃用 DROP USER】
sp_addrolemember添加用户到数据库角色
sp_droprolemember从库角色移除用户
sp_helprole查询数据库角色
sp_helprotect查询对象权限清单
sp_grantdbaccess给登录映射库用户【弃用】
sp_revokedbaccess删除库用户映射【弃用】

五、执行动态SQL、批处理

存储过程功能
sp_executesql重点 参数化动态SQL,防注入
sp_prepare预编译参数化语句,返回句柄
sp_execute通过句柄执行预编译语句
sp_unprepare释放预编译计划句柄
sp_prepexecprepare + execute 合并

示例 sp_executesql:

DECLARE @sql NVARCHAR(200), @p INT=1; SET @sql=N’SELECT * FROM t WHERE id=@id’;
 EXEC sp_executesql @sql,N’@id int’,@id=@p;

六、索引、触发器、表选项

存储过程功能
sp_settriggerorder设置AFTER触发器先后顺序(第一个/最后一个)
sp_tableoption设置表选项(text in row等)
sp_indexoption设置索引锁选项【弃用 ALTER INDEX】

七、全文检索(SQL2008全文)

⚠️ 2008支持,新版逐步弃用

  • sp_fulltext_catalog:创建/删除全文目录、启动填充
  • sp_fulltext_column:设置列是否加入全文索引
  • sp_fulltext_database:向后兼容,2008无实际效果
  • sp_fulltext_service:修改全文服务配置
  • sp_fulltext_keymappings:DocId与全文键映射

八、锁、会话、连接、应用锁

存储过程功能
sp_who查看会话、进程、阻塞
sp_who2sp_who增强版(非官方文档,但2008广泛使用)
sp_lock查看锁信息
sp_getapplock应用程序锁(业务层互斥锁)
sp_releaseapplock释放应用锁
sp_kill杀掉会话SPID

九、扩展存储过程 xp_(master.sys)【高危,谨慎使用】

存储过程功能
xp_cmdshell执行操作系统命令,默认关闭(sp_configure开启)
xp_readerrorlog读取SQL错误日志(最常用!)
xp_enum_oledb_providers枚举OLEDB驱动
xp_enumgroups枚举Windows组
xp_logininfoWindows账号信息
xp_msver返回SQL版本信息
xp_subdirs列出目录子文件夹
xp_dirtree列出目录文件树

xp_readerrorlog示例(2008神器)

-- 参数:日志编号(0当前),1=SQL日志 2=Agent日志,检索串1,检索串2,开始时间,结束时间
EXEC xp_readerrorlog 0,1,N'Error',NULL,'2026-11-01','2026-11-20';

⚠️ xp_cmdshell 安全风险极高,生产尽量关闭

十、数据库邮件、Agent、维护计划

  • sp_send_dbmail:发送数据库邮件(2008 Database Mail)
  • sp_add_job / sp_add_jobstep / sp_add_jobschedule:创建SQL Agent作业
  • sp_help_job:查看Agent作业
  • sp_add_maintenance_plan:维护计划【弃用】

十一、CDC 变更数据捕获(SQL2008企业版新增)

存储过程功能
sys.sp_cdc_enable_db库启用CDC
sys.sp_cdc_disable_db库关闭CDC
sys.sp_cdc_enable_table表启用CDC
sys.sp_cdc_disable_table表关闭CDC
sys.sp_cdc_help_change_data_capture查询CDC配置
sys.sp_cdc_add_job / sp_cdc_drop_jobCDC捕获/清理作业

十二、备份还原相关(少量系统sp,优先BACKUP/RESTORE)

  • sp_addumpdevice:添加备份设备【弃用】
  • sp_dropdevice:删除备份设备【弃用】
  • sp_helpdevice:查看备份设备

十三、向后兼容旧类型规则(尽量不要新开发使用)

  • sp_addtype:别名类型【弃用 CREATE TYPE】
  • sp_droptype:删除别名类型
  • sp_bindefault / sp_unbindefault:绑定默认值【弃用 DEFAULT约束】
  • sp_bindrule / sp_unbindrule:绑定rule规则【弃用 CHECK约束】

十四、ODBC 目录存储过程(给ODBC驱动用)

sp_schemata_rowset、sp_procedures_rowset、sp_table_constraints_rowset、sp_table_privileges_rowset、sp_statistics_rowset、sp_tables_info_90_rowset

一般开发很少直接调用,ODBC驱动自动调用

十五、2008 查找系统存储过程的查询脚本(可直接执行)

-- 1. 查看所有sp_ xp_系统存储过程
SELECT
    schema_name(schema_id) AS sch,
    name,
    type_desc,
    create_date,
    modify_date
FROM master.sys.procedures
WHERE name LIKE 'sp_%' OR name LIKE 'xp_%'
ORDER BY name;

-- 2. 查看系统存储过程参数
SELECT
    p.name AS proc_name,
    pm.name AS param_name,
    t.name AS type_name,
    pm.max_length,
    pm.is_output
FROM master.sys.procedures p
JOIN master.sys.parameters pm ON p.object_id=pm.object_id
JOIN master.sys.types t ON pm.system_type_id=t.system_type_id
WHERE p.name='sp_help'
ORDER BY pm.parameter_id;

⚠️ 重要避坑清单(SQL2008特有)

  1. sp_rename 改列名不会更新视图、存储过程依赖元数据,容易导致隐藏报错;
  2. sp_who2不是官方文档存储过程,属于未文档化,升级版本可能失效;
  3. xp_* 扩展存储过程在更高版本逐步移除,新项目不要依赖 xp_cmdshell;
  4. 标记【弃用】的sp只是兼容,优先使用标准DDL(CREATE/ALTER/DROP);
  5. sp_executesql 字符串参数必须是 NVARCHAR,不能用VARCHAR;
  6. CDC 只有企业版支持,标准版/Express不支持;
  7. sp_getapplock 锁是会话级,不是事务级,rollback不会自动释放。

📎 官方原版文档链接(SQL Server 2008 R2 ms105)

learn.microsoft.com/zh-