Diversity Collection

Cache database

VIEWS

The following objects are not included:

  • Logging tables
  • Enumeration tables
  • System objects
  • Objects marked as obsolete
  • Previous versions of objects

View Agents_TNT_TNTagents

Column Data type Description Nullable
BaseURL varchar (255) - YES
AgentID int - NO
AgentParentID int - YES
AgentName nvarchar (200) - NO
AgentTitle nvarchar (50) - YES
GivenName nvarchar (255) - YES
GivenNamePostfix nvarchar (50) - YES
InheritedNamePrefix nvarchar (50) - YES
InheritedName nvarchar (255) - YES
InheritedNamePostfix nvarchar (50) - YES
Abbreviation nvarchar (50) - YES
AgentType nvarchar (50) - YES
AgentRole nvarchar (255) - YES
AgentGender nvarchar (50) - YES
Description nvarchar (1000) - YES
OriginalSpelling nvarchar (200) - YES
Notes nvarchar (MAX) - YES
ValidFromDate datetime - YES
ValidUntilDate datetime - YES
SynonymToAgentID int - YES
ProjectID int - NO
LogUpdatedWhen smalldatetime - YES

View Agents_TNT_TNTagents_C

Column Data type Description Nullable
BaseURL varchar (255) - YES
AgentID int - NO
DisplayOrder tinyint - NO
AddressType nvarchar (50) - YES
Country nvarchar (255) - YES
City nvarchar (255) - YES
PostalCode nvarchar (50) - YES
Streetaddress nvarchar (255) - YES
Address nvarchar (255) - YES
Telephone nvarchar (50) - YES
CellularPhone nvarchar (50) - YES
Telefax nvarchar (50) - YES
Email nvarchar (255) - YES
URI nvarchar (255) - YES
Notes nvarchar (MAX) - YES
ValidFrom datetime - YES
ValidUntil datetime - YES
LogUpdatedWhen smalldatetime - YES

View Agents_TNT_TNTagents_I

Column Data type Description Nullable
BaseURL varchar (255) - YES
AgentID int - NO
URI varchar (255) - NO
Type nvarchar (50) - YES
Sequence int - YES
Description nvarchar (MAX) - YES
LogUpdatedWhen smalldatetime - YES

View Agents_TNT_TNTagents_ID

Column Data type Description Nullable
BaseURL varchar (255) - YES
AgentID int - NO
Identifier nvarchar (190) - YES
IdentifierURI varchar (500) - YES
Type nvarchar (50) - YES
Notes nvarchar (MAX) - YES
LogUpdatedWhen smalldatetime - YES

View Sources

Column Data type Description Nullable
SourceView nvarchar (400) The name of the source view of the data YES
URI nvarchar (255) - YES
DisplayText nvarchar (500) - YES
ID int - YES
BaseURL nvarchar (500) The basic URL as defined in the module database YES

View TaxonNames_Insecta_TNT_IBFnames

Column Data type Description Nullable
NameID int - NO
BaseURL varchar (52) - NO
TaxonName nvarchar (255) - YES
AcceptedNameID int - NO
AcceptedName nvarchar (255) - YES
TaxonomicRank nvarchar (50) - NO
GenusOrSupragenericName nvarchar (200) - NO
SpeciesGenusNameID int - YES
TaxonNameSinAuthor nvarchar (1058) - YES
ProjectID int - NO
AcceptedNameSinAuthor nvarchar (1058) - YES
LogUpdatedWhen smalldatetime - YES

View TaxonNames_Insecta_TNT_IBFnames_C

Column Data type Description Nullable
BaseURL varchar (255) - YES
NameID int - NO
CommonName nvarchar (220) - NO
LanguageCode varchar (2) - NO
CountryCode varchar (2) - NO
LogUpdatedWhen smalldatetime - YES

View TaxonNames_Insecta_TNT_IBFnames_E

Column Data type Description Nullable
BaseURL varchar (255) - YES
ExternalDatabaseID int - NO
ExternalDatabaseName nvarchar (800) - NO
ExternalDatabaseVersion nvarchar (255) - YES
Rights nvarchar (500) - YES
ExternalDatabaseAuthors nvarchar (200) - YES
ExternalDatabaseURI nvarchar (300) - YES
ExternalDatabaseInstitution nvarchar (300) - YES
ExternalAttribute_NameID nvarchar (255) - YES
LogUpdatedWhen datetime - YES

View TaxonNames_Insecta_TNT_IBFnames_EID

