Find bit fields with no default

SQL Apprentice Question
Does anyone know how to write a select to search all tables in a database
and return the table name and column name of any bit data type that allows
nulls? I inherited a database where some bit fields apparently do not have
a default value of 1 or 0.


Celko Answers
>> Does anyone know how to write a select to search all tables in a database and return the table name and column name of any bit data type that allows nulls? <<


After that, you need to re-write this piece of crap to get rid of
assembly language style programming. The schema is a nightmare.


>> I inherited a database where some bit fields [sic] apparently do not have a default value of 1 or 0. <<


That is not the real problem. It is a screw-up on a baaaaad design.
You should never be using assembly language bits in SQL. I also hope
that you know that columns and fields are absolutely nothing alike.




SQL Apprentice Question
Do you suggest using tinyint instead for a column that is simply true or
false?

Celko Answers
>> Do you suggest using tinyint instead for a column that is simply true or false? <<


You still thinking in terms of old procedural languages. Look at VB
and C# -- they do not agree on 0, 1, or -1 for Boolean values! Bits do
not port evern with proprietary languages inside Microsoft. In fact
this is a real mess in the X3J language standards.

But more importantly, I have found that assembly style flags are
usually redundant and dangerous. Instead of deducing a fact from a
logical predicate, the relational declarative way of programming, the
programmer is set a flag like we did with punch cards.


An actual example was an Inventory that had "is_instock" flag when
"(qty_on_hand > 0)" gave the same information. The bad news was that
the flag and the qty_on_hand had no relation to each other until the
guy did an update. His thought was to add a CHECK() constraint and
make it worse! Then they ran into the (1, -1) problem with the front
end languages.


My advise is to sit down and design a status code. be sure to leave
room for expansion. For example, the inventory status might include
"in stock", "back ordered", "on regular order" and then needs to add
"discontinued", etc. in the future.


I have the basics for such designs in SQL PROGRAMING STYLE. Yes, doing things right is work and I know that lot of XP-Agile-cowboy coders hate
that. Think high level abstract, not low level bit flags.