S> The database is not available to the outside world and it runs
S> on Windows Server 2003. If there is anything you could do let me
S> know.

Hallo Mike,

I frequently try helping dBase/Paradox users to switch to Firebird,
and ... well... a "typical" dBase user often finds that situation you
described.

Here is something from my portfolio for beginner's workshops. Note
please, that I did not need this for a long time, and I don't have
Null violations anywhere - but it should work with 1.5.x

It lists all NotNull fields and does a count on how many records are
violating that constraint.

For _REALLY_ big databases you can just use the input parameters
IP_NNStart and IP_NNTables. [1, 10] will scan tables 1-10, [51,20]
will scan tables 51-70 and so on...


CREATE PROCEDURE SYS_CHECKNULLS (
IP_NNSTART SMALLINT,
IP_NNTABLES SMALLINT)
RETURNS (
OP_DESCOBJECT BLOB SUB_TYPE 1 SEGMENT SIZE 80,
OP_FIELDNAME VARCHAR(18),
OP_TABLENAME CHAR(18),
OP_DESCFIELD BLOB SUB_TYPE 1 SEGMENT SIZE 80,
OP_VIOLATIONS INTEGER)
AS
DECLARE VARIABLE V_DESCOBJECT BLOB SUB_TYPE 1 SEGMENT SIZE 80;
DECLARE VARIABLE V_TABLENAME CHAR(31);
DECLARE VARIABLE V_DESCFIELD BLOB SUB_TYPE 1 SEGMENT SIZE 80;
DECLARE VARIABLE V_COMMAND_1 VARCHAR(1024);
DECLARE VARIABLE V_FIELDNAME CHAR(31);
DECLARE VARIABLE V_COMMAND_2 VARCHAR(1024);
DECLARE VARIABLE V_VIOLATIONS INTEGER;
begin
V_Command_1 =
'select
first '||:IP_NNTables||' skip '||(:IP_NNStart-1)||'
RDB$Relations.RDB$Description "Desc. Object",
RDB$Relation_Fields.RDB$Field_name "Field",
RDB$Relation_Fields.RDB$Relation_name "Table",
RDB$Relation_Fields.RDB$Description "Desc. Field"
from
RDB$Relations
join
(RDB$Relation_Fields
join
(RDB$Fields
join
RDB$Types
on RDB$Fields.RDB$Field_Type = RDB$Types.RDB$Type)
on RDB$Relation_Fields.Rdb$field_source = RDB$Fields.RDB$Field_Name)
on RDB$Relations.RDB$Relation_Name = RDB$Relation_Fields.RDB$Relation_Name
where
rdb$relation_fields.rdb$null_flag = 1 and
RDB$Types.RDB$Field_Name = ''RDB$FIELD_TYPE''
order by
3,2 ';

FOR
execute Statement V_Command_1
into
:V_DescObject, :V_Fieldname, :V_Tablename, :V_DescField
do
begin

V_Command_2 =
'select
count('||:V_Fieldname||') "NotNullViolations"
from
'||:V_Tablename||'
where
'||:V_Fieldname||' is null ';

V_Violations = 0;
For
execute Statement V_Command_2
into
:V_Violations
do
begin

if (:V_Violations is null) then V_Violations = 0;

OP_Violations = :V_Violations;
OP_DescObject = :V_DescObject;
OP_FieldName = :V_FieldName;
OP_TableName = :V_TableName;
OP_DescField = :V_DescField;
suspend;
end
end
end




~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
beta Eigenheim- und Grundstücksverwertungsgesellschaft mbH
Hafenweg 4
59192 Bergkamen-Rünthe

Telefon: +49 2389 9240 0
Telefax: +49 2389 9240 150
e-mail: info@...

Amtsgericht Hamm Nr. B 420 || USt-IDNr.: DE 125215402
Geschäftsführer: Achim Krähling, Dirk Salewski, Matthias Steinhaus