Detect if any values in NVARCHAR columns are actually unicode
Suppose one of your columns does not contain any unicode data. To verify that you would need to read the column value for every row. Unless you have an index on the column, with a rowstore table you will need to read every data page from the table. With that in mind I think it makes a lot of sense to combine all of the column checks into a single query against the table. That way you won't be reading the table's data many times and you don't have to code a cursor or some other kind of loop.

To check a single column believe that you can just do this:

SELECT COLUMN_1
FROM [P].[Q]
WHERE CAST(COLUMN_1 AS VARCHAR(80)) &lt;&gt; CAST(COLUMN_1 AS NVARCHAR(80));


A cast from NVARCHAR to VARCHAR should give you the same result except if there are unicode characters. Unicode characters will be converted to ?. So the above code should handle NULL cases correctly. You have 24 columns to check, so you check each column in a single query by using scalar aggregates. One implementation is below: 

SELECT 
  MAX(CASE WHEN CAST(COLUMN_1 AS VARCHAR(80)) &lt;&gt; CAST(COLUMN_1 AS NVARCHAR(80)) THEN 1 ELSE 0 END) COLUMN_1_RESULT
...
, MAX(CASE WHEN CAST(COLUMN_14 AS VARCHAR(30)) &lt;&gt; CAST(COLUMN_14 AS NVARCHAR(30)) THEN 1 ELSE 0 END) COLUMN_14_RESULT
...
, MAX(CASE WHEN CAST(COLUMN_23 AS VARCHAR(8)) &lt;&gt; CAST(COLUMN_23 AS NVARCHAR(8)) THEN 1 ELSE 0 END) COLUMN_23_RESULT
FROM [P].[Q];


For each column you will get a result of 1 if any of its values contain unicode. A result of 0 means that all data can be safely converted.

I strongly recommend making a copy of the table with the new column definitions and copying your data there. You'll be doing expensive conversions if you do it in place so making a copy might not be all that much slower. Having a copy means that you can easily validate that all of the data is still there (one way is to use the EXCEPT keyword) and you can undo the operation very easily. 

Also, be aware that you might not have any unicode data currently it's possible that a future ETL could load unicode into a previously clean column. If there is not a check for this in your ETL process you should consider adding that before doing this conversion.