Around 50 % of the tables in a certain SQL Server database have either zero, one or two rows - is this a sign of a good design?
How many of the tables are not connected to any other table by a FOREIGN KEY relationship? You can do this by checking out the answer here.

It is possible to have tables with 0 records - if you ran a nuclear power plant for example, you'd want the table catastropic_failure to have 0 records.


Some reference tables could only have 1 or 2 reference codes - maybe the devs thought they'd be added to later? 

I would say you're looking at a problem of orphaned tables - you've heard of orphaned records - records with no parents in parent-to-child relationship? These tables been lost and forgotten in the development process.  

It was somebody's "bright idea" one day but was subsequently vetoed by senior dev/management - but such was the hothouse and/or firefighting atmosphere in the big open plan room where all the devs worked, that these "bright ideas" were never removed from the code base.  

It's also possible that these tables are useful for 1 or 2 clients but not used by the bulk of the system users/clients/whatever?

Another possibility is that you should be careful that the coding style of your establishment (maybe prior to your arrival?) wasn't to use various tables as temporary storage - they are used briefly to store bits and pieces (important nonetheless) of data which aren't properly cleared down until they are used the next time?

What I would do:

1) try and search through your APPLICATION code and see if you can find tables that have no references and 0 records in the code - delete those - TAKE BACKUPS FIRST. 

Be especially careful of triggers which may refer to those tables and also stored procedures which reference them as well.

Do you have a test suite for your code - I'm guessing not?

Then do the same with those tables which have 1 and then 2 records. You will probably find a few that can't be deleted without provoking obvious problems. Then there are those which will cause problems which will pop up days/weeks/months/years later. The answer here is it depends on what risks you want to take.

Find the dev and/or managers who've been on the project longest and ask them about this issue - that's probably your best best. By my "rule of thumb" reckoning, you should have, at most, say 10% of your tables in the form you describe. However, unfortunately, it's going to be a difficult mess to clear up. 

Best of luck with your project and welcome to the forum! :-)