SQLServer存储过程简述以及常用的系统存储过程

1 存储过程简述

存储过程Procedure是一组为了完成特定功能的SQL语句集合，经编译后存储在数据库中，用户通过指定存储过程的名称并给出参数来执行。 存储过程中可以包含逻辑控制语句和数据操纵语句，它可以接受参数、输出参数、返回单个或多个结果集以及返回值。

简单来说，存储过程就是一条或者多条sql语句的集合,可视为批处理文件，但是其作用不仅限于批处理。
由于存储过程在创建时即在数据库服务器上进行了编译并存储在数据库中，所以存储过程运行要比单个的SQL语句块要快。同时由于在调用时只需用提供存储过程名和必要的参数信息，所以在一定程度上也可以减少网络流量、简单网络负担。

常用的系统存储过程

DECLARE @strTableName varchar(500)='XXX';

exec sp_databases; 			--查看所有数据库
exec sp_tables;			--查看所有表
exec sp_columns @strTableName;	--查看某个表中所有列
exec sp_helpIndex @strTableName;	--查看某个表中的索引
exec sp_helpConstraint @strTableName;	--查看某个表中的约束
exec sp_helpdb;			--数据库帮助，查询数据库信息
exec sp_helptext 'sp_stored_procedures';	--查看存储过程创建、定义语句
exec sp_rename student, stuInfo;	--修改表、索引、列的名称
exec sp_renamedb myTempDB, myDB;	--更改数据库名称
exec sp_defaultdb 'master', 'myDB';-	--更改登录名的默认数据库

举例说明

--表重命名
exec sp_rename 'table_old', 'table_new';
select * from table_new;
--列重命名
exec sp_rename 'table.column_old', 'column_new', 'column';
exec sp_help 'table';
--重命名索引
exec sp_rename N'student.idx_cid', N'idx_cidd', N'index';
exec sp_help 'student';
--查询所有存储过程
select * from sys.objects where type = 'P';
select * from sys.objects where type_desc like '%pro%' and name like 'sp%';

2 存储过程的基本操作

创建、修改

create proc USP_GetAllUser    --修改是alter
@UserId int =1
as 
set nocount on; --当 SET NOCOUNT 为 ON 时，不返回计数（表示受 Transact-SQL 语句影响的行数）。当 SET NOCOUNT 为 OFF 时，返回计数。 
begin
    select * from UserInfo where Id=@UserId
end

执行

exec  [存储过程名称] [参数1,参数2, ...]
exec dbo.USP_GetAllUser 2;   --示例

一般在执行存储过程时，最好加上架构名称，例如 dbo.USP_GetAllUser 这样可以可以减少不必要的系统开销，提高性能。 因为如果在存储过程名称前面没有加上架构名称，SQL SERVER 首先会从当前数据库sys schema（系统架构）开始查找，如果没有找到，则会去其它schema查找，最后在dbo架构（系统管理员架构）里面查找。

清除缓存

--清除存储过程缓存  
DBCC FREEPROCCACHE 
--清除会话缓存 
DBCC FREESESSIONCACHE 
--清除系统缓存 
DBCC FREESYSTEMCACHE('All') 
--清除所有缓存 
DBCC DROPCLEANBUFFERS