Drugs@FDA Data Files

July 5, 2017: We added the Reference Standard field to the Products table on July 5. The data definitions have been updated. [Orange Book information on reference standard]

April 14, 2017:
 SubmissionPropertyTypeID field has been added to SubmissionPropertyType table. The data definitions have been updated.  

Below you will find a compressed data file of the Drugs@FDA database. It does not include the scripts (programming) we use to produce the online version of Drugs@FDA. We are providing this technical information for users who are familiar with working with databases or spreadsheets. All fields are separated by tab delimiters. Each table's primary key, data types, field lengths and nulls appear in the list below. While the official online application, Drugs@FDA, is updated daily, this data file is updated once per week, on Tuesday, starting August 22, 2017.

Once you have downloaded the compressed file (drugsatfda.zip), you can unzip the file into 9 text tables. You can then import the tables into a database, spreadsheet or word processing program. Generally, a database program is the best program to use for these types of files.

We cannot offer guidance on how to construct your database design, as each user has different requirements or uses.

Download File

Entity Relationship Diagram

Drugs@FDA ERD Diagram Small Version


Drugs@FDA consists of 11 tables:

ActionTypes_Lookup (a linking table will be provided in the near future)

  • [ActionTypes_LookupID] [int] IDENTITY(1,1) NOT NULL
  • [ActionTypes_LookupDescription] [varchar](100) NOT NULL
  • [SupplCategoryLevel1Code] [varchar](100) NULL
  • [SupplCategoryLevel2Code] [varchar](100) NULL


  • [ApplicationDocsID] [int] IDENTITY(1,1) NOT NULL
  • [ApplicationDocsTypeID] [int] NOT NULL
  • [ApplNo] [char](6) NOT NULL
  • [SubmissionType] [char](10) NOT NULL
  • [SubmissionNo] [int] NOT NULL
  • [ApplicationDocsTitle] [varchar](100) NULL
  • [ApplicationDocsURL] [varchar](200) NULL
  • [ApplicationDocsDate] [datetime] NULL


  • [ApplNo] [char](6) NOT NULL
  • [ApplType] [char](5) NOT NULL
  • [ApplPublicNotes] [text] NULL
  • [SponsorName] [char](500) NULL


  • [ApplicationDocsType_Lookup_ID] [int] IDENTITY(1,1) NOT NULL
  • ApplicationDocsType_Lookup_Description] [varchar](200) NOT NULL


  • [ApplNo] [char](6) NOT NULL,
  • [ProductNo] [char](3) NOT NULL,
  • [MarketingStatusID] [int] NOT NULL


  • [MarketingStatusID] [int] IDENTITY(1,1) NOT NULL
  • [MarketingStatusDescription] [varchar](200) NOT NULL


  • [ApplNo] [char](6) NOT NULL
  • [ProductNo] [char](6) NOT NULL
  • [Form] [varchar](255) NULL
  • [Strength] varchar](240) NULL
  • [ReferenceDrug] [int] NULL
  • [DrugName] [varchar](125) NULL
  • [ActiveIngredient] [varchar](255) NULL
  • (NEW) [ReferenceStandard] [int] NULL


  • [SubmissionClassCodeID] [int] IDENTITY(1,1) NOT NULL
  • [SubmissionClassCode] [varchar](50) NOT NULL
  • [SubmissionClassCodeDescription] [varchar](500) NULL


  • [ApplNo] [char](6) NOT NULL
  • [SubmissionType] [char](10) NOT NULL
  • [SubmissionNo] [int] NOT NULL
  • [SubmissionPropertyTypeCode] [varchar](50) NOT NULL (Orphan or NULL)
  • (NEW) SubmissionPropertyTypeID [int] NOT NULL


  • [ApplNo] [char](6) NOT NULL
  • [SubmissionClassCodeID] [int] NULL
  • [SubmissionType] [char](10) NOT NULL
  • [SubmissionNo] [int] NOT NULL
  • [SubmissionStatus] [char](2) NULL
  • [SubmissionStatusDate] [datetime] NULL
  • [SubmissionsPublicNotes] [text] NULL
  • [ReviewPriority] [varchar](20) (Standard, Priority, NULL)


  • [ApplNo] [char](6) NOT NULL
  • [ProductNo] [char](3) NOT NULL
  • [MarketingStatusID] [int] NOT NULL
  • [TECode] [varchar](100) NOT NULL

