In case you like to find and delete those fields to keep your tables nice and meaningful, you might want to find out which fields are really never used and have therefore only NULL values.
Such an SQL function could be run also at all customers, to make sure that no real data is deleted.
In such cases I always ask Sergei Sheinin to code an SQL for me, and here it is.
The SQL will create another (large) SQL, that can then be run and will produce the desired output.
Sergei is available as a freelancer, by the way, also for JavaScript and data warehouse tasks.
Of course, you also need to search inside your Servoy sourcecode to make sure a field has no references.
One way to do this is a right click on the field name in the Servoy database table editor (and certainly, for Servoy newbies: Ctrl+H).
This is a SQL for postgres, in case you use other DBs some changes might be needed due to different information schemas inside that DB.
I suggest to give it a first try only with AND table_name = 'my_table_name' , as the produced SQL can get large for big DBs.
My idea is to use later AND table_name BETWEEN 'a' AND 'd' and so on, so that I can work through some tables step by step.
- Code: Select all
SELECT stmt FROM (
SELECT 'SELECT ''-- this is a placeholder for extra UNION at first row''' AS stmt
UNION
SELECT 'UNION SELECT ''' || table_name || '.' || column_name || ''' FROM ' || table_name || '
WHERE NOT EXISTS (SELECT 1 FROM ' || table_name || ' WHERE ' || column_name || ' IS NOT NULL)' AS stmt
FROM information_schema.columns
WHERE table_schema = 'public'
-- ** FILTER BY TABLE NAME E.G. WITH table_name IN () AS NEEDED, OR WITH ANY LIKE-PATTERN, OR FOR JUST ONE TABLE AS IN NEXT LINE **
-- AND table_name = 'address_formats'
) a
ORDER BY stmt