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

--表重命名exec sp_rename ‘stu‘, ‘stud‘;select * from stud;--列重命名exec sp_rename ‘stud.name‘, ‘sName‘, ‘column‘;exec sp_help ‘stud‘;--重命名索引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%‘;

create proc | procedure pro_name    [{@参数数据类型} [=默认值] [output],     {@参数数据类型} [=默认值] [output],     ....    ]as    SQL_statements

--创建存储过程if (exists (select * from sys.objects where name = ‘proc_get_student‘))    drop proc proc_get_studentgocreate proc proc_get_studentas    select * from student;--调用、执行存储过程exec proc_get_student;

--修改存储过程alter proc proc_get_studentasselect * from student;

--带参存储过程if (object_id(‘proc_find_stu‘, ‘P‘) is not null)    drop proc proc_find_stugocreate proc proc_find_stu(@startId int, @endId int)as    select * from student where id between @startId and @endIdgoexec proc_find_stu 2, 4;

--带通配符参数存储过程if (object_id(‘proc_findStudentByName‘, ‘P‘) is not null)    drop proc proc_findStudentByNamegocreate proc proc_findStudentByName(@name varchar(20) = ‘%j%‘, @nextName varchar(20) = ‘%‘)as    select * from student where name like @name and name like @nextName;goexec proc_findStudentByName;exec proc_findStudentByName ‘%o%‘, ‘t%‘;

if (object_id(‘proc_getStudentRecord‘, ‘P‘) is not null)    drop proc proc_getStudentRecordgocreate proc proc_getStudentRecord(    @id int, --默认输入参数    @name varchar(20) out, --输出参数    @age varchar(20) output--输入输出参数    @sex int ,--默认输入参数)as    --用select 给变量@name,@age 赋值。赋的值为从student表中查出的name,和age字段    select @name = name, @age = age  from student where id = @id and sex = @sex;    --也可以写成:set @name=(select name from student where id=@id and sex=@sex)    --          set @age=(select age from student where id=@id and sex=@sex)go---------------------------------------------------------------------------------------带参数的存储过程调用如下： declare @id int,                         -----先声明参数        @name varchar(20),        @temp varchar(20);        @sex intset @id = 7;                 --再给参数赋值set @sex=0;set @temp = 1;exec proc_getStudentRecord @id, @name out, @temp output,@sex;           --调用存储过程select @name, @temp;--打印出@name,@temp

create proc p_xs_sum(    @xh int,    @kcm nvarchar(20),    @sum int output)asselect @sum=总学分 from xs join xs_kc join kc  on xs_kc.课程号=kc.课程号 on xs.学号=xs_kc.学号 where xs.学号=@xh  and 课程名=@kcmgo------------------------存储过程的调用declare @xh int,        @kcm nvarchar(20),        @sum int        set @xh=‘001101‘;set @kcm=‘离散数学‘;        exec p_xs_sum @xh,@kcm, @sum outprint @sum 

--WITH RECOMPILE 不缓存if (object_id(‘proc_temp‘, ‘P‘) is not null)    drop proc proc_tempgocreate proc proc_tempwith recompileas    select * from student;goexec proc_temp;

--加密WITH ENCRYPTION if (object_id(‘proc_temp_encryption‘, ‘P‘) is not null)    drop proc proc_temp_encryptiongocreate proc proc_temp_encryptionwith encryptionas    select * from student;goexec proc_temp_encryption;exec sp_helptext ‘proc_temp‘;exec sp_helptext ‘proc_temp_encryption‘;

if (object_id(‘proc_cursor‘, ‘P‘) is not null)    drop proc proc_cursorgocreate proc proc_cursor    @cur cursor varying outputas    set @cur = cursor forward_only static for    select id, name, age from student;    open @cur;go--调用declare @exec_cur cursor;declare @id int,        @name varchar(20),        @age int;exec proc_cursor @cur = @exec_cur output;--调用存储过程fetch next from @exec_cur into @id, @name, @age;while (@@fetch_status = 0)begin    fetch next from @exec_cur into @id, @name, @age;    print ‘id: ‘ + convert(varchar, @id) + ‘, name: ‘ + @name + ‘, age: ‘ + convert(char, @age);endclose @exec_cur;deallocate @exec_cur;--删除游标

---存储过程、row_number完成分页if (object_id(‘pro_page‘, ‘P‘) is not null)    drop proc proc_cursorgocreate proc pro_page    @startIndex int,    @endIndex intas    select count(*) from product;        select * from (        select row_number() over(order by pid) as rowId, * from product     ) temp    where temp.rowId between @startIndex and @endIndexgo--drop proc pro_pageexec pro_page 1, 4----分页存储过程if (object_id(‘pro_page‘, ‘P‘) is not null)    drop proc pro_stugocreate procedure pro_stu(    @pageIndex int,    @pageSize int)as    declare @startRow int, @endRow int    set @startRow = (@pageIndex - 1) * @pageSize +1    set @endRow = @startRow + @pageSize -1    select * from (        select *, row_number() over (order by id asc) as number from student     ) t    where t.number between @startRow and @endRow;exec pro_stu 2, 2;

Raiserror({msg_id | msg_str | @local_variable}  {, severity, state}  [,argument[,…n]]  [with option[,…n]])

raiserror(‘is error‘, 16, 1);select * from sys.messages;--使用sysmessages中定义的消息raiserror(33003, 16, 1);raiserror(33006, 16, 1);