精华内容
下载资源
问答
  • 方便导出指定数据库的各表的字段名称类型。 方便导出指定数据库的各表的字段名称类型。 方便导出指定数据库的各表的字段名称类型。 方便导出指定数据库的各表的字段名称类型。 方便导出指定数据库的各表的字段名称...
  • SELECT t.TABLE_NAME,t.COLUMN_NAME,t.DATA_TYPE,t.DATA_LENGTH,c.COMMENTS FROM USER_TAB_COLUMNS t,user_col_comments c where t.TABLE_NAME=c.TABLE_NAME and t.COLUMN_NAME=c.COLUMN_NAME ...

     SELECT t.TABLE_NAME,t.COLUMN_NAME,t.DATA_TYPE,t.DATA_LENGTH,c.COMMENTS
      FROM USER_TAB_COLUMNS t,user_col_comments c 
      where t.TABLE_NAME=c.TABLE_NAME and t.COLUMN_NAME=c.COLUMN_NAME
      and t.TABLE_NAME in ('W_WORK_BASIS','W_SEND_WORK','W_ORDERS_WORK','W_DEAL_WORK','W_DEAL_WEIGHT');

    展开全文
  • WHERE d.name='表名称' --如果只查询指定表,加上此条件 order by a.id,a.colorder     SELECT CASE WHEN col.colorder = 1 THEN obj.name ELSE '' END AS 表名, col.colorder AS 序号 , col.name AS ...

    SELECT (case when a.colorder=1 then d.name else null end) 表名,
    a.colorder 字段序号,a.name 字段名,
    (case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) 标识,
    (case when (SELECT count(*) FROM sysobjects
    WHERE (name in (SELECT name FROM sysindexes
    WHERE (id = a.id) AND (indid in
    (SELECT indid FROM sysindexkeys
    WHERE (id = a.id) AND (colid in
    (SELECT colid FROM syscolumns WHERE (id = a.id) AND (name = a.name)))))))
    AND (xtype = 'PK'))>0 then '√' else '' end) 主键,b.name 类型,a.length 占用字节数,
    COLUMNPROPERTY(a.id,a.name,'PRECISION') as 长度,
    isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as 小数位数,(case when a.isnullable=1 then '√'else '' end) 允许空,
    isnull(e.text,'') 默认值,isnull(g.[value], ' ') AS [说明]
    FROM syscolumns a
    left join systypes b on a.xtype=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.class and f.minor_id=0
    --where b.name is not null
    WHERE d.name='表名称' --如果只查询指定表,加上此条件
    order by a.id,a.colorder

     

     

    SELECT CASE WHEN col.colorder = 1 THEN obj.name
    ELSE ''
    END AS 表名,
    col.colorder AS 序号 ,
    col.name AS 列名 ,
    t.name AS 数据类型 ,
    col.length AS 长度 ,
    ISNULL(COLUMNPROPERTY(col.id, col.name, 'Scale'), 0) AS 小数位数 ,
    CASE WHEN EXISTS ( SELECT 1
    FROM dbo.sysindexes si
    INNER JOIN dbo.sysindexkeys sik ON si.id = sik.id
    AND si.indid = sik.indid
    INNER JOIN dbo.syscolumns sc ON sc.id = sik.id
    AND sc.colid = sik.colid
    INNER JOIN dbo.sysobjects so ON so.name = si.name
    AND so.xtype = 'PK'
    WHERE sc.id = col.id
    AND sc.colid = col.colid ) THEN '主键'
    ELSE ''
    END AS 主键 ,
    CASE WHEN col.isnullable = 1 THEN '是'
    ELSE '否'
    END AS 允许空 ,
    ISNULL(comm.text, '') AS 默认值
    FROM dbo.syscolumns col
    LEFT JOIN dbo.systypes t ON col.xtype = t.xusertype
    inner JOIN dbo.sysobjects obj ON col.id = obj.id
    AND obj.xtype = 'U'
    AND obj.status >= 0
    LEFT JOIN dbo.syscomments comm ON col.cdefault = comm.id
    LEFT JOIN sys.extended_properties ep ON col.id = ep.major_id
    AND col.colid = ep.minor_id
    AND ep.name = 'MS_Description'
    LEFT JOIN sys.extended_properties epTwo ON obj.id = epTwo.major_id
    AND epTwo.minor_id = 0
    AND epTwo.name = 'MS_Description'
    WHERE obj.name in (SELECT name FROM SysObjects Where XType='U' )--表名

     

     

     

     

     

     


    SELECT
    a.colorder 序号,
    a.name 字段名,
    isnull(g.[value], ' ') AS 字段名称,
    b.name 类型,
    (case when (SELECT count(*) FROM sysobjects
    WHERE (name in (SELECT name FROM sysindexes
    WHERE (id = a.id) AND (indid in
    (SELECT indid FROM sysindexkeys
    WHERE (id = a.id) AND (colid in
    (SELECT colid FROM syscolumns WHERE (id = a.id) AND (name = a.name)))))))
    AND (xtype = 'PK'))>0 then '是' else '' end) 主键,
    (case when a.isnullable=1 then '是'else '否' end) 允许空,
    '' 备注
    FROM syscolumns a
    left join systypes b on a.xtype=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.class and f.minor_id=0
    --where b.name is not null
    WHERE d.name='ReturnStatus' --如果只查询指定表,加上此条件
    order by a.id,a.colorder

    转载于:https://www.cnblogs.com/niuniu0108/p/9753633.html

    展开全文
  • -- 数据字典 SELECT  (case when a.colorder=1 then d.name else '' end)表名,  a.colorder 字段序号,  a.name 字段名,  (case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else
    -- 数据字典
    SELECT
            (case when a.colorder=1 then d.name else '' end)表名,
            a.colorder 字段序号,
            a.name 字段名,
            (case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) 标识,
           (case when (SELECT count(*)
            FROM sysobjects
            WHERE (name in
                    (SELECT name
                    FROM sysindexes
                    WHERE (id = a.id) AND (indid in
                             (SELECT indid
                            FROM sysindexkeys
                            WHERE (id = a.id) AND (colid in
                                      (SELECT colid
                                     FROM syscolumns
                                     WHERE (id = a.id) AND (name = a.name))))))) AND
                (xtype = 'PK'))>0 then '√' else '' end) 主键,
           b.name 类型,
           a.length 占用字节数,
          COLUMNPROPERTY(a.id,a.name,'PRECISION') as 长度,
          isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as 小数位数,
          (case when a.isnullable=1 then '√'else '' end) 允许空,
          isnull(e.text,'') 默认值,
          isnull(g.[value],'') AS 字段说明
    FROM  syscolumns  a left join systypes b
    on  a.xtype=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
    order by a.id,a.colorder
    展开全文
  • select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=‘StudentTable’

    select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=‘StudentTable’

    展开全文
  • --查询语句 SELECT [Table Name] = OBJECT_NAME(col.object_id), [Column Name] = col.name, [Description] = ex.value FROM sys.columns col LEFT OUTER JOIN sys.extended_properties ex O...
  • select b.name,a.name,c.name,a.xprec,a.xscalefrom syscolumns aleft outer join sysobjects b ON a.id=b.id left outer join systypes c ON c.xusertype=a.xusertype where b.xtype='U' AND b.name LIKE '%C...
  • 1、mysql查询数据表字段信息 SELECT TABLE_NAME, column_name, DATA_TYPE, column_comment FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME='sys_user'; 注意:TABLE_SCHEMA 是...
  • --查询数据库中被锁住的表 Select c.sid,c.serial#,d.name,b.object_name,c.username,c.program,c.osuser from gv$Locked_object a, All_objects b, gv$session c, audit_actions d where a.object_id = b.object...
  • 查询数据库表schedule中cmId字段重复信息的sql如下: select cmId,count(cmId)as total_count from schedule group by cmId having count(cmId) > 1
  • ALTER PROC [dbo].[sp_help_table] @tableName NVARCHAR(50) AS BEGIN SELECT a.colorder 字段序号,a.name 字段名,b.name 字段类型,b.length AS 字段长度,g.[value] AS 字段说明 FROM syscolumns a left ...
  • 1、查询字段类型 select b.name as tablename,a.name as columnname,c.name as typename,a.max_length as typelength from sys.columns a inner join sys.tables b on b.object_id=a.object_id inner join sys....
  • if not exists (select NAME from @TempTable where len(NAME)>0 group by NAME) --查询字段值全为空或NULL的列 23 if exists ( select count ( 1 ) from @TempTable where isnull (NAME, '' ) ...
  • 数据库表字段命名规范

    千次阅读 2018-09-22 14:43:16
    在后续开发使用原来数据库表时,也会因为数据库表的可读性不够高,表字段规则不统一,造成数据查询,数据使用效率低的问题,所以有必要整理出一套合适的数据库表字段命名规范来解决优化这些问题。 本文是一篇包含了...
  • 查询数据库db_tmp_bookstore中所有表的表字段信息: SELECT table_name as 表, COLUMN_NAME 列名, COLUMN_TYPE 数据类型, DATA_TYPE 字段类型, CHARACTER_MAXIMUM_LENGTH 长度, IS_NULLABLE 是否为空, ...
  • [数据库] 数据库表字段命名规范

    千次阅读 2018-07-23 11:21:02
    在后续开发使用原来数据库表时,也会因为数据库表的可读性不够高,表字段规则不统一,造成数据查询,数据使用效率低的问题,所以有必要整理出一套合适的数据库表字段命名规范来解决优化这些问题。 本文是一篇包含了...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 1,285
精华内容 514
关键字:

查询数据库表字段