Any way to quickly get a list of all the tables that have any UD fields that have been used?
I don’t need the records or the values of those UD fields, just which tables have data in them.
One shortcut i’d be willing to explore would be just checking the first couple of UD fileds of each type.
In other words, just check for
ShortChar01 <> null OR ShortChar02 <> null
OR CheckBox01 = true OR CheckBox02 = true
OR Number01 <> 0 OR Number02 <> 0
... etc ...
Instead of all ShortChar’s [01 … 10], CheckBox’s [01 - 10}, Number’s , etc …
Especially because I assume not all tables have the same number of UD fields.
This a Progress DB, but if a SQL query existed, I’d be curious to what it would look like.
I guess my question was if there was a way to automatically step through all the tables in a DB the way you can the records in a table.
And if so, is there a way to identify the tables keys
In totally made up pseudo code
EACH table IN MfgSys (the DB)
FIRST Key1, Key2, ShortChar01, ShortChar02, CheckBox01, CheckBox02, ... IN table
WHERE (ShortChar01 <> '' OR ShortChar02 <> '' OR CheckBox01 = true OR CheckBox02 = true
OR Number01 <> 0 OR Number02 <> 0 ...)
I know what you are getting at and I had to come up with a similar solution while migrating to E10.1. It is a SQL solution, but maybe you can apply it.
SELECT t.name AS table_name, SCHEMA_NAME(t.schema_id) AS schema_name, c.name AS column_name
FROM sys.tables AS t INNER JOIN
sys.columns AS c ON t.object_id = c.object_id
WHERE (c.name LIKE ‘character%’ OR
c.name LIKE ‘number%’ OR
c.name LIKE ‘date%’ OR
c.name LIKE ‘checkbox%’ OR
c.name LIKE ‘shortchar%’) AND (t.name NOT LIKE ‘IM%’) AND (t.name LIKE ‘%_UD’ OR
t.name LIKE ‘UD%’)
ORDER BY column_name
This provided me with a list of every UD field in every table. Next, I copy and pasted those results into Excel and wrote a quick formula to generate a bunch of sql statements. From there, I just copy and pasted the sql statements back into sql management studio.
I don’t recommend running all of the lines of sql at once. There are approx 5,000 of them and it will be intensive on your server. But if you batch it by table or data type, you can pull a list of all of the UD fields that are active or being used.
It’s been over 10 years since I’ve worked with progress, but I remember being able to run simple sql statements in progress. I’ve never worked with Open Edge so I don’t know if it applies or not. The Squirrel client Vic mentioned might be useful.
One last note. I ran this against the E10.1 database, so I don’t know if the table and column names are the same as previous versions.