Entity Relationship Diagram

Drugs@FDA Entity Relationship Diagram

Drugs@FDA consists of 9 tables:

  1. Application Documents (AppDoc): Document addresses or URLs to letters, labels, reviews, Consumer Information Sheets, FDA Talk Papers, and other types.
    • AppDocID [int, 4] (Primary Key)
    • ApplNo  [varchar, 6]
    • SeqNo [varchar,4]
    • DocType [varchar, 50]
    • DocTitle [varchar, 100, nulls]
    • DocURL [varchar, 200, nulls]
    • DocDate [datetime, 8, nulls]
    • ActionType [varchar, 10]
    • DuplicateCounter [int, 4, nulls]
  2. Application Document Type Lookup (AppDocType_Lookup): Type of document that is linked, which relates to the AppDoc table.
    • AppDocType [varchar, 50] (Primary Key)
    • SortOrder [int, 4]
  3. Application (Application): Application number and sponsor name.
    • ApplNo [varchar, 6] (Primary Key)
    • ApplType [varchar, 5] (A=ANDA, N=NDA, B=BLA)
    • SponsorApplicant [varchar, 50]
    • MostRecentLabelAvailableFlag [bit, 1]
    • CurrentPatentFlag [bit, 1]
    • ActionType [varchar, 10]
    • Chemical_Type [varchar, 3, nulls]
    • Therapeutic_Potential [varchar, 2, nulls]
    • Orphan_Code [varchar, 1, nulls]
  4. Document Type Lookup (DocType_Lookup): Supplement type code and description to the application number.
    • DocType  [varchar, 4] (Primary Key)
    • DocTypeDesc [varchar, 50, nulls]
  5. Product (Product): This table contains the products included in each application. Includes form, dosage, and route.
    • ApplNo [varchar, 6] (Primary Key)
    • ProductNo [varchar, 3] (Primary Key)
    • Form [varchar, 255, nulls]
    • Dosage [varchar, 240, nulls]
    • ProductMktStatus [tinyint, 1] (1=prescription, 2=OTC, 3=discontinued, 4=tentative approval) (Primary Key)
    • TECode [varchar, 100, nulls]
    • ReferenceDrug [bit, 1] (0=not RLD, 1=RLD, 2=TBD)
    • Drugname [varchar, 125, nulls]
    • Activeingred [varchar, 255, nulls] 
  6. Product_TECode: Therapeutic Equivalence Code for Products.
    • ApplNo [varchar, 6] (Primary Key)
    • ProductNo [varchar, 3] (Primary Key)
    • TECode [varchar, 50]
    • TESequence [int, 4] (Primary Key)
    • ProdMktStatus [tinyint, 1] (Primary Key)
  7. Supplements (RegActionDate): Approval history for each application. Includes supplement number and dates of approval.
    • ApplNo  [varchar, 6] (Primary Key)
    • ActionType [varchar, 10]
    • InDocTypeSeqNo [varchar, 4] (Primary Key)
    • DuplicateCounter [int, 4] (Primary Key)
    • ActionDate [datetime, 8, nulls]
    • DocType [varchar, 4, nulls]
  8. ChemicalType_Lookup
    • ChemicalTypeID [int, 4] (Primary Key)
    • ChemicalTypeCode [varchar, 3]
    • ChemicalTypeDescription [varchar, 200]
  9. ReviewClass_Lookup
    • ReviewClassID [int, 4] (Primary Key)
    • ReviewCode [varchar, 1]
    • LongDescritption [varchar, 100, nulls]
    • ShortDescription [varchar, 100]


Page Last Updated: 09/19/2017
Note: If you need help accessing information in different file formats, see Instructions for Downloading Viewers and Players.
Language Assistance Available: Español | 繁體中文 | Tiếng Việt | 한국어 | Tagalog | Русский | العربية | Kreyòl Ayisyen | Français | Polski | Português | Italiano | Deutsch | 日本語 | فارسی | English