How to count across multiple tables in a DB?

Thank you in advance for your assitance. I am trying to write a query that will query multiple tables for the same column. All the tables have thsi column "szF11". I am wanting something similar to this:

Code Snippet

SELECT count(ulID)

FROM (dbo.F_ACCOU_Data UNION dbo.F_AGNCY_Data UNION dbo.F_APPEA_Data UNION etc.....)

WHERE szF11 = ' '

Note: ulID is the name of a column that every table has and szF11 is also in every table.

Pseudo Code: I want to count how many ulID's (if there is a row then something is in the ulID column it is never blank) in all the tables that are listed that have a blank in the szF11 column.

I am getting a very cryptic error message and of course I can't find anything in the documentation to help me understand the error.

Thanks,

Erik

You have a few options.

You could do use a derived table:

Code Snippet

SELECT COUNT(uID)
FROM
(SELECT uID, szF11
FROM F_ACCOU_Data
UNION ALL
SELECT uID, szF11
FROM F_AGNCY_Data
UNON ALL
SELECT uID, szF11
FROM F_APPEA_Data
UNION
etc....) AS Tbl
WHERE sF11 = ''

A variation on this could be that you include the WHERE clause for each table within Tbl

Code Snippet

SELECT COUNT(uID)
FROM
(SELECT uID, szF11
FROM F_ACCOU_Data
WHERE sF11 = ''
UNION ALL
SELECT uID, szF11
FROM F_AGNCY_Data
WHERE sF11 = ''
etc...) AS Tbl

Another option would be to include the tablename in the derived table so you can find out how many rows per table

Code Snippet

SELECT TableName, COUNT(uID)
FROM
(SELECT uID, 'F_ACCOU_Data' AS TableName, sF11
FROM F_ACCOU_Data
UNION ALL
SELECT uID, 'F_AGNCY_Data', sF11
FROM F_AGNCY_Data
etc...
) AS tbl
WHERE sF11 = ''
GROUP BY TableName

HTH!