5.www.9159.com:获取一个表的表名,一般情况下视图

作者: www.9159.com  发布:2019-11-22

1.获取数据库中的表

 

 在很多情况下,我们开发都需要有一个快速的代码生成工具用来提高开发效率,代码生成工具很多信息都是读取数据库的表、视图等元数据进行对象表信息的完善,有了这些信息,我们就可以在普通的实体类代码里面添加属性字段的中文注释,或者在Winform或者Web界面的快速生成的时候,可以在查询框或者界面编辑的时候,充当标签提示等处理信息。但是,一般情况下视图的备注信息是没有的,但是视图和表之间是存在一定的关系的,虽然不同数据库系统对于它们的信息对应不一定一致,但是我们可以把它们的对应关系寻找出来就可以给视图字段增加备注了,这个就是本文介绍的思路和做法了,本篇结合介绍代码生成工具Databae2Sharp的处理实现进行讲解如何获取视图中字段对应的备注信息。

select name from sysobjects where type='U'

推荐】Sql Server 不常见应用之一

1、SQLServer数据库的视图信息处理

我们为了演示视图的处理,我们需要创建两个表,以及视图的信息,首先我们创建一个客户信息表,如下所示。

www.9159.com 1

www.9159.com 2

我们希望这个T_Customer表中的Creator字段和用户表进行关联,然后我们添加一个视图,其中的视图代码如下所示。

SELECT     dbo.T_Customer.ID, dbo.T_Customer.Name, dbo.T_Customer.Age, dbo.T_Customer.Creator, dbo.T_Customer.CreateTime, dbo.Users.UserName
FROM         dbo.T_Customer INNER JOIN
dbo.Users ON dbo.T_Customer.Creator = dbo.Users.ID

也就是列出T_Customer的字段,以及一个Users表中的Username字段,相当于关联了两个表的视图,获取这个表的数据如下所示。

www.9159.com 3

好了,到这里我们基本就准备好前提条件了,剩下的就是如何获取这些视图的信息了。

在SQLServer里面,我们如果需要获取用户视图的信息,那么通过系统的视图就可以获取到了,如下所示。

www.9159.com 4

这个就是视图的名称和视图SQL代码了,我们如果需要详细获取视图的字段信息,这些还不够,我们可以根据视图信息进一步列出用户视图和具体表的关系,如下所示。

www.9159.com 5

从上图我们就可以看出这个视图字段和表的字段的一一对应关系了,也就是可以通过具体表的字段信息,包括我们这里非常关注的备注信息了。

例如根据这些原理,我们视图就直接从数据库表字段中提取备注信息了,从而实现视图备注字段的自动提取,减少人工设置的繁琐和不便。

www.9159.com 6

 

2.获取表字段(此处是Route表)

获取表的基本信息、字段列表、存储过程参数列表

2、Oracle数据库视图信息的处理

在Oracle里面的视图字段信息处理,和SQLServer有所不同,因为我们很难找到视图字段和表字段的一一对应关系的视图信息,因此有点困难。

在Oracle里面我们先准备好一个T_Customer表,以及视图等基础信息先。

www.9159.com 7

同时创建一个视图和用户表发生关联,和在SQLServer一样的做法,SQL如下所示。

create or replace view view_customer as
select u.fullname, t.id, t.name, t.age, t.creator, t.createtime from t_customer t inner join t_acl_user u on t.creator = u.id;

我们通过Oracle的系统视图ALL_TAB_COLUMNS或者DBA_TAB_COLUMNS可以获得视图的字段信息,如下所示。

www.9159.com 8

不过很不幸,这个视图的信息没有和视图关联表发生任何的交集,无法获取到对应的表信息,那么如何获取视图对应表的信息呢,答案是可以通过ALL_DEPENDENCIES这个视图进行获取,如下图所示。

www.9159.com 9

但是这里也只是获取到对应的表,没有和字段关联,不过这样基本上也差不多了,因为我们可以获得视图字段,以及这个视图里面包含的关联表,那么基本上它的字段信息也大致锁定了,除非两个表之间很多字段名称相同,难以处理。

结合下面的SQL,我们可以获得表和字段的信息了,如下所示。

select distinct table_name, column_name from all_tab_columns where table_name in 
 (select referenced_name from all_dependencies where name='VIEW_CUSTOMER' and type = 'VIEW' and referenced_type = 'TABLE')  
  and column_name in (select column_name from all_tab_columns where table_name='VIEW_CUSTOMER') order by table_name; 

www.9159.com 10

这样我们根据视图对应表的字段信息,匹配它的备注信息就差不多了,最后我们来看看基于Oracle的实现效果。

www.9159.com 11

www.9159.com 12

从上图我们可以看到,代码工具Database2Sharp,根据上述介绍的逻辑关系,自动从表对应的字段里面去匹配对应的备注信息,从而减少手工设置视图字段备注信息的繁琐工作,为视图代码生成和界面生成提高了速度和效率。

以上的视图备注信息的提取逻辑,我们可以把它应用在自己的业务处理里面,实现更加广泛的应用。

以上视图备注信息的提取需求,来自一个客户的建议,非常不错,在此感谢他为这个细节做出的贡献。

Select name from syscolumns Where ID=OBJECT_ID('Route')

 

3.获取表中字段的描述

——通过知识共享树立个人品牌。

SELECT   value  FROM   ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', 'Route', 'column', default)

 

4.获取表的信息的所有信息如下图

  

