Query that returns the column names that have never been used (1 Viewer)

[amorosik]

The application, which you didn't bother to try, can examine tables local to or linked to ANY Access FE. It doesn't matter what RDBMS houses the BE or even how many BE's the FE links to or whether they are mixed RDBMS' or just one. The app will work with Jet/ACE, SQL Server, Oracle, DB2, Sybase, Postgre, etc!!!!!!!!!! If your Access app can link to the RDBMS and work with it, then pick the FE from the dialog and that is ALL you need to do.

The output is a table with ONE row per table in the analyzed database that has ANY columns where all the rows are empty. So, if the BE has 100 tables and only two of them have any columns where any column has no value on any row, then the output will be 2 rows with as many columns (up to 20 - which you can easily expand with NO CODE CHANGES (OK, you have to change the number 20 to the new limit or remove the validation))

The only reason I bothered with creating this was because I thought it to be a useful utility but not the way anyone had presented it to date. Having to read through dozens of lines of spaced out text in the debug window was not a useful way to look at the output for anything more than a couple of tables. My altered solution can display results for as many tables as your app is linking to in a format that is concise and easy to understand and use.

You're welcome.

"..The application, which you didn't bother to try,.."
The application, which I took the trouble to test, can surely do everything you says
And i thank you very much for the help provided
But it doesn't answer the original question
The question is whether it is possible, by passing only the name of a table, with a single query to have returned fields that have never been used (which return null) on any row of data
This was the question and this remains
The problem of finding unused fields on a table has already been solved a few months ago
And therefore the routine is already available in my tools bag
What I wanted to understand was if it was possible to do all the work that is currently done with code with a single query
Where by 'query' I mean one of the queries that can be used with Access and therefore also the passthrough (it seems that lately it is necessary to specify this thing)

"..You're welcome..."
Thank you

 

[ebs17]

My question IS about Access queries

This sentence is unmistakable. Access primarily uses Jet-SQL. This is not an assumed condition but a fact, like jumping in the water will get you wet.

The SQL optimizer (also in Jet/ACE) uses internal table statistics to determine execution plans for queries. So something like the counted contents in fields will already exist. However, access to it is not included in normal SQL (Data Manupulating Language). Point. The simple magic wand for all conceivable DBMS, as you imagine it, does not exist as a query, unless you create table statistics yourself as shown (including table definitions / data definition language).

You can believe and pray and hope for miracles. I've repeated myself often enough. Any additional word is a waste of time.

 

[amorosik]

You're saying it's not possible to do something that I showed you how to do in post #35
Yes, I agree with you, I believe that every added word is useless

 

[Pat Hartman]

You have been told multiple times that there is no way to answer the question without program logic whether it be VBA or a stored procedure. I gave you a solution that easily solves the problem REGARDLESS of the BE. AND it provides the answer concisely in a table format.

DDL is a very small language. It cannot switch from table to table on the fly. The VBA code looks at each table in the set and builds a query to count the contents of each column so YES you can do this with a query if YOU are willing to write hundreds of queries manually. The VBA is AUTOMATING the building of the individual queries, running them and logging the results.

LOOK at the query built by the code. Feel free to build that query manually and you have an answer but only for ONE table.

I'm done too.

 

[The_Doc_Man]

You're saying it's not possible to do something that I showed you how to do in post #35
Yes, I agree with you, I believe that every added word is useless

Your post #35 will work with SQL Server, I presume, because SQL Server exposes its metadata.

For a generic SQL statement you will not succeed with queries because if you stay generic, you will be forced to use ANSI SQL, which does not expose metadata. That requires specific knowledge of the vendor's method of storing metadata, and every one of them does it in a different way. It also requires a way to make indirect references in SQL, and again, not every version of SQL allows indirection. So this would be a case where one size does not fit all.

For an Access "native" back-end or for tables included in a front-end or monolithic DB, you will not succeed with pure SQL because while Access tables names CAN be identified, the interface for a single query will not transfer field names so easily to support a query on specific fields.

I will not insult you, amorosik, but I will say this: You have been persistent to the point that you have alienated some people. The reason for that alienation is that your repeated questions on the same topic imply that you know more than they do since you won't take NO for the answers they give you. Some databases simply won't do what you asked about. Many will not because of your initial question:

