Skip to Content
avatar image
Former Member

How to change the sort column list in a table object?

Currently, I'm doing a lot of reverse engineering of our MS SQL and MS ACCESS databases. Creating visual documentation by creating the tables/columns/joins between tables. I don't have any problem with the reverse engineering itself. When the table objects are populated with the columns and I run the name to code conversion everything is fine. But, in our outfit, we like to have the primary key at the top of the list of columns and foreign key(s) at the bottom of the displayed list. I can do it by hand, but after a hundred tables or so, that gets a little old. I would like to know if there is an option I'm missing, either in Display Preferences or Model Options where I can reverse the sort?

Thanks, George Teachman

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Jun 21, 2017 at 07:46 PM

    The current release doesn't have such functionality.

    However you run script to automate the whole process. Open your pdm, go to Tools->Execute Command->Edit\Run Script. Run the following code as start point.

    Set PrimaryKeyColumnDictionary = CreateObject("Scripting.Dictionary")
    Set ForeignKeyColumnDictionary = CreateObject("Scripting.Dictionary")
    Set NotKeyColumnDictionary = CreateObject("Scripting.Dictionary")

    set tables=Activemodel.tables

    for each tbl in tables
    set columns=tbl.columns

    for each col in columns
    if col.primary=true then
    PrimaryKeyColumnDictionary.add col.name, col
    elseif col.foreignkey = true then
    ForeignKeyColumnDictionary.add col.name, col
    else
    NotKeyColumnDictionary.add col.name, col
    end if
    next

    tbl.columns.clear()

    for each key in PrimaryKeyColumnDictionary.Keys
    set obj=PrimaryKeyColumnDictionary.Item(key)
    tbl.columns.add(obj)
    next

    for each key in NotKeyColumnDictionary.Keys
    tbl.columns.add(NotKeyColumnDictionary.Item(key))
    next

    for each key in ForeignKeyColumnDictionary.Keys
    tbl.columns.add(ForeignKeyColumnDictionary.Item(key))
    next

    PrimaryKeyColumnDictionary.RemoveAll
    NotKeyColumnDictionary.RemoveAll
    ForeignKeyColumnDictionary.RemoveAll
    next

    Add comment
    10|10000 characters needed characters exceeded

    • The script removes all the columns from the collection, then adds them back. What effect does this have on the column dependencies, such as mappings and traceability links? Are they lost? For example, if I delete an LDM relationship from a model in ER/Studio Data Architect, it gives me the option of keeping the FK attributes in the child entity; unfortunately it does this by deleting them and re-creating them, and you lose all the associated data lineage. At one of my clients, that's a real pain for them.
  • avatar image
    Former Member
    Jun 28, 2017 at 08:12 PM

    Mr. Lam,

    Outstanding! Just what I was looking for and more, because it did the job extremely fast. Worked through about 350 tables and 4300 columns in about 5 to 10 minutes.

    Mr. Lam, you saved me several excruciating hours of work.

    Thank you.

    George Teachman

    Add comment
    10|10000 characters needed characters exceeded