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

[former_member1238734]

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

[former_member200945]

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

[GeorgeMcGeachie]

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.

[former_member1238734]

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