sqlserver元数据：视图、序列、函数和存储过程

环境：

• window10 x64 专业版
• sqlserver2014

一、视图

可以把视图看成一条查询语句

• 创建视图：

  IF EXISTS(SELECT * FROM sys.views WHERE name = N'v_test2')
      DROP view v_test2;
  GO
  create view v_test2 as
  select * from test2;
  

• 查看视图元数据：

  select infov.*,sysv.create_date,sysv.modify_date 
  from INFORMATION_SCHEMA.VIEWS infov 
  	left join sys.views sysv on infov.TABLE_NAME=sysv.name and infov.TABLE_SCHEMA=(select 	name from sys.schemas where schema_id=sysv.schema_id)
  where sysv.type='V'
  

  

二、序列

sqlserver2012中引入的功能，主要解决定制自增列的问题。

• 创建序列：

  IF EXISTS(SELECT * FROM sys.sequences WHERE name = N'TestSeq')
      DROP SEQUENCE TestSeq;
  GO
  --创建序列对象 
  CREATE SEQUENCE TestSeq AS TINYINT
      START WITH 1
      INCREMENT BY 1;
  

• 使用序列：

  --创建表
  CREATE TABLE TEST(ID tinyint primary key,  Name varchar(50))
  --产生序列号码并插入表中
  INSERT INTO TEST(ID,Name) VALUES(NEXT VALUE FOR TestSeq, 'allen')
  INSERT INTO TEST(ID,Name) VALUES(NEXT VALUE FOR TestSeq, 'kevin')
   
  SELECT * FROM TEST
  

  使用效果如下：

• 查询序列元数据：

  select 	infos.SEQUENCE_CATALOG,infos.SEQUENCE_SCHEMA,infos.SEQUENCE_NAME,infos.DATA_TYPE,infos.MINIMUM_VALUE,infos.MAXIMUM_VALUE,
  	infos.START_VALUE,infos.INCREMENT,infos.CYCLE_OPTION, syss.create_date,syss.modify_date 
  from INFORMATION_SCHEMA.SEQUENCES infos 
  	left join sys.sequences syss on infos.SEQUENCE_NAME=syss.name and infos.SEQUENCE_SCHEMA=(select name from sys.schemas where schema_id=syss.schema_id)
  where syss.type='SO'
  

  

三、函数

sqlserver中的函数分三类：标量函数；内联表值函数；多语句表值函数；

参考：《SQL server 自定义函数FUNCTION的使用》

• 创建并测试标量函数：

  -- 创建标量函数
  IF EXISTS(SELECT * FROM sys.objects WHERE name = N'func_scalar' and type in('FN','TF','IF'))
  	DROP function func_scalar;
  GO
  -- 创建标量函数
  create function func_scalar(@x1 int,@x2 int)
  returns int
  as
  begin
  	SET @x1=@x1+@X2
  	return @x1
  end
  GO
  --调用标量函数
  SELECT dbo.func_scalar(1,2)
  

  输出：
  

• 创建并测试表值函数：

  --准备表
  IF EXISTS(SELECT * FROM sys.tables WHERE name = N'test')
      DROP table test;
  GO
  create table test(id int primary key,name varchar(50))
  insert into test(id,name) values(1,'小明'),(2,'小花')
  GO
  
  IF EXISTS(SELECT * FROM sys.objects WHERE name = N'func_tablevalue' and type in('FN','TF','IF'))
  	DROP function func_tablevalue;
  GO
  
  CREATE FUNCTION func_tablevalue(@id int) 
  RETURNS table
  as
    return  select  * from test where id>@id
  GO
  
  --调用表值函数
  select * from func_tablevalue(1)
  

  输出：
  

• 创建并测试多表值函数：

  IF EXISTS(SELECT * FROM sys.objects WHERE name = N'func_multitablevalue' and type in('FN','TF','IF'))
  	DROP function func_multitablevalue;
  GO
  create function func_multitablevalue()
  returns @temp table (
  	name varchar(20),
  	sex char(2),
  	age int
  )
  as
  begin
  insert into @temp (name,sex,age) values ('多语句','嘛',18)
  return 
  end
  GO
  
  -- 调用多表值函数
  select * from func_multitablevalue()
  

  输出：
  

• 查询函数元数据：

  select syso.name,syso.type,syso.type_desc,syso.create_date,syso.modify_date,infor.ROUTINE_CATALOG,infor.ROUTINE_SCHEMA,infor.ROUTINE_DEFINITION
  from sys.objects syso left join INFORMATION_SCHEMA.ROUTINES infor on syso.name=infor.ROUTINE_NAME
  where type in ('FN','TF','IF')
  

  输出如下：
  

四、存储过程

参考：《SqlServer存储过程详解》

在sqlserver中，存储过程不同于函数，它们虽然长相类似，但有诸多不同，比如：函数中不能修改表数据，而存储过程可以等。。。

• 创建并测试存储过程：

  if (exists (select * from sys.procedures where name = 'usp_test'))
      drop proc usp_test
  go
  create proc usp_test
  @UserId int =1
  as
  set nocount on;
  begin
      select @UserId as id,18 as age
  end
  
  -- 调用存储过程
  exec usp_test 1
  

• 查询元数据：

  select infor.ROUTINE_CATALOG, infor.ROUTINE_SCHEMA,infor.ROUTINE_NAME,infor.ROUTINE_DEFINITION,infor.CREATED,infor.LAST_ALTERED
  from INFORMATION_SCHEMA.ROUTINES infor where ROUTINE_TYPE='PROCEDURE'
  

  输出：