Sql Server Query: Get A List Of Columns Which Don't Exist In Another Table's Field

I need to select all the columns names only from a SPLANNING_ROOMDATA2 table in such way that those names should not exist in a comma separated list of column names in another tabl

Solution 1:

You should not store lists as comma separated values. They should be in a separate table, called a junction table with one row per column and table.

Sometimes, though, you are stuck with such a structure. Here is one method for getting what you need:

select c.COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS c LEFTJOIN
     SPLANNING_RESTRICTED_ATTRIBUTES ra
     ON','+lower(ra.ATTRIBUTENAME) +','LIKE'%,'+lower(c.column_name) +',%'where TABLE_NAME ='Splanning_RoomData2'and ra.ATTRIBUTENAME isnull;

Solution 2:

Use AND instead of WHERE twice

SqlCommand cmd = new SqlCommand("SELECT COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME='Splanning_RoomData2' 
AND COLUMN_NAME NOT IN (SELECT ATTRIBUTENAME 
                        FROM SPLANNING_RESTRICTED_ATTRIBUTES)", con);

Solution 3:

I am just posting the updated answer of @Gordon Linoff answer try the query as,

select c.COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS c JOIN
     SPLANNING_RESTRICTED_ATTRIBUTES ra
     ON','+lower(ra.ATTRIBUTENAME) +','NOTLIKE'%,'+lower(c.column_name) +',%'where c.TABLE_NAME ='Splanning_RoomData2';