Query that returns the column names that have never been used

Hence the 'lot of code'!
I thought the OP wanted to do one table but rereading his post, I see that he wants to do some unspecified number. Once you want to do multiples, you're stuck with code but I would still create one query per table rather than one query per field per table. Using Max() should result in one complete pass through each table assuming the Max() expressions can be updated as each record is read. Doing a separate query per field could get expensive depending on the number of rows/columns in each table. An average of 30 fields and 50 tables results in 1500 queries to run rather than 50 if you do one per table.
 
An example with Count(Field) and one record per table.
Code:
Const ResultTableName As String = "tab_NotUsedColumns"

'Start this:
Private Sub SearchNotUsedDataFieldsInAllTables()

   Dim db As DAO.Database
   Dim tdf As DAO.TableDef
   Dim TargetRs As DAO.Recordset
  
   Set db = CurrentDb
   If ResultTableExists(db) Then
      db.Execute "delete from " & ResultTableName
   Else
      CreateResultTable db
   End If
  
   Set TargetRs = db.OpenRecordset(ResultTableName, DAO.RecordsetTypeEnum.dbOpenDynaset, dbAppendOnly)
  
   For Each tdf In db.TableDefs
      SearchInTable db, tdf, TargetRs
   Next

   TargetRs.Close

End Sub

Private Sub SearchInTable(db As DAO.Database, ByVal tdf As DAO.TableDef, ByVal TargetRs As DAO.Recordset)

   Const FieldNamePrefix As String = "fld"

   Dim fld As DAO.Field
   Dim SqlText As String
   Dim rs As DAO.Recordset
  
   For Each fld In tdf.Fields
      SqlText = SqlText & ", " & CreateFieldSqlString(fld.Name, FieldNamePrefix)
   Next
   SqlText = Mid(SqlText, 3)
   SqlText = "select " & SqlText & _
             " from [" & tdf.Name & "] as T" & _
             " having count(1) > 0"
  
   Set rs = db.OpenRecordset(SqlText, DAO.RecordsetTypeEnum.dbOpenForwardOnly)
   If Not rs.EOF Then
      CheckRecordsetFields rs, TargetRs, tdf.Name, FieldNamePrefix
   End If
   rs.Close

End Sub

Private Function CreateFieldSqlString(ByVal FldName As String, FieldNamePrefix As String) As String
   CreateFieldSqlString = "Count([" & FldName & "]) as [" & FieldNamePrefix & FldName & "]"
   ' FieldNamePrefix .. to prevent error with reserved words as alias
End Function

Private Sub CheckRecordsetFields(ByVal rs As DAO.Recordset, ByVal TargetRs As DAO.Recordset, ByVal TabName As String, ByVal FieldNamePrefix As String)

   Dim fld As DAO.Field
   Dim TabNameTargetFld As DAO.Field
   Dim DataFieldNameTargetFld As DAO.Field
  
   Set TabNameTargetFld = TargetRs.Fields("TableName")
   Set DataFieldNameTargetFld = TargetRs.Fields("DataFieldName")
  
   For Each fld In rs.Fields
      If fld.Value = 0 Then
         TargetRs.AddNew
         TabNameTargetFld.Value = TabName
         DataFieldNameTargetFld.Value = Mid(fld.Name, Len(FieldNamePrefix) + 1)
         TargetRs.Update
      End If
   Next

End Sub

Private Function ResultTableExists(db As DAO.Database) As Boolean
  
   Dim tdf As DAO.TableDef
   For Each tdf In db.TableDefs
      If tdf.Name = ResultTableName Then
         ResultTableExists = True
         Exit Function
      End If
   Next
  
   ResultTableExists = False
  
End Function

Private Sub CreateResultTable(db As DAO.Database)

   db.Execute "create table " & ResultTableName & " (TableName varchar(255) not null, DataFieldName varchar(255) not null, CONSTRAINT PK_" & ResultTableName & " PRIMARY KEY (TableName, DataFieldName))", dbFailOnError
   Application.RefreshDatabaseWindow
  
End Sub
 

Attachments

Last edited:
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.
 
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
 
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.
 
Last edited:
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')
 
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
 
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
 
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.
 
Last edited:
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'?
 
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.
 
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'

1683196114381.png



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 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
 
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.
 
[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? :unsure:
But then there would be a possibility to solve this only with SQL.
 
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.
 

Users who are viewing this thread

Back
Top Bottom