Column Data type Description Nullable
BaseURL varchar (255) - YES
NameID int - NO
ExternalDatabaseID int - NO
ExternalNameURI varchar (255) - YES
LogUpdatedWhen datetime - YES

View TaxonNames_Insecta_TNT_IBFnames_H

Column Data type Description Nullable
BaseURL varchar (255) - YES
NameID int - NO
NameParentID int - YES
LogUpdatedWhen smalldatetime - YES

View TaxonNames_Insecta_TNT_IBFnames_L

Column Data type Description Nullable
BaseURL varchar (255) - YES
ProjectID int - NO
Project nvarchar (50) - NO
DisplayText nvarchar (50) - YES

View TaxonNames_Insecta_TNT_IBFnames_LA

Column Data type Description Nullable
BaseURL varchar (255) - YES
NameID int - NO
ProjectID int - NO
AnalysisID int - NO
AnalysisValue nvarchar (MAX) - YES
Notes nvarchar (MAX) - YES
LogUpdatedWhen smalldatetime - YES

View TaxonNames_Insecta_TNT_IBFnames_LAC

Column Data type Description Nullable
BaseURL varchar (255) - YES
AnalysisID int - NO
AnalysisParentID int - YES
DisplayText nvarchar (50) - YES
Description nvarchar (MAX) - YES
AnalysisURI varchar (255) - YES
ReferenceTitle nvarchar (800) - YES
ReferenceURI varchar (400) - YES
SortingID int - YES
LogUpdatedWhen datetime - YES

View TaxonNames_Insecta_TNT_IBFnames_LACV

Column Data type Description Nullable
BaseURL varchar (255) - YES
AnalysisID int - NO
AnalysisValue nvarchar (255) - NO
Description nvarchar (500) - YES
DisplayText nvarchar (50) - YES
DisplayOrder smallint - YES
Notes nvarchar (500) - YES
LogUpdatedWhen smalldatetime - YES

View ViewAnalysis

View for analysis used in the published data

Column Data type Description Nullable
AnalysisID int - NO
AnalysisParentID int - YES
DisplayText nvarchar (50) - YES
Description nvarchar (MAX) - YES
MeasurementUnit nvarchar (50) - YES
Notes nvarchar (MAX) - YES
AnalysisURI varchar (255) - YES
OnlyHierarchy bit - YES
LogUpdatedWhen datetime - YES

View ViewAnnotation

View for all annotations

Column Data type Description Nullable
AnnotationID int - NO
ReferencedAnnotationID int - YES
AnnotationType nvarchar (50) - NO
Title nvarchar (50) - YES
Annotation nvarchar (MAX) - NO
URI varchar (255) - YES
ReferenceDisplayText nvarchar (500) - YES
ReferenceURI varchar (255) - YES
SourceDisplayText nvarchar (500) - YES
SourceURI varchar (255) - YES
IsInternal bit - YES
ReferencedID int - NO
ReferencedTable nvarchar (500) - NO
LogUpdatedWhen datetime - YES

View ViewCollection

View for all not withheld collections

Column Data type Description Nullable
CollectionID int - NO
CollectionParentID int - YES
CollectionName nvarchar (255) - NO
CollectionAcronym nvarchar (10) - YES
AdministrativeContactName nvarchar (500) - YES
AdministrativeContactAgentURI varchar (255) - YES
Description nvarchar (MAX) - YES
Location nvarchar (255) - YES
CollectionOwner nvarchar (255) - YES
DisplayOrder smallint - YES
LogUpdatedWhen datetime - YES

View ViewCollectionAgent

View for all not withheld collectors

Column Data type Description Nullable
CollectionSpecimenID int - NO
CollectorsName nvarchar (255) - NO
CollectorsSequence datetime2 - YES
CollectorsNumber nvarchar (50) - YES
CollectorsAgentURI varchar (255) - YES
LogUpdatedWhen datetime - YES

View ViewCollectionEvent

View for all not withheld collection events

Column Data type Description Nullable
CollectionEventID int - NO
Version int - NO
CollectorsEventNumber nvarchar (50) - YES
CollectionDate datetime - YES
CollectionDay tinyint - YES
CollectionMonth tinyint - YES
CollectionYear smallint - YES
CollectionDateSupplement nvarchar (100) - YES
CollectionTime varchar (50) - YES
CollectionTimeSpan varchar (50) - YES
LocalityDescription nvarchar (MAX) - YES
HabitatDescription nvarchar (MAX) - YES
ReferenceTitle nvarchar (255) - YES
CollectingMethod nvarchar (MAX) - YES
Notes nvarchar (MAX) - YES
CountryCache nvarchar (50) - YES
ReferenceDetails nvarchar (50) - YES
LocalityVerbatim nvarchar (MAX) - YES
CollectionEndDay tinyint - YES
CollectionEndMonth tinyint - YES
CollectionEndYear smallint - YES
LogUpdatedWhen datetime - YES

