精华内容
下载资源
问答
  • SELECT 主键列ID=b.rkey ,主键列名=(SELECT name FROM syscolumns WHERE colid=b.rkey AND id=b.rkeyid) ... ,外键表ID=b.fkeyid ,外键表名称=object_name(b.fkeyid) ,外键列ID=b.fkey ,外键列名...
    SELECT 主键列ID=b.rkey   
      ,主键列名=(SELECT name FROM syscolumns WHERE colid=b.rkey AND id=b.rkeyid)   
        ,外键表ID=b.fkeyid   
        ,外键表名称=object_name(b.fkeyid)   
        ,外键列ID=b.fkey   
        ,外键列名=(SELECT name FROM syscolumns WHERE colid=b.fkey AND id=b.fkeyid)   
        ,级联更新=ObjectProperty(a.id,'CnstIsUpdateCascade')   
        ,级联删除=ObjectProperty(a.id,'CnstIsDeleteCascade')   
    FROM sysobjects a   
        join sysforeignkeys b on a.id=b.constid   
        join sysobjects c on a.parent_obj=c.id   
    where a.xtype='f' AND c.xtype='U'   
        and object_name(b.rkeyid)='TABLE_PMD_TPP_TRAITS'
    
    展开全文
  • SELECT 主键列ID=b.rkey ,主键列名=(SELECT name FROM syscolumns WHERE colid=b.rkey AND id=b.rkeyid) ... ,外键表ID=b.fkeyid ,外键表名称=object_name(b.fkeyid) ,外键列ID=b.fkey ,外键列名...
    SELECT 主键列ID=b.rkey   
      ,主键列名=(SELECT name FROM syscolumns WHERE colid=b.rkey AND id=b.rkeyid)   
        ,外键表ID=b.fkeyid   
        ,外键表名称=object_name(b.fkeyid)   
        ,外键列ID=b.fkey   
        ,外键列名=(SELECT name FROM syscolumns WHERE colid=b.fkey AND id=b.fkeyid)   
        ,级联更新=ObjectProperty(a.id,'CnstIsUpdateCascade')   
        ,级联删除=ObjectProperty(a.id,'CnstIsDeleteCascade')   
    FROM sysobjects a   
        join sysforeignkeys b on a.id=b.constid   
        join sysobjects c on a.parent_obj=c.id   
    where a.xtype='f' AND c.xtype='U'   
        and object_name(b.rkeyid)='TABLE_PMD_TPP_TRAITS'

     

    展开全文
  • /**************************系统数据库中查询表的所有字段以及描述(2008,2000区分)以及查询表的外键***************************/----2008下-------方法一----表的扩展属性01------SELECT *FROM ::fn_...

    /**************************
    系统数据库中查询表的所有字段以及描述(2008,2000区分)以及查询表的外键

    ***************************/
    ----2008下

    -------方法一----表的扩展属性01------
    SELECT   *
    FROM   ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', 't_bill_in', 'column', default)

     

    SELECT
        CAST(value AS nvarchar(200)) as tableDescription
        FROM fn_listextendedproperty ('MS_Description', 'user', 'dbo', 'table', 'T_Bill_Cedula_Detail', default, default);

     

     


    -----方法二----表的扩展属性描述-----
    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 parent_obj=a.id 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
        dbo.dtproperties  g
    on
        a.id=g.id and a.colid=g.objectid 
    left join
        dbo.dtproperties  f
    on
        d.id=f.id and f.objectid=0
    where
        d.name='要查询的表'    --如果只查询指定表,加上此条件
    order by
        a.id,a.colorder

    ----方法03----表字段的描述(简易)
    Select
    col.[name]  as '字段名',  
    col.[length]as '长度'  ,
     type.[name] as '类型'  , 
     pro.value   as '描述' 
     From syscolumns as col 
    Left Join systypes as type on col.xtype = type.xtype 
    Left Join dbo.dtproperties as pro on col.id = pro.id and col.colid = pro.objectid  
    where col.id = (Select id From Sysobjects Where name = 'T_DeptClass')

     

     

     


    ---2000下------------------------
    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 parent_obj=a.id 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
        sysproperties g
    on
        a.id=g.id and a.colid=g.smallid 
    left join
        sysproperties f
    on
        d.id=f.id and f.smallid=0
    where
        d.name='要查询的表'    --如果只查询指定表,加上此条件
    order by
        a.id,a.colorder

    =-----------方法02---表的描述2000下----
    Select
    col.[name]  as '字段名',  
    col.[length]as '长度'  ,
     type.[name] as '类型'  , 
     pro.value   as '描述' 
     From syscolumns as col 
    Left Join systypes as type on col.xtype = type.xtype 
    Left Join sysProperties as pro on col.id = pro.id and col.colid = pro.smallid 
    where col.id = (Select id From Sysobjects Where name = 'T_DeptClass')

     

     

     


    -----------------查询一个表的所有外键
    SELECT 主键列ID=b.rkey
        ,主键列名=(SELECT name FROM syscolumns WHERE colid=b.rkey AND id=b.rkeyid)
        ,外键表ID=b.fkeyid
        ,外键表名称=object_name(b.fkeyid)
        ,外键列ID=b.fkey
        ,外键列名=(SELECT name FROM syscolumns WHERE colid=b.fkey AND id=b.fkeyid)
        ,级联更新=ObjectProperty(a.id,'CnstIsUpdateCascade')
        ,级联删除=ObjectProperty(a.id,'CnstIsDeleteCascade')
    FROM sysobjects a
        join sysforeignkeys b on a.id=b.constid
        join sysobjects c on a.parent_obj=c.id
    where a.xtype='f' AND c.xtype='U'
        and object_name(b.rkeyid)='titles'

    SELECT *
    FROM information_schema.columns
    WHERE TABLE_CATALOG='数据库名'
        AND TABLE_NAME = '表名'
        AND COLUMN_NAME='列名'

    select *
    from syscolumns
    where id=object_id('tableName') and name='fieldName'

     

     

     

     

     

    ------------2005以及2008中,查询表的字段---------------------

    DECLARE @tableName nvarchar(100)
    SET @tableName ='tab'
    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.major_id WHERE d.name=@tableName
            ORDER BY a.id,a.colorder

     

     


    SELECT
        CAST(value AS nvarchar(200)) as tableDescription
        FROM fn_listextendedproperty ('MS_Description', 'user', 'dbo', 'table', 't_bank', default, default);

     

     

    declare @tablename varchar(100)
    set @tablename=''
    SELECT
           objname
        ,CAST(value AS nvarchar(200)) as fieldDescription
        FROM fn_listextendedproperty ('MS_Description', 'user', 'dbo', 'table', @TableName , 'column', default) AS E

    展开全文
  • 为什么80%码农都做不了架构师?>>> ...
    select *
    from sys.foreign_keys
    where referenced_object_id=object_id('表名')
    order by 1

    转载于:https://my.oschina.net/ind/blog/353289

    展开全文
  • 查找表的外键(包括名称,引用表的表名和对应的键名,下面是分成多步查询): select * from user_constraints c where c.constraint_type = 'R' and c.table_name = 要查询的表 查询外键约束的列名: ...
  • 就是为程序员提供了一条可以查询表的主键被其他表引用为外键情况的查询
  • 查询所有表的外键约束

    千次阅读 2011-06-06 14:56:00
    查询所有表的外键约束select name from sysobjects where xtype=f 查询所有表的外键约束,并显示该表信息select OBJECT_NAME(sysobjects.parent_obj),* from sysobjects where xtype=f
  • 1、创建表test create table test( id int(5) not null primary key, wid int(5) not null); 查看表结构 2、创建student表 create table student( ...3、修改表结构来创建外键(test表的wid...
  • BSOOC里需要一个查询表主键外键信息SQL,昨晚研究到凌晨1点,终于能实现这个目标:Oracle:select o.obj# as objectId, o.name AS tableName, oc.name AS constraintName, decode(c.type#, 1, C, 2, P, 3, U,...
  • BSOOC里需要一个查询表主键外键信息SQL,昨晚研究到凌晨1点,终于能实现这个目标:Oracle:select o.obj# as objectId, o.name AS tableName, oc.name AS constraintName, decode(c.type#, 1, C, 2, P, 3, U,...
  • 用于查询某个数据库下所有表的外键约束情况,以便统一分析外键约束是否合理;主要查询出外键的,是否级联删除,级联更新,强制外键约束,强制复制约束始终状态。如想使用别的状态,请自行添加。下载的童鞋别下载错了...
  • mysql查询一个表是哪些别的表的外键

    千次阅读 2019-04-01 14:29:56
    删除InnoDB引擎的表时,如果不把涉及该表的所有外键关系删了的话是不能直接删除的,用以下的sql语句可以查到都有哪些表的外键约束涉及到要删除的表。 select TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_...
  • 查询表主键外键信息SQL

    千次阅读 2005-09-29 16:55:00
    Oracle:select o.obj# as objectId, o.name AS tableName, oc.name AS constraintName, decode(c.type#, 1, C, 2, P, 3, U, 4, R, 5, V, 6, O, 7,C, ?) as constraintType,
  • Mysql多表查询外键

    2014-05-22 18:45:34
    表的外键是另一表的主键,waii
  • 1. use INFORMATION_SCHEMA;... select TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME,REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME from KEY_COLUMN_USAGE where table_name = '要查询的表名'; 查询结果:
  • select TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where REFERENCED_TABLE_NAME = 't_user';
  • 列出给定表的外键引用 需求描述 需求:查询出给定的表emp的外键约束信息。 解决方法:通过各个数据库里提供的与外键约束相关的数据字典进行查询。 注: 数据库数据集SQL脚本详见如下链接地址 员工表结构和数据...
  • 1.关于外键:1.1 什么是外键: 用于建立主与从之间关系键,存在于从中。1.2 外键的特点: (1)从外键的值是对主主键引用。 (2)外键类型必须与主键类型一致。1.3 从中有外键,主与从还是...
  • /*查关联外键表*/ select TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where REFERENCED_TABLE_NAME = 'user';
  • 1,查询表的主键被其他表作为外键参照的SQLselect child_cons.constraint_name,child_cons.table_name child_table_name,parent_cons.table_name parent_table,child_cons.statusfrom (select *from all_...
  • --执行如下语句select object_name(B.referenced_object_id),a.name as 约束名,object_name(b.parent_object_id) as 外键表,d.name as 外键列,object_name(b.referenced_object_id) as 主健,c.name as 主键列from ...
  • 在执行数据库删除操作时,外键关联是令我们最烦的一个东西,有了外键关联,就不允许随意删除数据,那么怎知道这个外键关联度的外键属于那个? select * from user_cons_columns cl where cl.constraint_name = '...
  • 、子单独生成了一些测试数据,但子对应的外键字段未生成(数据库设计该字段可为空)。为了更好地模拟数据关系,要填充子外键字段,填充要保证参照完整性,并尽量均衡引用父记录。 方案 利用MySQL的...
  • 数据库的外键虽然能保证数据数据一致性和完整性,但是也一定程度地影响了数据更新的性能。在开发中,我们使用PowerDesigner建立物理数据模型时,为了结构的清晰,增加可读性,会...删除所有表的外键查询schema...
  • select TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where REFERENCED_TABLE_NAME = '表名';
  • 两个相关联 TblRecords判定规则字段与TblRuleid字段关联 CREATE TABLE [dbo].[TblRule]( [Id] [bigint] IDENTITY(1,1) NOT NULL, [厚度_min] [float] NOT NULL, [厚度_max] [float] NOT NULL, [平均厚度_...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 6,097
精华内容 2,438
关键字:

查询表的外键