How to find non-used table fields in a database

Find out how to get things done with Servoy. Post how YOU get things done with Servoy

How to find non-used table fields in a database

Postby Bernd.N » Mon Oct 17, 2016 3:21 pm

When creating a large application fast, there will be fields defined that will never get used later.
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
Bernd Korthaus
LinkedIn
Servoy 7.4.9 SC postgreSQL 9.4.11 Windows 10 Pro
User avatar
Bernd.N
 
Posts: 544
Joined: Mon Oct 21, 2013 5:57 pm
Location: Langenhorn, North Friesland, Germany

Re: How to find non-used table fields in a database

Postby sanneke » Tue Jan 17, 2017 4:20 pm

Thanks for sharing this.

I will save the script for later use.

Regards,
Sanneke
Yield Software Development: Need help on your project?
yieldsd.com
User avatar
sanneke
 
Posts: 383
Joined: Thu Jun 15, 2006 9:20 am
Location: Amersfoort

Re: How to find non-used table fields in a database

Postby Bernd.N » Tue Jan 17, 2017 4:39 pm

You're welcome. I save such snippets for myself in a tiny table with a tags field, so that I will always find any script that I already used.
Bernd Korthaus
LinkedIn
Servoy 7.4.9 SC postgreSQL 9.4.11 Windows 10 Pro
User avatar
Bernd.N
 
Posts: 544
Joined: Mon Oct 21, 2013 5:57 pm
Location: Langenhorn, North Friesland, Germany


Return to How To

Who is online

Users browsing this forum: No registered users and 9 guests

cron