SQL Server xml and nvarchar columns

I wanted to achieve this by the query below , I have assigned the table names to the @table_names variable and want to now check if these tables exist in the database . If they don't then I want to print their names and raise an error .

BUT I think the IF OBJECT_ID(@TABLE_NAMES, 'U') IS NULLis failing and the print command is listing all the tables names regardless of their existence .

DECLARE @TABLE_NAMES nvarchar(MAX) =
(
        select distinct B.POP_TABLE_name + ' '
        from GEOLEVELS a
        left outer join GEOG b
        on a.GEOGid=b.GEOGid
        where   b.POP_TABLE_name is not null and 
                (a.x_COLUMN is not null and a.y_COLUMN is not null) OR
                a.z_column is not null 
                FOR XML PATH('') 

)
IF  OBJECT_ID(@TABLE_NAMES, 'U') IS NULL
PRINT 'Table not found : ' +  @TABLE_NAMES
RAISERROR('TABLE NOT FOUND %S',16,1,@TABLE_NAMES)

Solution

Your code is creating a local variable @TABLE_NAMES, then checking for a USER_TABLE (i.e. 'U') with that name.

Since you are never creating a user table with the name @TABLE_NAMES, your OBJECT_ID(@TABLE_NAMES, 'U') will always be NULL.

I think what you want is something like this:

DECLARE @TABLE_NAMES (ID INT IDENTITY(1,1), Name VARCHAR(255))
INSERT INTO @TABLE_NAMES (Name)
select distinct B.POP_TABLE_name
from GEOLEVELS a
left outer join GEOG b
on a.GEOGid=b.GEOGid
where   b.POP_TABLE_name is not null and 
        (a.x_COLUMN is not null and a.y_COLUMN is not null) OR
        a.z_column is not null 


DECLARE @Counter INT = (SELECT COUNT(*) FROM @TABLE_NAMES)
DECLARE @CurrentName VARCHAR(255)
DECLARE @TablesNotFound TABLE (TableName VARCHAR(255))
WHILE @Counter > 0
    BEGIN
        SET @CurrentName = (SELECT Name FROM @TABLE_NAMES WHERE ID = @Counter)
        IF OBJECT_ID(@CurrentName) IS NULL
            BEGIN
                PRINT 'Table not found: ' + @CurrentName
                INSERT INTO @TablesNotFound (TableName)
                VALUES (@CurrentName)
                SET @Counter = @Counter - 1
            END
        ELSE 
            BEGIN
                SET @Counter = @Counter - 1
            END
    END

IF (SELECT COUNT(*) FROM @TablesNotFound) > 0
    BEGIN
        DECLARE @ErrorTables VARCHAR(MAX) = (SELECT STUFF((SELECT ',' + TableName FROM @TablesNotFound ORDER BY TableName FOR XML PATH ('')), 1, 1, ''))
        RAISERROR(@ErrorTables,16,1)
    END

This will put all of the table names matching your criteria into a table variable. Then, it will iterate over the table (using the ID column and a counter), put the non-existent tables into an error table, then stuff the results into one text variable and push that out with the RAISERROR...it will also accomplish the printing of each table that fails. The issue you are experiencing is that all your table names are being mashed together (though separated by a space) and "SQL" sees this as just one string of text; there is nothing telling it how to distinguish the table names within the string from one another.