Identifying Empty columns

Question

Identifying Empty columns

EamonSQL

SSCrazy

Points: 2307
More actions
February 10, 2020 at 12:19 pm

#3724316

Hi
is there a way to identify empty columns.
I'm going through a new database right now and dropping columns that have never been used.
I've been generating SQL statements to get the max length value of each column and identifying columns that return NULL
If there is an easier way to do this I'd appreciate it.
Thanks,
Eamon

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398712 More actions · Answer 1

I'm not aware of any way of doing this.

Strong suggestion, have a copy of your database in source control so that you can easily restore columns after you find out that they're somehow in use, but NULL when you query them.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 2

EamonSQL wrote:

Hi
is there a way to identify empty columns.
I'm going through a new database right now and dropping columns that have never been used.
I've been generating SQL statements to get the max length value of each column and identifying columns that return NULL
If there is an easier way to do this I'd appreciate it.
Thanks,
Eamon

I admire your tenacity but dropping even truly empty columns with great prejudice is a recipe for disaster. You don't know how the front end is setup and you could be breaking major amounts of front end and other code. Empty columns <> Unused columns.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

EamonSQL SSCrazy Points: 2307 More actions · Answer 3

Hi,

there isn't a front and end the columns dropped have never been used. It was originally put together with too many columns and lots of good intentions.

I do understand your comments and thanks for this. I'm just tidying up and very loosely built system.

Thanks

jcelko212 32090 SSCrazy Eights Points: 9295 More actions · Answer 4

To add to what Grant said, you might also want to look out for constraints, triggers, and references on those empty columns..

Please post DDL and follow ANSI/ISO standards when asking for help.

jonathan.crawford SSCertifiable Points: 6611 More actions · Answer 5

...and every report in your system that your team or any other reporting team has built, to see if they are using those NULL columns. Or, as previously mentioned, blow them away but keep a copy you can restore with them still in there, so that if someone yells you can fix it.

-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

GaryV Hall of Fame Points: 3307 More actions · Answer 6

Also beware of any queries, views, procedures or reports that use "SELECT * FROM ..." one of these tables you propose to change.