asked on

Find unused columns in a table

Hi Experts,

I have an extremely large table with hundreds of fields, with many of them not in use anymore, and would like to get a list of all those unused fields, and perhaps move them to a different table.

Does anybody know of a script/function that would help me in this direction, instead of having to deal with each field separately?

Thanks in advance.

Paul Cook-Giles

Is this SQL table linked to an Access front end? And by 'unused' do you mean 'unpopulated'... no entries in the field in any record?

Aneesh

Unfortunately there is nothing out there.

PatHartman

If you have used querydefs rather than embedded SQL, Total Access Analyzer by FMSINC.com might be helpful. It creates excellent cross references of where objects are used. However, it doesn't examine VBA for embedded SQL.

Aneesh

My suggestion was for SQL Server

bfuchs

ASKER

Is this SQL table linked to an Access front end?

Yes.

no entries in the field in any record?

No, I mean fields that were used in the past and do have data, however since requirements got changed and therefore they're not in use anymore, lets say for last half year or so there were nothing entered there. (We do have a DateEntered field for each record, perhaps some info can be retrieved from there..)

If you have used querydefs

There are many ways this table can be updated, starting from a bound form in FE, users updatable queries and even some code that can update fields behind the scene..

Thanks,
Ben

ASKER CERTIFIED SOLUTION

Paul Cook-Giles

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bfuchs

ASKER

@Paul,
I get attached error in following line

      If DCount("ID", strTableNa, fld.name & " is not null and UploadDate > #" & dtCutOffDt & "#") = 0 Then

Open in new window

FYI- I have A2003 if that matters.

Thanks,
Ben
Untitled.png

Paul Cook-Giles

You need to change "UploadDate" (the field in my table) to "DateEntered" (the field in yours) for the code to work...

bfuchs

ASKER

This seems to work well..although users may perform updates on previously created records, however as others here have mentioned, there is nothing build into SQL to give such reports, so we have to deal with what we have..

Thank you!

crystal (strive4peace) - Microsoft MVP, Access

actually, there IS something to tell you if there are "no entries in the field in any record?" -- and it is free.

Analyzer for Microsoft Access (works on linked tables too)
http://analyzer.codeplex.com/

bfuchs

ASKER

no entries in the field in any record?

No, I was not concerned about that as all the fields were used at one point in time, what I was looking for is which fields were not recently used, which means they were removed from the FE application and therefore should be taken out from the BE as well..

Thanks,
Ben

PatHartman

I realize that Total Access Analyzer is not free but it is your best bet. It doesn't matter that the tables are SQL Server. What matters is that the application uses querydefs rather than embedded SQL. Although, I'm going to guess that no one bothered to update the queries when they stopped needing the data field. If they didn't remove the field from the queries, then there will be no way short of manual examination to find unused fields.