select   b.[value] from sys.columns a left join sys.extended_properties b on a.object_id=b.major_id
and a.column_id=b.minor_id inner join sysobjects c on a.column_id=c.id
and a.[name]='列名' and c.[name]='表名'
SELECT
表名=case   when   a.colorder=1   then   d.name   else   ''   end,
表说明=case   when   a.colorder=1   then   isnull(f.value,'')   else   ''   end,
字段序号=a.colorder,
字段名=a.name,
标识=case   when   COLUMNPROPERTY(   a.id,a.name,'IsIdentity')=1   then   '√'else   ''   end,
主键=case   when   exists(SELECT   1   FROM   sysobjects   where   xtype='PK'   and   name   in   (
SELECT   name   FROM   sysindexes   WHERE   indid   in(
SELECT   indid   FROM   sysindexkeys   WHERE   id   =   a.id   AND   colid=a.colid
)))   then   '√'   else   ''   end,
类型=b.name,
占用字节数=a.length,
长度=COLUMNPROPERTY(a.id,a.name,'PRECISION'),
小数位数=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
允许空=case   when   a.isnullable=1   then   '√'else   ''   end,
默认值=isnull(e.text,''),
字段说明=isnull(g.[value],'')
FROM   syscolumns   a
left   join   systypes   b   on   a.xusertype=b.xusertype
inner   join   sysobjects   d   on   a.id=d.id     and   d.xtype='U'   and     d.name<>'dtproperties'
left   join   syscomments   e   on   a.cdefault=e.id
left   join   sys.extended_properties   g   on   a.id=g.major_id   and   a.colid=g.minor_id
left   join   sys.extended_properties   f   on   d.id=f.major_id   and   f.minor_id=0
--where   d.name='orders'         --如果只查询指定表,加上此条件
order   by   a.id,a.colorder

 

 

如下图

一、获取表的基本信息

www.9159.com 13

SELECT [TableName] = [Tables].name ,

        [TableOwner] = [Schemas].name ,

        [TableCreateDate] = [Tables].create_date ,

        [TableModifyDate] = [Tables].modify_date

FROM    sys.tables AS [Tables]

        INNER JOIN sys.schemas AS [Schemas] ON [Tables].schema_id = [Schemas].schema_id

WHERE   [Tables].name = '案卷目录'

 

 

5.获取一个表的表名,字段名,数据类型,字段说明的简写SQL:

效果如图一所示:

SELECT 
    --表名
    [TableName] = i_s.TABLE_NAME, 
    --列名
    [ColumnName] = i_s.COLUMN_NAME, 
    --字段描述
    [Description] = s.value,
    --数据类型
    [DataType]=i_s.DATA_TYPE ,
    --是否是主键
    [IsPrimaryKey]=case when exists(SELECT 1 FROM sysobjects where xtype='PK' and name in (
                SELECT   name   FROM   sysindexes   WHERE   indid   in(
                    SELECT   indid   FROM   sysindexkeys   WHERE   id   =   a.id   AND   colid=a.colid
                )))   then   '1'   else   '0'   end
    FROM 
    INFORMATION_SCHEMA.COLUMNS i_s 
    LEFT OUTER JOIN sys.extended_properties s ON 
                        s.major_id = OBJECT_ID(i_s.TABLE_SCHEMA+'.'+i_s.TABLE_NAME) 
                       AND s.minor_id = i_s.ORDINAL_POSITION
                        AND s.name = 'MS_Description' 
    INNER JOIN     syscolumns a ON a.id=s.major_id AND a.colid=s.minor_id

    WHERE OBJECTPROPERTY(OBJECT_ID(i_s.TABLE_SCHEMA+'.'+i_s.TABLE_NAME), 'IsMsShipped')=0 
          AND i_s.TABLE_NAME = 'BusRoute' AND i_s.TABLE_SCHEMA='office'
          ORDER BY i_s.TABLE_NAME, i_s.ORDINAL_POSITION

 

 

www.9159.com 14

 

 

 

 

 

 

图一 指定表的基本信息

 

二、根据表名获取字段列表

 

SELECT [ColumnName] = [Columns].name ,

        [SystemTypeName] = [Types].name ,

        [Precision] = [Columns].precision ,

        [Scale] = [Columns].scale ,

        [MaxLength] = [Columns].max_length ,

        [IsNullable] = [Columns].is_nullable ,

        [IsRowGUIDCol] = [Columns].is_rowguidcol ,

        [IsIdentity] = [Columns].is_identity ,

        [IsComputed] = [Columns].is_computed ,

        [IsXmlDocument] = [Columns].is_xml_document ,

        [Description] = [Properties].value

FROM    sys.tables AS [Tables]

        INNER JOIN sys.columns AS [Columns] ON [Tables].object_id = [Columns].object_id

        INNER JOIN sys.types AS [Types] ON [Columns].system_type_id = [Types].system_type_id

                                           AND is_user_defined = 0

                                           AND [Types].name <> 'sysname'

        LEFT OUTER JOIN sys.extended_properties AS [Properties] ON [Properties].major_id = [Tables].object_id

                                                              AND [Properties].minor_id = [Columns].column_id

                                                              AND [Properties].name = 'MS_Description'

WHERE   [Tables].name =案卷目录

ORDER BY [Columns].column_id

 

效果如图二所示:

www.9159.com 15

 

 

 

 

图二 指定表的字段列表信息

 

 

三、获取指定存储过程参数列表

 

SELECT sc.name AS 参数名 ,

        st.name AS 类型 ,

        sc.length AS 长度

FROM    syscolumns sc

        INNER JOIN sysobjects so ON so.id = sc.id

        INNER JOIN systypes st ON sc.xtype = st.xtype

WHERE   so.name = 'SP_Pagination'

效果如图三所示: 

www.9159.com 16

图三 指定存储过程的参数列表

本文由9159.com发布于www.9159.com,转载请注明出处:5.www.9159.com:获取一个表的表名,一般情况下视图

关键词: