1. 枚举扩展存储过程
扩展存储过程是一种用于执行系统级任务的特殊存储过程。它们具有对CLR(公共语言运行时)的完全访问权限,并与SQL Server使用的语言相同。扩展存储过程通常用于访问网络资源、操作系统级操作等功能,而这些功能通常不在SQL Server范围内。
在SQL Server中,可以使用如下命令来枚举扩展存储过程:
EXEC sp_enumextendedproc
这会返回所有已安装的扩展存储过程名称。
1.1 示例
EXEC sp_enumextendedproc
返回结果:
xp_availablemedia
2. 运行操作系统命令
在某些情况下,您可能需要在SQL Server上运行操作系统级别的命令。在SQL Server中,可以使用扩展存储过程xp_cmdshell来执行这样的命令。但是需要注意的是:默认情况下,xp_cmdshell被禁用。
2.1 启用xp_cmdshell
要启用xp_cmdshell,可以使用以下命令:
sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE
GO
启用后,可以使用如下命令来运行操作系统命令:
xp_cmdshell 'your_command'
2.2 示例
在某些情况下,可能需要使用扩展存储过程来将结果输出到一个表中。以下示例显示如何将ipconfig /all的结果插入到包含所有网络配置信息的临时表中:
CREATE TABLE #tmp (result varchar(2000))
INSERT INTO #tmp EXEC xp_cmdshell 'ipconfig /all'
SELECT * FROM #tmp
DROP TABLE #tmp
3. 使用sp_prepare和sp_execute
如果您需要频繁地运行一些重复的查询(例如从客户端向服务器插入数据),您可以使用sp_prepare和sp_execute组合,这将大大提高性能。
尽管SQL Server有自己的执行计划缓存,但是通过使用sp_prepare和sp_execute,可以缓存的SQL提高执行计划的效率。这对于仅传递参数不但不变的查询非常有用。
3.1 示例
以下示例将一条包含参数的复杂查询准备好,然后使用sp_execute来在后续查询中重复使用:
DECLARE @var1 int, @var2 varchar(50)
SET @var1 = 123456
SET @var2 = 'Hello, World!'
EXEC sp_prepare @handle OUTPUT, N'@p1 int, @p2 varchar(50)', N'
SELECT * FROM mytable WHERE col1 = @p1 AND col2 = @p2'
EXEC sp_execute @handle, @var1, @var2
EXEC sp_execute @handle, @var1, 'Goodbye, World!'
EXEC sp_unprepare @handle
说明:
@handle 是一个输出参数。如果预处理成功,将返回一个准备好的句柄。
sp_prepare 准备一个SQL语句。它需要三个参数:一个输出句柄、一组参数和SQL语句本身。
sp_execute 在上下文中执行准备就绪的SQL语句,带准备好的句柄、参数。
sp_unprepare 从高速缓存中删除准备好的查询(释放句柄占用的内存)。