Query that returns the column names that have never been used

[amorosik]

How to find out what columns in a table are never used ?

For example, in a table CUSTOMERS there is 5 column, NAME, SURNAME, ADDRESS, CITY, EMAIL The CUSTOMERS table already contains thousands of rows But the CITY field has never been valued on any line Here, the question is: how to find the columns within a table, which have never been filled in...

www.access-programmers.co.uk

It would be possible that only people should be employed here, but that there is no problem to be solved. 5 months without progress speaks for itself.

The problem to be solved is written in the initial post:
QUERY that return...
Which is different from the problem posted 5 months ago
I thought it was a trivial thing, but I see that it is not

 

[amorosik]

I see that he wants to do some unspecified number.

I don't understand, what do you mean exactly?
Maybe I didn't explain myself correctly in the initial request?

 

[ebs17]

I thought it was a trivial thing

In tables of a proper database schema, this should only occur in exceptional cases, and a manual check is sufficient there. In the planned schema are fields that make sense and are not easy to delete. I can't let go of any automatism.
In the case of supplied tables that supply export data, for example, one would first check what that is, who and why creates something like that. Perhaps an UNPIVOT is simply missing when you think of the popular Excel matrices.

In any case, I'm critical of a large, uncritical VBA action here. In the beginning there was the question of who creates such crap and why.
Or are the introductory words missing: I'm going for a bunch of chaos.

 

[amorosik]

If the world were ideal it would be called Heaven
But this is not the case, and we must "adapt and achieve the goal" (cit. 'Gunny')

 

[CJ_London]

The problem to be solved is written in the initial post:
QUERY that return...

and a QUERY provided in post#2.

Your response

Already now I have a routine that iterates through every single field, and checks if it has ever been valued within the table
But it's incredibly slow

implies you have not tried it

 

[amorosik]

and a QUERY provided in post#2.

Your response

implies you have not tried it

Yes on post#2 there is a query
But it doesn't respond to the initial request
"... a query that returns, given the name of a table in the db, all the names of the fields that have never ..."
Your query need to know also the single field name
The only parameter that I can give in input to the query is the table name

 

[ebs17]

A query (SQL) returns contents of fields (record sets), not field names, not table names. It cannot do the latter.
In an ideal world, you understand and master your tools of the trade.

 

[amorosik]

A query (SQL) returns contents of fields (record sets), not field names, not table names. It cannot do the latter.
In an ideal world, you understand and master your tools of the trade.

As you surely know, there are methods that allow you to 'read' the names of the 'objects' contained in the db, whatever it is
Whether we are talking about Sql Server, Oracle, Db2, Postgresql, tiny SqLite or other
And it is therefore possible to launch a query and get the name of the objects in response, if these objects were data tables, then the query will return the names of the tables, while if the objects were the names of the single fields of a table then a single query could return the field names of that table
What is written above is only a feature that you need to use to answer the initial question
But you surely already know this
And yet you're writing to me that it can't be done
Why'?

 

[cheekybuddha]

In Access, you can use a query to get the names of tables, but it is not possible to get the names of the table fields like in other DB's.

Access stores all this info in the TableDefs collection object.

 

[amorosik]

In Access, you can use a query to get the names of tables, but it is not possible to get the names of the table fields like in other DB's.

Access stores all this info in the TableDefs collection object.

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME like 'ART_ANA'

This query, launched by Access to Sql Server, return all field name on ART_ANA table
Keeping data inside an Access file I don't even consider it

 

[cheekybuddha]

So, run that query in Access. What do you get?

 

[CJ_London]

you are all over the place. you said

given the name of a table in the db, all the names of the fields

you give the query the name

you now say

there are methods that allow you to 'read' the names of the 'objects' contained in the db, whatever it is

but exclude those methods because the way you do that in Access VBA, which you don't want to use

As with some of your other threads, you are just being contentious

there are methods that allow you to 'read' the names of the 'objects' contained in the db

there is in access - run a query on the msysobjects table

But you cannot get the names of the fields with SQL - there is no 'column_name' functionality in access sql. For that you would need to write a VBA function

 

[ebs17]

you can use a query to get the names of tables

If this means a query on MSysObjects: Table names can only be evaluated there as the contents of a field.

 

[Josef P.]

[SQL-Server]
This query, launched by Access to Sql Server, return all field name on ART_ANA table
Keeping data inside an Access file I don't even consider it

You are asking for a query to list all unused data fields and you don't write that you are using SQL server as backend?
But then there would be a possibility to solve this only with SQL.

 

[cheekybuddha]

This query, launched by Access to Sql Server, return all field name on ART_ANA table

If you had said that you are working with SQLServer tables then we could have given you a query that might do what you want.

You might still need some code (VBA or T-SQL) to get the information about which fields contain no data - I can't remember whether SQLServer keeps those statistics on individual fields.

In MySQL there are such statistics, but they are not always current.

 

[amorosik]

"..but exclude those methods.."
No, you are writing things that are not true
What I exclude is cycling hundreds of fields to compose a query, but what I have shown above is a very different thing, it is a single query that returns as many records as there are fields that make up the indicated table

"..As with some of your other threads, you are just being contentious.."
No, i'm asking a specific thing, and on a tech forum, I would expect an answer to the question

"..there is no 'column_name' functionality in access sql.."
I demonstrated above a single query to read the field names of a table
Are you telling me you don't know how to run a passthrough query?

 

[amorosik]

You are asking for a query to list all unused data fields and you don't write that you are using SQL server as backend?
But then there would be a possibility to solve this only with SQL.

And who said I'm using Sql Server?
I only made one example, on Sql Server, to answer the user cheekybuddha
But the question asked is a query on a generic db, then Sql Server will have its own system to read the fields of a table, Postgresql will have another system, Firebird will have its own system, this is not the important thing
The important thing is how to set up the query to get the names of the fields never used (which I think are the ones that return all null in all rows)

 

[amorosik]

If you had said that you are working with SQLServer tables then we could have given you a query that might do what you want.

You might still need some code (VBA or T-SQL) to get the information about which fields contain no data - I can't remember whether SQLServer keeps those statistics on individual fields.

In MySQL there are such statistics, but they are not always current.

Ok to simplify things, since Sql Server is probably a db familiar to many users of this forum, let's suppose we are dealing with Sql Server

 

[cheekybuddha]

I only made one example, on Sql Server, to answer the user cheekybuddha
But the question asked is a query on a generic db, then Sql Server will have its own system to read the fields of a table, Postgresql will have another system, Firebird will have its own system, this is not the important thing

I answered this in Post #34

In Access, you query the TableDefs collection object (using VBA)

Ok to simplify things, since Sql Server is probably a db familiar to many users of this forum, let's suppose we are dealing with Sql Server

See here on StackOverflow

See here on StackOverflow

 

[amorosik]

@amorosik Did you bother to try the database I uploaded back in #25? It works against ANY db whether the tables are local or linked. If you have a FE that is linked to SQL server, select that. Otherwise, if the BE is jet or ACE, select that. The code does ALL the tables in the linked database and produces a concensed output rather than the other examples which output using dbug print and are hard to work with.

The table I built to hold the output only holds 20 columns for "empty" column names. Just add more if you have wide tables that have more than 20 empty columns. Just use the same naming pattern so you won't break the code. You can go up to 253. I used two columns for other things.

Yes I saw, thank you
But since the work is essentially done via vba by scrolling through the fields and composing the query, I don't consider it useful for the purposes of the current request