新增文章

文章标题分类内容

------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--【视图】 删除

if exists (select * from sys.views where object_id = OBJECT_ID(N'dbo.a_TableField'))
              drop     view                                         dbo.a_TableField
   go

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--【视图】 a_TableField  数据表字段

create view dbo.a_TableField
   as 
   select 
          a1.xtype                  -- 数据类型
          
         ,a1.name    FTableName     -- 数据表名
         
         ,a2.colid
         ,a2.name    FFieldName     -- 字段名称
         
         ------------------------------------------------------------------------------------------------------------------------------------------------------------
         ,(CASE  WHEN a3.name IN ( 'varchar','nvarchar','char','nchar' ) THEN a3.name +'('+ CONVERT(varchar(10),a2.prec) +')'
                 WHEN a3.name IN ( 'decimal'                           ) THEN a3.name +'('+ CONVERT(varchar(10),a2.prec) +','+ CONVERT(varchar(10),a2.scale) +')'
                 ELSE a3.name END) 
         
         +(CASE  WHEN a2.isnullable = 0 THEN '' ELSE '  Not Null' END)   FDataType  -- 数据类型
         
         ------------------------------------------------------------------------------------------------------------------------------------------------------------
         ,a3.name          FDataName      -- 字段类型
         ,a2.length        FDataLength    -- 字段长度
         ,a2.prec          FDataPrec      -- 字段精度
         
         ,(CASE  WHEN a2.scale IS NULL THEN '' ELSE CONVERT(varchar(10),a2.scale) END)  FDataScale  -- 小数位数
         
         ,a2.isnullable    FDataNull        -- 允许为空
         
         ,a1.crdate        FCreateDate    -- 创建日期
         
   from sysobjects  a1
   join syscolumns  a2 on a2.id        = a1.id
   join systypes    a3 on a3.xusertype = a2.xusertype
   
/*
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   
   select * from a_TableField   where FTableName LIKE '%t_ICItemCore%'  order by FCreateDate,FTableName,colid   --【数据表】【字段信息】
   
------------------------------------------------------------------------------------------------------------------------------------------------------------------------

select * from sysobjects  order by crdate DESC
   select * from syscolumns  order by id DESC
   select * from systypes    order by xusertype 
   
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
*/
go