06 视图

/* 视图 View 视图可以被看成虚拟表或存储查询。可通过视图访问数据库中的数据，返回的数据可以是一个表 的部分数据，也可以是多个表的联合数据。 1）安全性，可以屏蔽表的字段名 2）访问限制，可以将用户的查询限制在某几列上 3）简化查询，将多个表的查询结果封装在一个虚拟表上 视图的使用就如同表一个样，一般只对视图做查询操作，增、删、改的操作一般不用 create view v_name with encryption as selectstatement with check option 加密视图创建文本 with encryption 更新视图条件 with check option */ --单表全部字段视图 create view v01 as select * from t1 go --单全部分字段视图 create view v02(猫, 狗) as select sid,tid from t1 go --使用视图 select * from v02 go -- create view v_01(姓名,电子邮箱,出生日期) as select name,email,birthday from person go --查看视图创建文本 sp_helptext v_01 --对创建视图文本进行加密 with encryption create view v_01(姓名,电子邮箱,出生日期) with encryption as select name,email,birthday from person go sp_helptext v_01 --创建只读视图 (利用触发器来做) create view v_person as select * from person go create trigger tr_v$person on v_person instead of insert,update,delete as print '视图v_person只读' go select * from v_person delete from v_person where id=4 --删除视图 drop trigger tr_v$person --更新视图 (针对单表视图) create view v_p(姓名,邮箱,出生日期) as select name,email,birthday from person where datediff(yy,getdate(),birthday) between -5 and 5 with check option go --当前getdate()为2009-12-14 insert into person(name,email,birthday) values('测试1','test1@163.com','2004-12-14') insert into person(name,email,birthday) values('测试2','test2@163.com','2005-12-14') insert into person(name,email,birthday) values('测试3','test3@163.com','2013-12-14') insert into person(name,email,birthday) values('测试4','test4@163.com','2014-12-14') update v_p set 姓名='test' select * from person --视图绑定索引1)不能用* 2)对象名由两部分组成dbo.t1 1)视图绑定架构 create view v001 with schemabinding as select sid,tid from dbo.t1 go 2)创建索引 create unique clustered index in01 on v001(sid) go

 

常见的系统视图

 

/************************************************* Microsoft SQL Server 2005 系统视图 *************************************************/ /* 一、兼容性视图：为了向后兼容而保留下来的一些视图 兼容性视图名写的时候前面"sys."可加可不加，下面 统一都不加 */ --1、sysservers 查询所有本地服务器及链接服务器 select * from sysservers go --2、sysdatabases 查询本地所有数据库 --查询非sa创建的所有数据库 select dbid,name from sysdatabases where sid<>0x01 go --或者 select dbid,name from sysdatabases where sid not in (select sid from syslogins where name='sa') go --3、sysobjects --获取当前数据库中所有用户表 select name from sysobjects where xtype='U' and status>0 go --加status>0的原因是，因为表dtproperties的xtype为U，但实质上它是系统表 。dtproperties保存的是关系图，如果没有建关系图，它是空的。 --查询当前数据库中所有的存储过程 select name from sysobjects where xtype='P' and status>0 go --加status>0的原因是去除系统存储过程 --注意：type与xtype的区别，type保留的原因是与后版本兼容 --4、syscolumns 获取表或视图的所有字段、存储过程或函数的所有参数 select name from syscolumns where id=object_id('表名') go --5、sysindexes --根据聚集索引，快速查询表的行数 select rowcnt,indid from sysindexes where id=object_id('tablename') and indid<2 go --查看索引表信息 select table_name = sysobjects.name, index_name = sysindexes.name, type = sysobjects.type, 分配索引页= sysindexes.reserved, 使用索引页= sysindexes.used, 叶子层页= sysindexes.dpages, 非叶子层页= sysindexes.used-sysindexes.dpages, rows = sysindexes.rowcnt from sysindexes left outer join sysobjects on sysindexes.id = sysobjects.id where sysindexes.indid>0 and sysindexes.indid<255 and sysindexes.status & 64=0 go --若发现非叶子层的页数为负数，最好先更新sysindexes的信息 --decc updateusage ('dbname','tbname','ixname') --6、syslogins 服务器登录信息 select * from syslogins go --7、sysprocesses --查看用户进程信息 select spid,uid,syslogins.name,login_time,net_address from sys.sysprocesses ,syslogins where sys.sysprocesses.sid=syslogins.sid go --查看数据库启动时间 select convert(varchar(30),login_time,120) from sys.sysprocesses where spid=1 go --8、sysdepends 查询与一个表相关的视图、存储过程、函数 select * from sysdepends where depid=object_id('tablename') go --或： select a.* from sysobjects a ,syscomments b where a.id=b.id and b.text like '%tablename%' go --9、sysmessages 返回sqlserver的内部错误 select * from sysmessages where error=5037 go --10、sysfile、sysfilegroups --查询当前数据库的文件使用情况 select name,filename,size/128 'used(M)', (case maxsize/128 when 0 then 'no limit' else cast(maxsize/128 as varchar(10)) end) 'total(M)' from sysfiles go --查询当前数据库的表所在文件组 select distinct a.id,a.name,b.groupid,c.groupname from sysobjects a left join sysindexes b on a.id=b.id left join sysfilegroups c on b.groupid=c.groupid where a.xType='U' and a.status>0 order by a.name go 二、目录视图：SQL Server 2005中建议用这种方式来获得元数据 下面着重了解一下对象目录视图 sys.allocation_units :数据库中的每个分配单元都在表中占一行 。 sys.numbered_procedures :带编号的过程而创建的 SQL Server 存储 过程 sys.assembly_modules :为公共语言运行时 (CLR) 程序集所定义的 每个函数、过程或触发器返回一行。 sys.numbered_procedure_parameters:带编号过程的每个参数都在表中对应一行 。 sys.check_constraints :CHECK 约束（sys.objects.type = C）的 对象都在表中占一行 sys.objects :在数据库中创建的每个用户定义的架构范 围内的对象在该表中均对应一行。 sys.columns :为包含列的对象（如视图或表）的每列返 回一行。 sys.parameters :接受参数的对象的每个参数在表中对应一 行。 sys.computed_columns :sys.columns 中的每个计算列对应一行。 sys.partitions :数据库中所有表和索引的每个分区在表中 各对应一行。 sys.default_constraints :作为默认定义且 sys.objects.type = D 的每个对象在表中各对应一行，该默认定义是作为 CREATE TABLE 或 ALTER TABLE 语句的一部分创建的，而不是作为 CREATE DEFAULT 语句的一部分创建的 。 sys.procedures :属于同类过程并且 sys.objects.type = P 、X、RF 和 PC 的每个对象对应一行。 sys.events :导致触发器或事件通知激发的每个事件对 应一行。 sys.service_queues :数据库中每个作为服务队列的对象 （sys.objects.type = SQ）都在表中对应一行。 sys.event_notifications :为作为事件通知的每个对象返回一行，且 sys.objects.type = EN。 sys.sql_dependencies :在定义某些其他引用（相关）对象的 SQL 表达式或语句中进行引用时，被引用（独立）实体的每个相关性在表中均对应一 行。任何对象或列的定义属性包含 SQL 表达式时，都可以有相关性。 sys.extended_procedures :每个作为扩展存储过程且 sys.objects.type = X 的对象对应一行。因为扩展存储过程安装在 master 数 据库中，它们只有在该数据库上下文中才可见。从任何其他数据库上下文中的 sys.extended_procedures 视图中进行选择，将返回空的结果集。 sys.stats :U、V 或 TF 类型表格对象的每个统计信息 都对应一行。 sys.foreign_keys :每个作为 FOREIGN KEY 约束并且 sys.object.type = F 的对象都在表中占一行。 sys.stats_columns :sys.stats 统计信息包含的每列对应一行 。 sys.foreign_key_columns :组成外键的每一列或列集在表中对应一行 。 sys.sql_modules :每个 SQL 语言定义模块对象在表中对应一 行。type 为 P、RF、V、TR、FN、IF、TF、R 和 D 的对象均有关联的 SQL 模块 。 sys.fulltext_indexes :表对象的每个全文索引对应一行 sys.synonyms :在该视图中，sys.objects.type = SN 的 每个同义词对象对应一行 sys.fulltext_index_columns :构成全文索引的每列都对应一行 sys.tables :为每个表对象返回一行。当前仅用于 sys.objects.type = U 的表对象 sys.identity_columns :用作标识列的每列都在表中占一行 sys.views :sys.objects.type = V 的每个视图对象在 该表中对应一行 sys.indexes :每个表格对象（例如，表、视图或表值函 数）的索引或堆都包含一行 sys.triggers :每个类型为 TR 或 TA 的触发器对象对应 一行 sys.index_columns :属于 sys.indexes 索引或未排序的表（堆 ）的每个列都对应一行 sys.trigger_events :触发触发器的每个事件对应一行 sys.key_constraints :每个作为主键或唯一约束的对象对应一行 。包括 sys.objects.type PK 和 UQ sys.traces :sys.traces 目录视图包含当前在系统中运 行的跟踪