Describe all columns in a table?

[accessmatt]

Hello,



What's the query to use if I want to describe all of the columns in a table? I want to know how to do this with SQL but also interested if Access has easier way.

Thanks!

[pbaldy]

What do you mean by describe? If you mean return all fields, you want *.

SELECT * FROM TableName

[ranman256]

Do you mean return all the fields and their description property?

[accessmatt]

Hi guys,

SELECT * isn't want I want because it pulls the whole table. I simple want to know the column names. ranman256: yes, I think what you're describing is it. How do I do that?

Thanks,

[pbaldy]

I can't think of a way with SQL offhand. You could use a DAO TableDef and loop the Fields collection or even open a recordset and loop the fields.

[orange]

I have a routine that uses DAO TableDef and Fields as Paul suggested. I've used it for years. I have copied it here.
It uses a sub and a function. It may be more than you are seeking, but it's quite general and you can query the table
for many purposes.
Good luck.

'---------------------------------------------------------------------------------------
' Procedure : DocumentTables
' DateTime  : 2006-06-09 09:29
' Author    : jack
' Purpose   : To create documentation for all tables in this database
'             where tableName <>MSys*  or TableName doesn't start with "~"
'
'**** Note: This routine builds a new table (data_dictionary) from scratch on each execution ****
'
'Fields that are recorded
'   table_name varchar(250)
'   table_description varchar(255)
'   field_name varchar(250)
'   field_description varchar(255)
'   ordinal_position NUMBER,
'   data_type varchar(15),"
'   length varchar(5)
'   default varchar(30)
'
'
' ' Last Modified:
'
' Inputs: N/A
' Dependency:   Calls Function FieldType
'---------------------------------------------------------------------------------------
'
Public Sub DocumentTables()
      'Requires function FieldType

10        On Error GoTo Error_DocumentTables

          Dim db As DAO.Database
          Dim rs As DAO.Recordset
          Dim tdf As DAO.TableDef
          Dim fld As DAO.Field
          Dim strSQL_Drop As String
          Dim strSQL_Create As String
          '==
          'SQL to Delete existing copy of this table
20        strSQL_Drop = "DROP TABLE data_dictionary;"

          'SQL to Create the data_dictionary table
30        strSQL_Create = "CREATE TABLE data_dictionary" & _
                          "(EntryID AUTOINCREMENT PRIMARY KEY,table_name varchar(250),table_description varchar(255), field_name varchar(250),field_description varchar(255)," & _
                          "ordinal_position NUMBER, data_type varchar(15)," & _
                          "length varchar(5), default varchar(30));"

40        Set db = CurrentDb()
          
50        db.Execute strSQL_Drop, dbFailOnError
60        DoEvents
70        db.Execute strSQL_Create, dbFailOnError
80        DoEvents
90        Set rs = db.OpenRecordset("data_dictionary")

100       With rs
110           For Each tdf In db.TableDefs
120               If Left(tdf.name, 4) <> "Msys" _
                     And Left(tdf.name, 1) <> "~" Then

130                   For Each fld In tdf.Fields
140                       .AddNew
150                       !table_name = tdf.name
160                       !table_description = tdf.Properties("description")
170                       !Field_Name = fld.name
180                       !field_description = fld.Properties("description")
190                       !ordinal_position = fld.OrdinalPosition
200                       !data_type = FieldType(fld.Type)
210                       !Length = fld.Size
220                       !Default = fld.DefaultValue

230                       .Update
240                   Next
250               End If
260           Next
270       End With

280       MsgBox "Tables have been documented", vbInformation, "TABLES DOCUMENTED"

290       rs.Close
300       db.Close

Exit_Error_DocumentTables:

310       Set tdf = Nothing
320       Set rs = Nothing
330       Set db = Nothing

340       Exit Sub

Error_DocumentTables:

350       Select Case Err.number

          Case 3376

360           Resume Next    'Ignore error if table not found
370       Case 3270    'Property Not Found

380           Resume Next
390       Case Else

400           MsgBox Err.number & ": " & Err.Description
410           Resume Exit_Error_DocumentTables

420       End Select

End Sub



'---------------------------------------------------------------------------------------
' Procedure : FieldType
' Author    : Jack
' Created   : 3/18/2008
' Purpose   : To identify fieldtypes in Access.
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: N/A
' Dependency: N/A
'------------------------------------------------------------------------------
'
Private Function FieldType(v_fldtype As Integer) As String

10  On Error GoTo Error_FieldType

20  Select Case v_fldtype
    Case dbBoolean
30      FieldType = "Boolean"
40  Case dbByte
50      FieldType = "Byte"
60  Case dbInteger
70      FieldType = "Integer"
80  Case dbLong
90      FieldType = "Long"
100 Case dbCurrency
110     FieldType = "Currency"
120 Case dbSingle
130     FieldType = "Single"
140 Case dbDouble
150     FieldType = "Double"
160 Case dbDate
170     FieldType = "Date"
180 Case dbText
190     FieldType = "Text"
200 Case dbLongBinary
210     FieldType = "LongBinary"
220 Case dbMemo
230     FieldType = "Memo"
240 Case dbGUID
250     FieldType = "GUID"
260 End Select

Exit_Error_Fieldtype:
270 Exit Function

Error_FieldType:
280 MsgBox Err.number & ": " & Err.Description
290 Resume Exit_Error_Fieldtype

End Function

[dhutton27]

Can this be run in Access?

[pbaldy]

Can this be run in Access?

If "this" refers to the code orange posted, that is an Access VBA function.

[dhutton27]

Yes - the code posted by orange. So what do I have to have open the execute her code. I have Access 2010, SQL Server 2012, and Visual Studio 2012

Thanks

[orange]

For help with Access and vba see this site.

[ItsMe]

For help with Access and vba see this site.

Why is that guy throwing CRT monitors on top of that lady?