Table properties

[Knicks]

I am involved in a project at work to move Access tables over to SQL. I am trying to figure out if there is a way to get a data dictionary on tables and fields and in particular if a field is part of a composite primary key.

I have been able to generate if a field is unique, primary, zero length ect.., but I was wondering if composite is an attribute somewhere.

The problem has been that the data dictionary has been showing primary true and unique true for fields that don't need to be set as unique as a single field, only as a composite. If I could get an attribute that saids composite true, then the conversion process could use that field to not set the individual field as needing to be unique.

Any help would be appreciated.

 

[maxhugen]

Not sure what you mean by a data dictionary... are you referring to Access's Documenter?

A 'composite primary key' is an Index with more than one field, that has Unique and Primary properties set to True.

Note that these 2 properties don't apply to the fields, only to an Index.

If you know of a table that uses a Composite key, run the Documenter (Tools > Analyze > Documenter), and select that one table. In the report output, you'll see that it's the Index(es), not the fields, that have these properties.

If you want to check all Indexes on all Tables for composites, I'd write a short bit of VBA code to enumerate the tables, their indexes, and the index fields, or just output those tables where the number of fields in an Index >1.

HTH

Max Hugen
Australia

 

[genomon]

FYI - a data dictionary is
a file that defines the basic organization of a database. It will contain a list of all files in the database, the number of records in each file, and the names and types of each field.

http://www.tjiss.net/glossary_d.html

"Business conventions are important because they demonstrate how many people a company can operate without."