View ViewCollectionEventLocalisation

View for all not withheld localisations

Column Data type Description Nullable
CollectionEventID int - NO
LocalisationSystemID int - NO
Location1 nvarchar (255) - YES
Location2 nvarchar (255) - YES
LocationAccuracy nvarchar (50) - YES
LocationNotes nvarchar (MAX) - YES
DeterminationDate smalldatetime - YES
DistanceToLocation varchar (50) - YES
DirectionToLocation varchar (50) - YES
ResponsibleName nvarchar (255) - YES
ResponsibleAgentURI varchar (255) - YES
AverageAltitudeCache float - YES
AverageLatitudeCache float - YES
AverageLongitudeCache float - YES
RecordingMethod nvarchar (500) - YES
Geography nvarchar (MAX) - YES
LogUpdatedWhen datetime - YES

View ViewCollectionEventProperty

View for all not withheld collection site properties

Column Data type Description Nullable
CollectionEventID int - NO
PropertyID int - NO
DisplayText nvarchar (255) - YES
PropertyURI varchar (255) - YES
PropertyHierarchyCache nvarchar (MAX) - YES
PropertyValue nvarchar (255) - YES
ResponsibleName nvarchar (255) - YES
ResponsibleAgentURI varchar (255) - YES
Notes nvarchar (MAX) - YES
AverageValueCache float - YES
LogUpdatedWhen datetime - YES

View ViewCollectionExternalDatasource

View for all external datasources

Column Data type Description Nullable
ExternalDatasourceID int - NO
ExternalDatasourceName nvarchar (255) - YES
ExternalDatasourceVersion nvarchar (255) - YES
Rights nvarchar (500) - YES
ExternalDatasourceAuthors nvarchar (200) - YES
ExternalDatasourceURI nvarchar (300) - YES
ExternalDatasourceInstitution nvarchar (300) - YES
InternalNotes nvarchar (1500) - YES
ExternalAttribute_NameID nvarchar (255) - YES
PreferredSequence tinyint - YES
Disabled bit - YES
LogUpdatedWhen datetime - YES

View ViewCollectionProject

View for all not withheld collection specimen within the projects

Column Data type Description Nullable
CollectionSpecimenID int - NO
ProjectID int ID of the project to which the specimen belongs (Projects are defined in DiversityProjects) NO
LogUpdatedWhen datetime - YES

View ViewCollectionSpecimen

View for all not withheld specimen

Column Data type Description Nullable
CollectionSpecimenID int - NO
LabelTranscriptionNotes nvarchar (255) - YES
OriginalNotes nvarchar (MAX) - YES
LogUpdatedWhen datetime - YES
CollectionEventID int - YES
AccessionNumber nvarchar (50) - YES
AccessionDate datetime - YES
AccessionDay tinyint - YES
AccessionMonth tinyint - YES
AccessionYear smallint - YES
DepositorsName nvarchar (255) - YES
DepositorsAccessionNumber nvarchar (50) - YES
ExsiccataURI varchar (255) - YES
ExsiccataAbbreviation nvarchar (255) - YES
AdditionalNotes nvarchar (MAX) - YES
ReferenceTitle nvarchar (255) - YES
ReferenceURI varchar (255) - YES
ExternalDatasourceID int - YES

View ViewCollectionSpecimenImage

View for all not withheld collection specimen images

Column Data type Description Nullable
CollectionSpecimenID int - NO
URI varchar (255) - NO
ResourceURI varchar (255) - YES
SpecimenPartID int - YES
IdentificationUnitID int - YES
ImageType nvarchar (50) - YES
Notes nvarchar (MAX) - YES
LicenseURI varchar (500) - YES
LicenseNotes nvarchar (500) - YES
DisplayOrder int - YES
LicenseYear nvarchar (50) - YES
LicenseHolderAgentURI nvarchar (500) - YES
LicenseHolder nvarchar (500) - YES
LicenseType nvarchar (500) - YES
CopyrightStatement nvarchar (500) - YES
CreatorAgentURI varchar (255) - YES
CreatorAgent nvarchar (500) - YES
IPR nvarchar (500) - YES
Title nvarchar (500) - YES
LogUpdatedWhen datetime - YES