I need a query that returns, given the name of a table in the db, all the names of the fields that have never been filled in any row of the data present
Basically the fields I could also delete because at the moment they don't contain any information
How to make a unique query that return the field name as above?

A unique query probably cannot be written in most variants of SQL. This query cannot happen unless the vendor exposes the schema as tables accessible by queries. Not all vendors do that. You also gave us this statement:

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

But then there was a key sentence:

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

The flaw in this statement is that NOT ALL VENDORS DO THIS! The ones that expose their metadata as tables might be able to do this. The ones that don't expose their metadata won't. Access metadata can be read, but the problem would then be to, in a single query or in a sub-query transfer table and field names to the scanning query that would do the actual "empty column" test given the table name you wanted to input. In your post #35, you did indeed show us something from SQL Server. That query cannot be run against the JET or ACE DB engines in Access itself because those structures don't exist in that format in Access.

In #41, you asked us: "Are you telling me you don't know how to run a passthrough query?" - so that means we ARE talking about a non-Access version of SQL where you can send statements to that server that cannot be run on Access SQL. In post #42, you named other SQL vendor products and said this:

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)

So that means you are talking about trying to find a generic query, not an Access or SQL Server query? I answered you directly in post #48 and then in post #51 gave you a reason based on Language Theory (related to SQL) why you couldn't do what you wanted in the general case.

You also said "Let's leave barking to dogs, not humans or programmers" in response to folks who had trouble following your leaps of focus. While that is less vulgar than other comments in this thread, it was also disrespectful. Just as I asked others to back down, I will also ask you to back down.

In summary, the answer to your original question plus the variations you have asked, is:

NO you cannot do that kind of work in a generic query or set of queries that would run on generic SQL. For the generic case, to do what you asked would violate linguistic rules associated with SQL and would violate Set Theory concepts as applied to SQL. The metadata you would need to examine belongs to a metadata set that is not an exposed part of the primary data set.

MAYBE you can build such a query when running under specific vendors. But even there, you cannot do it in a single query because of the need to breach the logic wall between ordinary data and metadata. I suspect you would need several layers of query to do that.

 

[Pat Hartman]

Original question:

I need a query that returns, given the name of a table in the db, all the names of the fields that have never been filled in any row of the data present
Basically the fields I could also delete because at the moment they don't contain any information
How to make a unique query that return the field name as above?

Doc's assessment:

NO you cannot do that kind of work in a generic query or set of queries that would run on generic SQL.

Nothing in your original question indicated this was a non-Access problem. Why would you ever post the question in an Access forum if the solution would be unacceptable.

My solution seems to provide the EXACT result you want. Here is the result of the procedure and it is a query that gives the names of all tables and all columns within said table that are null in every row. You could easily take this query and use it to generate Alter statements in WHATEVER SQL dialect you need. The sample is from a FE that includes BOTH local and SQL Server linked tables. You can even include the database and connection string on each row to further automate the process. ONLY tables with at least one empty column are logged. The sample had about 50 tables, some quite large but none of them had totally empty columns.

PS - I ran with a different BE this time and I did just find a bug which I'm not going to fix for you, you can fix it yourself. Count() doesn't work on "ntext" or "Long Text" fields, so you would need to exclude fields of that data type or handle them using a different method.

 

[HealthyB1]

I liked the idea of this so I cleaned it up a little and made it easier to use.

Great piece of code Pat. I developed several Access DB's many years ago and upgraded same many times as customer needs changed. Always saying after I sorted out the bugs I'd go back and delete old versions of unnecessary tables and remove unused fields. Your application is fantastic. Thanks for sharing!

 

[Pat Hartman]

You're very welcome Thank you for noticing --- Remember to fix the bug for the long text data type. I put enough code in the version I uploaded so it doesn't stop, it just ignores the error and moves on. My additional "notes to self" in the app include adding the database path/name as well as the connection string. Those two should give you enough information to generate the necessary Alter statements in whatever dialect is necessary. The big difference between the Access and SQL server syntax is that Access substitutes an underscore for the dot in the SQL Server table names. Otherwise the DDL for DROP should be the same. The DDL is different for Create because the data type names are different.

Here's the version with the partial fix.