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

1 --表重命名exec sp_rename ‘stu‘, ‘stud‘;select * from stud;
2  --列重命名exec sp_rename ‘stud.name‘, ‘sName‘, ‘column‘;exec sp_help ‘stud‘;
3 --重命名索引exec sp_rename N‘student.idx_cid‘, N‘idx_cidd‘, N‘index‘;exec sp_help ‘student‘; 
4 --查询所有存储过程select * from sys.objects where type = ‘P‘;select * from sys.objects where type_desc like ‘%pro%‘ and name like ‘sp%‘;

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

1 --带参存储过程if (object_id(‘proc_find_stu‘, ‘P‘) is not null)    
2 drop proc proc_find_stugocreate proc proc_find_stu(@startId int, @endId int)as    
3 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%‘;   

1 if (object_id(‘proc_getStudentRecord‘, ‘P‘) is not null)   
2  drop proc proc_getStudentRecordgocreate proc proc_getStudentRecord(   
3 @id int, --默认输入参数   
4 @name varchar(20) out, --输出参数   
5  @age varchar(20) output--输入输出参数)as   
6  select @name = name, @age = age 
7  from student where id = @id and sex = @age; go-- declare @id int,        @name varchar(20),        
8 @temp varchar(20); set @id = 7; set @temp = 1; exec proc_getStudentRecord @id,  @name out, @temp output;select @name, @temp;print @name + ‘#‘ + @temp; 

 1 if (object_id(‘proc_cursor‘, ‘P‘) is not null)   
 2  drop proc proc_cursorgocreate proc proc_cursor    
 3 @cur cursor varying outputas    
 4 set @cur = cursor forward_only static for   
 5  select id, name, age from student;    
 6 open @cur;go
 7 --调用declare @exec_cur cursor;declare @id int,       
 8  @name varchar(20),       
 9  @age int;exec proc_cursor @cur = @exec_cur output; 
10 --调用存储过程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); 
11 endclose @exec_cur;deallocate @exec_cur;--删除游标  

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