如何查询出两张表里不同的字段名

lsqzx000 2008-03-28 10:26:55

查询两表中的不同字段名
select name from syscolumns where id=object_id('B')
and name not in (select name from syscolumns where id=object_id('A'))
sqlserver下可以这么写，access下呢，该怎么写？

...全文

197 8 打赏收藏转发到动态举报

写回复

用AI写文章

8 条回复

切换为时间正序

请发表友善的回复…

发表回复

lsqzx000 2008-03-28

打赏
举报

MSysObjects里面有字段的信息吗！

kaikai_kk 2008-03-28

打赏
举报

access没有SYSCOLUMNS
access的是MSysObjects,隐藏的

自己不变通一下!!!!

lsqzx000 2008-03-28

打赏
举报

access 有SYSCOLUMNS吗？

kaikai_kk 2008-03-28

打赏
举报

access 没有object_id函数

判断对像(表名,列,ID...)存在,要用like

select name from syscolumns where (syscolumns.id like 'B')
and name not in (select name from syscolumns where (syscolumns.id like 'A'))

yyunffu 2008-03-28

打赏
举报

关注

liuyann 2008-03-28

打赏
举报

的到某个表的字段

Public Sub getTableColumns(sTableName As String)

    Dim rs As New ADODB.Recordset

    Dim conn As ADODB.Connection

    Dim fld As ADODB.Field



    Set conn = CurrentProject.Connection

    rs.Open sTableName, conn, , , adCmdTable



    

    For Each fld In rs.Fields

        Debug.Print fld.name, sFldType(fld.Type), fld.NumericScale, fld.Precision

        

    Next

    

    rs.Close

    Set rs = Nothing

    Set conn = Nothing



End Sub







Public Function sFldType(iType As Integer)

    Select Case iType

        Case 20

            sFldType = "BigInt"

        Case 128

            sFldType = "Binary"

        Case 11

            sFldType = "Boolean"

        Case 8

            sFldType = "BSTR"

        Case 136

            sFldType = "Chapter"

        Case 129

            sFldType = "Char"

        Case 6

            sFldType = "Currency"

        Case 7

            sFldType = "Date"

        Case 133

            sFldType = "DBDate"

        Case 134

            sFldType = "DBTime"

        Case 135

            sFldType = "DBTimeStamp"

        Case 14

            sFldType = "Decimal"

        Case 5

            sFldType = "Double"

        Case 0

            sFldType = "Empty"

        Case 10

            sFldType = "Error"

        Case 64

            sFldType = "FileTime"

        Case 72

            sFldType = "GUID"

        Case 9

            sFldType = "IDispatch"

        Case 3

            sFldType = "Integer"

        Case 13

            sFldType = "IUnknown"

        Case 205

            sFldType = "LongVarBinary"

        Case 201

            sFldType = "LongVarChar"

        Case 203

            sFldType = "LongVarWChar"

        Case 131

            sFldType = "Numeric"

        Case 138

            sFldType = "PropVariant"

        Case 4

            sFldType = "Single"

        Case 2

            sFldType = "SmallInt"

        Case 16

            sFldType = "TinyInt"

        Case 21

            sFldType = "UnsignedBigInt"

        Case 19

            sFldType = "UnsignedInt"

        Case 18

            sFldType = "UnsignedSmallInt"

        Case 17

            sFldType = "UnsignedTinyInt"

        Case 132

            sFldType = "UserDefined"

        Case 204

            sFldType = "VarBinary"

        Case 200

            sFldType = "VarChar"

        Case 12

            sFldType = "Variant"

        Case 139

            sFldType = "VarNumeric"

        Case 202

            sFldType = "VarWChar"

        Case 130

            sFldType = "WChar"

        Case Else

            sFldType = "unKnown"

    End Select



End Function