代码之家  ›  专栏  ›  技术社区  ›  Baalback

在不同的列上显示所选的表及其列名称

  •  0
  • Baalback  · 技术社区  · 8 年前

    TMP 和 TMP1 因此,我想从syscolumns中选择并获取这两个表的列名 我想要这样的结果

    tablename columnname  tablename1 columnname1
    
    Tmp         col1        TMP1         col1
    
    Tmp         col2        TMP1         col2
    

    这是我试图做的错误的事情:

    select sc.name , sc1.name
    from syscolumns sc inner join syscolumns sc1
    on( sc.id=sc1.id and sc.id=object_id('TMP1'))
    where sc.id=object_id('TMP')
    
    1 回复  |  直到 8 年前
        1
  •  1
  •   markp-fuso    8 年前

    假设:

    • 表的列数相同
    • 两个表中的列名相同

    设置:

    create table TMP (col1 int, col2 int)
    go
    create table TMP1(col1 iny, col2 int)
    go
    

    select  'TMP' as tablename,
            sc.name as columnname,
            'TMP1' as tablename1,
            sc1.name as columname1
    
            -- can't join syscolumns on 'id' because each table has a unique object id;
            -- only thing we have to join on is the similarly named columns
    
    from    syscolumns sc
    join    syscolumns sc1
    on      sc.name = sc1.name
    
    where   sc.id  = object_id('TMP')
    and     sc1.id = object_id('TMP1')
    
    order by sc.name
    go
    
     tablename columnname tablename1 columname1
     --------- ---------- ---------- ----------
     TMP       col1       TMP1       col1
     TMP       col2       TMP1       col2