Mike wrote:
I was just wondering if anyone else has found a way to find any record in any and all tables in a Firebird database that should not be null, but is.
We all know how we get here. A field is added that allows nulls when first created and then it is later changed to not allow nulls.
Any ideas on how this could be done or if there is already any tools out there that will do this?
Ivan Prenosil answers:
This is from my presentation on FBCon2008-Bergamo.
EXECUTE BLOCK RETURNS ("Table" VARCHAR(31), "Field" VARCHAR(31)) AS
DECLARE VARIABLE stmt VARCHAR(1000);
DECLARE VARIABLE flg INTEGER;
BEGIN
FOR SELECT R.RDB$RELATION_NAME, RF.RDB$FIELD_NAME
FROM RDB$RELATIONS R JOIN RDB$RELATION_FIELDS RF ON R.RDB$RELATION_NAME=RF.RDB$RELATION_NAME
WHERE (R.RDB$SYSTEM_FLAG = 0 OR R.RDB$SYSTEM_FLAG IS NULL)
AND R.RDB$VIEW_BLR IS NULL AND R.RDB$EXTERNAL_FILE IS NULL
AND RF.RDB$UPDATE_FLAG = 1
AND (RF.RDB$NULL_FLAG = 1 OR EXISTS(SELECT * FROM RDB$FIELDS F
WHERE RF.RDB$FIELD_SOURCE=F.RDB$FIELD_NAME AND
f.RDB$NULL_FLAG = 1))
ORDER BY R.RDB$RELATION_NAME, RF.RDB$FIELD_POSITION
INTO :"Table", :"Field"
DO
BEGIN
stmt = 'SELECT FIRST 1 1 FROM ' || "Table" || ' WHERE ' || "Field" || ' IS NULL';
flg = 0;
EXECUTE STATEMENT :stmt INTO :flg;
IF (flg = 1) THEN
SUSPEND;
END
END
If it is not fast enough for you, you can further enhance it to query all not-nullable fields of one table in single pass.