Depending on:

  • CollectionSpecimenImage

View ViewCollectionSpecimenPart

View for all not withheld collection specimen parts

Column Data type Description Nullable
SpecimenPartID int - NO
DerivedFromSpecimenPartID int - YES
PreparationMethod nvarchar (MAX) - YES
PreparationDate datetime - YES
PartSublabel nvarchar (50) - YES
CollectionID int - NO
MaterialCategory nvarchar (50) - NO
StorageLocation nvarchar (255) - YES
Stock float - YES
Notes nvarchar (MAX) - YES
CollectionSpecimenID int - NO
AccessionNumber nvarchar (50) - YES
StorageContainer nvarchar (500) - YES
StockUnit nvarchar (50) - YES
ResponsibleName nvarchar (255) - YES
LogUpdatedWhen datetime - YES

View ViewCollectionSpecimenProcessing

View for all not withheld collection specimen processings

Column Data type Description Nullable
CollectionSpecimenID int - NO
SpecimenProcessingID int - NO
ProcessingDate datetime - YES
ProcessingID int - NO
Protocoll nvarchar (100) - YES
SpecimenPartID int - YES
ProcessingDuration varchar (50) - YES
ResponsibleName nvarchar (255) - YES
ResponsibleAgentURI varchar (255) - YES
Notes nvarchar (MAX) - YES
LogUpdatedWhen datetime - YES

View ViewCollectionSpecimenReference

View for all not withheld references

Column Data type Description Nullable
CollectionSpecimenID int - NO
ReferenceID int - NO
ReferenceTitle nvarchar (400) - NO
ReferenceURI varchar (500) - YES
IdentificationUnitID int - YES
SpecimenPartID int - YES
ReferenceDetails nvarchar (500) - YES
Notes nvarchar (MAX) - YES
ResponsibleName nvarchar (255) - YES
ResponsibleAgentURI varchar (255) - YES
LogUpdatedWhen datetime - YES

View ViewCollectionSpecimenRelation

View for all not withheld collection specimen processings

Column Data type Description Nullable
CollectionSpecimenID int - NO
RelatedSpecimenURI varchar (255) - NO
RelatedSpecimenDisplayText varchar (255) - NO
RelationType nvarchar (50) - YES
RelatedSpecimenCollectionID int - YES
RelatedSpecimenDescription nvarchar (MAX) - YES
Notes nvarchar (MAX) - YES
IdentificationUnitID int - YES
SpecimenPartID int - YES
LogUpdatedWhen datetime - YES

View ViewExternalIdentifier

View for all external identifiers

Column Data type Description Nullable
ID int - NO
Type nvarchar (50) - YES
Identifier nvarchar (500) - YES
URL varchar (500) - YES
Notes nvarchar (MAX) - YES
ReferencedTable nvarchar (128) - NO
ReferencedID int - NO
LogUpdatedWhen datetime - YES

View ViewIdentification

View for all identifications

Column Data type Description Nullable
CollectionSpecimenID int - NO
IdentificationUnitID int - NO
IdentificationSequence smallint - NO
IdentificationDay tinyint - YES
IdentificationMonth tinyint - YES
IdentificationYear smallint - YES
IdentificationDateSupplement nvarchar (255) - YES
IdentificationCategory nvarchar (50) - YES
IdentificationQualifier nvarchar (50) - YES
VernacularTerm nvarchar (255) - YES
TaxonomicName nvarchar (255) - YES
NameURI varchar (255) - YES
Notes nvarchar (MAX) - YES
TypeStatus nvarchar (50) - YES
TypeNotes nvarchar (MAX) - YES
ReferenceTitle nvarchar (255) - YES
ReferenceDetails nvarchar (50) - YES
ResponsibleName nvarchar (255) - YES
LogUpdatedWhen datetime - YES

View ViewIdentificationUnit

View for all not withheld identification units

Column Data type Description Nullable
CollectionSpecimenID int - NO
IdentificationUnitID int - NO
LastIdentificationCache nvarchar (255) - NO
TaxonomicGroup nvarchar (50) - NO
RelatedUnitID int - YES
RelationType nvarchar (50) - YES
ExsiccataNumber nvarchar (50) - YES
DisplayOrder smallint - NO
ColonisedSubstratePart nvarchar (255) - YES
FamilyCache nvarchar (255) - YES
OrderCache nvarchar (255) - YES
LifeStage nvarchar (255) - YES
Gender nvarchar (50) - YES
HierarchyCache nvarchar (500) - YES
UnitIdentifier nvarchar (50) - YES
UnitDescription nvarchar (50) - YES
Circumstances nvarchar (50) - YES
Notes nvarchar (MAX) - YES
NumberOfUnits smallint - YES
OnlyObserved bit - YES
RetrievalType nvarchar (50) - YES
LogUpdatedWhen datetime - YES

View ViewIdentificationUnitAnalysis

View for all not withheld identification units

Column Data type Description Nullable
AnalysisID int - NO
AnalysisNumber nvarchar (50) - NO
AnalysisResult nvarchar (MAX) - YES
ExternalAnalysisURI varchar (255) - YES
ResponsibleName nvarchar (255) - YES
ResponsibleAgentURI varchar (255) - YES
AnalysisDate nvarchar (50) - YES
SpecimenPartID int - YES
Notes nvarchar (MAX) - YES
CollectionSpecimenID int - NO
IdentificationUnitID int - NO
LogUpdatedWhen datetime - YES

View ViewIdentificationUnitGeoAnalysis

View for all not withheld geo analysis

Column Data type Description Nullable
CollectionSpecimenID int - NO
IdentificationUnitID int - NO
AnalysisDate datetime - NO
Geography nvarchar (MAX) - YES
Geometry nvarchar (MAX) - YES
ResponsibleName nvarchar (255) - YES
ResponsibleAgentURI varchar (255) - YES
Notes nvarchar (MAX) - YES
LogUpdatedWhen datetime - YES

Depending on:

  • IdentificationUnitGeoAnalysis

View ViewIdentificationUnitInPart

View for all not withheld identification units in a specimen part

Column Data type Description Nullable
Description nvarchar (500) - YES
CollectionSpecimenID int - NO
IdentificationUnitID int - NO
SpecimenPartID int - NO
DisplayOrder smallint - NO
LogUpdatedWhen smalldatetime - YES

View ViewMetadata

View for metadata

Column Data type Description Nullable
ProjectID int - NO
SettingID int - NO
Value nvarchar (MAX) - NO
LogUpdatedWhen smalldatetime - YES

View ViewProcessing

View for processings

Column Data type Description Nullable
ProcessingID int - NO
ProcessingParentID int - YES
DisplayText nvarchar (50) - YES
Description nvarchar (MAX) - YES
Notes nvarchar (MAX) - YES
ProcessingURI varchar (255) - YES
OnlyHierarchy bit - YES
LogUpdatedWhen datetime - YES

View ViewTaxononmy

View for all identifications including the accepted names and synonyms as derived from the table TaxonSynonymy

Column Data type Description Nullable
CollectionSpecimenID int - NO
IdentificationUnitID int - NO
IdentificationSequence smallint - NO
IdentificationDay tinyint - YES
IdentificationMonth tinyint - YES
IdentificationYear smallint - YES
IdentificationDateSupplement nvarchar (255) - YES
IdentificationCategory nvarchar (50) - YES
IdentificationQualifier nvarchar (50) - YES
VernacularTerm nvarchar (255) - YES
TaxonomicName nvarchar (255) - YES
NameURI varchar (255) - YES
Notes nvarchar (MAX) - YES
TypeStatus nvarchar (50) - YES
TypeNotes nvarchar (MAX) - YES
ReferenceTitle nvarchar (255) - YES
ReferenceDetails nvarchar (50) - YES
SynonymName nvarchar (255) - YES
AcceptedName nvarchar (255) - YES
TaxonomicRank nvarchar (50) - YES
AcceptedNameURI varchar (255) - YES
GenusOrSupragenericName nvarchar (255) - YES
TaxonNameSinAuthor nvarchar (2000) - YES
ValidTaxonName nvarchar (255) - YES
ValidTaxonWithQualifier nvarchar (560) - YES
ValidTaxonNameSinAuthor nvarchar (2000) - YES
ValidTaxonIndex nvarchar (259) - YES
ValidTaxonListOrder int - NO
ResponsibleName nvarchar (255) - YES

View ViewWithholdAgent

View for all withheld collectors of published specimen

Column Data type Description Nullable
CollectionSpecimenID int - NO

View ViewWithholdPart

View for all withheld parts of published specimen

Column Data type Description Nullable
SpecimenPartID int - NO

View ViewWithholdUnit

View for all withheld units of published specimen

Column Data type Description Nullable
IdentificationUnitID int - NO