Need to identify only columns that have all NULL values in a row per each table in a database.

You may start with this script and update it for your needs:

declare @col sysname, @tbl sysname, @cmd varchar(max), @tblold sysname = ' '
--DECLARE @i int = 0
DECLARE getinfo cursor for
SELECT t.name, c.name FROM sys.tables t JOIN sys.columns c ON t.Object_ID = c.Object_ID
WHERE c.is_nullable = 1
-- WHERE t.Name = 'USA_SETLD_GDP_W_BOEM_WELL_SURFACE'
OPEN getinfo
FETCH NEXT FROM getinfo into @tbl, @col
WHILE @@FETCH_STATUS = 0 --AND @i < 10
BEGIN
    IF @tbl != @tblold
     BEGIN 
   -- SET @i = @i + 1
   SET @tblold = @tbl
   PRINT @tbl+':'
   END
 
 SELECT @cmd = 'IF NOT EXISTS (SELECT top 1 * FROM [' + @tbl + '] WHERE [' + @col + '] IS NOT NULL) BEGIN print ''' + @tbl + '.' + @col + ''' end'
    EXEC(@cmd)
    FETCH NEXT FROM getinfo into @tbl, @col
END
CLOSE getinfo
DEALLOCATE getinfo

Select allOpen in new window

I need some help further converting it to where it displays like:

TableName, DateFound, NumberOfTotalColumnsFound, AllColumnNamesListed

select  quotename(s.name) + '.' + quotename(t.name) as [TableName],
        getdate() as [DateWhenNullValuesFound],
        (select count(*) from sys.columns c where c.[object_id] = t.[object_id] and c.is_nullable = 1) as [NumberOfColumnsThatHaveNulls],
        cast((select c.name + '' from sys.columns c where c.[object_id] = t.[object_id] and c.is_nullable = 1 for xml path('')) as varchar(max)) as [ColumnNamesforAllNullValues],
        (select count(*) from sys.columns c where c.[object_id] = t.[object_id]) as [NumberOfTotalColumnsFound],
        cast((select c.name + '' from sys.columns c where c.[object_id] = t.[object_id] and c.is_nullable = 1 for xml path('')) as varchar(max)) as [AllColumnNamesListed]
from sys.tables t
inner join sys.schemas s on t.[schema_id] = s.[schema_id]
order by 1

Select allOpen in new window

This should do what you need:

SET NOCOUNT ON
declare @col sysname, @tbl sysname, @sch sysname, @cmd varchar(max), @tblold sysname = ' ', @list varchar(max) = '', @cnt int = 0
--DECLARE @i int = 0
CREATE TABLE #list (cols sysname)
DECLARE getinfo cursor for
SELECT s.name, t.name, c.name FROM sys.tables t 
  JOIN sys.columns c ON t.Object_ID = c.Object_ID
  JOIN sys.schemas s ON s.schema_id = t.schema_id
WHERE c.is_nullable = 1
ORDER BY t.name, c.name
-- WHERE t.Name = 'USA_SETLD_GDP_W_BOEM_WELL_SURFACE'
OPEN getinfo
FETCH NEXT FROM getinfo into @sch, @tbl, @col
WHILE @@FETCH_STATUS = 0 --AND @i < 10
BEGIN
 SELECT @cmd = 'IF NOT EXISTS (SELECT 1 FROM [' + @sch + '].[' + @tbl + '] WHERE [' + @col + '] IS NOT NULL) BEGIN INSERT INTO #list VALUES (''' + @col + ''') end'
    EXEC(@cmd)
    IF @tbl != @tblold
     BEGIN 
   -- SET @i = @i + 1
   SET @tblold = @tbl
   SELECT @cnt = COUNT(*) FROM #list
   IF @cnt > 0
     BEGIN
  SET @list = @sch + '.' + @tbl+', ' + CONVERT(varchar, GETDATE(), 101) + ' ' + CAST(@cnt AS varchar) + ' '
  SELECT @list = @list + cols + ', ' FROM #list
     PRINT @list
  DELETE FROM #list
  END
   END
 
    FETCH NEXT FROM getinfo into @sch, @tbl, @col
END
   SELECT @cnt = COUNT(*) FROM #list
   IF @cnt > 0
     BEGIN
  SET @list = @sch + '.' + @tbl+', ' + CONVERT(varchar, GETDATE(), 101) + ' ' + CAST(@cnt AS varchar) + ' '
  SELECT @list = @list + cols + ', ' FROM #list
     PRINT @list
  DELETE FROM #list
  END
CLOSE getinfo
DEALLOCATE getinfo
DROP TABLE #list

Select allOpen in new window

It also lists empty tables where we can handle all columns as NULLs because they do not have values defined...

Hi pcelba,

I need some more help with the above to make the right adjustments.
So I'm trying to adjust the above to put a comma after the date, and also the number of null columns and then at the end no longer have a comma.

How do I change the above to do what I'm asking.
And some explanation on how the concatenation works?

I've got some new code ready.  This will list every table with the NULL counts for every row.

Can you explain in more detail what you want to see as far as "AllColumnNamesListed"?  I think maybe you just want a string of column names, with counts if applicable, that match what you're looking for?!  But I'm not sure, and don't want to write code until I know it's what you need.

Please run the code below and let me know how you'd like to refine the results.  I've initially set a table counter to limit execution to 10 tables.  That should be enough for a test.  Naturally we can remove that restriction later.

I also process all columns in a single pass of the table.  It makes no sense to do a separate pass of the table for every column.

If you do plan to run this every day, it's probably worth adding a computed bit column for every column to indicate whether the corresponding column is NULL or not, then put all those bits into a single covering index to run this query against, but we can wait until later to discuss that, if you'd like to consider that approach.

/* code deemed not applicable, redacted */

Select allOpen in new window

pcelba,

I'm actually trying to get it where the data is separated by pipe delimited character
so some of the data as an example is like this:

dbo.W_BOEM_INFRA_PIPELINES_POI, 05/12/2020 1 PRP_CON_DT,
dbo.W_BOEM_LAND_BLOCKS_CLIP, 05/12/2020 2 PLAN_CD, PLAN_NAME,
dbo.W_BOEM_LAND_LEASES_ACTIVE_FLAT, 05/12/2020 2 PLAN_CD, PLAN_NAME,
dbo.ZQ_NOAA_REGU_COLREGS_DEMARCATION_BDRY, 05/12/2020 4 INFO, SRC_CHART, SRC_FILE, SRC_SCALE,

What I need it to show is a pipe delimited character separating between each set of values:
like so...  (ex:  pipe delimited is this character: |)

dbo.W_BOEM_INFRA_PIPELINES_POI | 05/12/2020| 1 | PRP_CON_DT
dbo.W_BOEM_LAND_BLOCKS_CLIP | 05/12/2020| 2 PLAN_CD | PLAN_NAME
dbo.W_BOEM_LAND_LEASES_ACTIVE_FLAT | 05/12/2020 | 2 PLAN_CD | PLAN_NAME
dbo.ZQ_NOAA_REGU_COLREGS_DEMARCATION_BDRY | 05/12/2020 | 4 INFO | SRC_CHART, SRC_FILE, SRC_SCALE

and how do I change it to where it only shows table names that have the term 'W_BOEM' in the name?
It's not like a value for a field....where I do a WHERE statement.

[CODE]
SET NOCOUNT ON
declare @col sysname, @tbl sysname, @sch sysname, @cmd varchar(max), @tblold sysname = ' ', @list varchar(max) = '', @cnt int = 0
--DECLARE @i int = 0
CREATE TABLE #list (cols sysname)
DECLARE getinfo cursor for
SELECT s.name, t.name, c.name FROM sys.tables t
  JOIN sys.columns c ON t.Object_ID = c.Object_ID
  JOIN sys.schemas s ON s.schema_id = t.schema_id
WHERE c.is_nullable = 1 AND t.Name LIKE '%USA_SETLD%'
ORDER BY t.name, c.name
OPEN getinfo
FETCH NEXT FROM getinfo into @sch, @tbl, @col
WHILE @@FETCH_STATUS = 0 --AND @i < 10
BEGIN
 SELECT @cmd = 'IF NOT EXISTS (SELECT 1 FROM [' + @sch + '].[' + @tbl + '] WHERE [' + @col + '] IS NOT NULL) BEGIN INSERT INTO #list VALUES (''' + @col + ''') end'
    EXEC(@cmd)
    IF @tbl != @tblold
     BEGIN
   -- SET @i = @i + 1
   SET @tblold = @tbl
   SELECT @cnt = COUNT(*) FROM #list
   IF @cnt > 0
     BEGIN
  SET @list = @sch + '.' + @tbl+', ' + CONVERT(varchar, GETDATE(), 101) + ' ' + CAST(@cnt AS varchar) + ' '
  SELECT @list = @list + cols + ', ' FROM #list
     PRINT @list
  DELETE FROM #list
  END
   END
 
    FETCH NEXT FROM getinfo into @sch, @tbl, @col
END
   SELECT @cnt = COUNT(*) FROM #list
   IF @cnt > 0
     BEGIN
  SET @list = @sch + '.' + @tbl+', ' + CONVERT(varchar, GETDATE(), 101) + ' ' + CAST(@cnt AS varchar) + ' '
  SELECT @list = @list + cols + ', ' FROM #list
     PRINT @list
  DELETE FROM #list
  END
CLOSE getinfo
DEALLOCATE getinfo
DROP TABLE #list


[/CODE]


This works - now I have to figure out how to put the Pipe Delimited characters in instead of comma.

alright so I go this further:

[CODE]SET NOCOUNT ON
declare @col sysname, @tbl sysname, @sch sysname, @cmd varchar(max), @tblold sysname = ' ', @list varchar(max) = '', @cnt int = 0
--DECLARE @i int = 0
CREATE TABLE #list (cols sysname)
DECLARE getinfo cursor for
SELECT s.name, t.name, c.name FROM sys.tables t
  JOIN sys.columns c ON t.Object_ID = c.Object_ID
  JOIN sys.schemas s ON s.schema_id = t.schema_id
WHERE c.is_nullable = 1 AND t.Name LIKE '%USA_SETLD%'
ORDER BY t.name, c.name
OPEN getinfo
FETCH NEXT FROM getinfo into @sch, @tbl, @col
WHILE @@FETCH_STATUS = 0 --AND @i < 10
BEGIN
 SELECT @cmd = 'IF NOT EXISTS (SELECT 1 FROM [' + @sch + '].[' + @tbl + '] WHERE [' + @col + '] IS NOT NULL) BEGIN INSERT INTO #list VALUES (''' + @col + ''') end'
    EXEC(@cmd)
    IF @tbl != @tblold
     BEGIN
   -- SET @i = @i + 1
   SET @tblold = @tbl
   SELECT @cnt = COUNT(*) FROM #list
   IF @cnt > 0
     BEGIN
  SET @list = @sch + '.' + @tbl+' | ' + CONVERT(varchar, GETDATE(), 101) + ' | ' + CAST(@cnt AS varchar) + ' '
  SELECT @list = @list + cols + ' | ' FROM #list
     PRINT @list
  DELETE FROM #list
  END
   END
 
    FETCH NEXT FROM getinfo into @sch, @tbl, @col
END
   SELECT @cnt = COUNT(*) FROM #list
   IF @cnt > 0
     BEGIN
  SET @list = @sch + '.' + @tbl+' | ' + CONVERT(varchar, GETDATE(), 101) + ' ' + CAST(@cnt AS varchar) + ' '
  SELECT @list = @list + cols + ' | ' FROM #list
     PRINT @list
  DELETE FROM #list
  END
CLOSE getinfo
DEALLOCATE getinfo
DROP TABLE #list

[/CODE]

Now I just need to drop off the last character "|" at the end.

If you just list every column, how does that tell you anything about NULL values?  Or do you just want to list column names that had ONLY NULL values?

Would, instead, a NULL "marker" be useful, such as a leading * to mean "this column has only NULL values"?  Or would a  count of NULL values for each column be more useful?

"do you want to list column names that had ONLY NULL values?"

Yes.

That's why the above with pcelba's comment works.

pcelba,

I've got my adjustments working as so:
The only downfall I have is it lists a comma at the end of the line and I don't want that.

[CODE]
SET NOCOUNT ON
declare @col sysname, @tbl sysname, @sch sysname, @cmd varchar(max), @tblold sysname = ' ', @list varchar(max) = '', @cnt int = 0
--DECLARE @i int = 0
CREATE TABLE #list (cols sysname)
DECLARE getinfo cursor for
SELECT s.name, t.name, c.name FROM sys.tables t
  JOIN sys.columns c ON t.Object_ID = c.Object_ID
  JOIN sys.schemas s ON s.schema_id = t.schema_id
WHERE c.is_nullable = 1 AND t.Name LIKE '%USA_SETLD%'
ORDER BY t.name, c.name
OPEN getinfo
FETCH NEXT FROM getinfo into @sch, @tbl, @col
WHILE @@FETCH_STATUS = 0 --AND @i < 10
BEGIN
 SELECT @cmd = 'IF NOT EXISTS (SELECT 1 FROM [' + @sch + '].[' + @tbl + '] WHERE [' + @col + '] IS NOT NULL) BEGIN INSERT INTO #list VALUES (''' + @col + ''') end'
    EXEC(@cmd)
    IF @tbl != @tblold
     BEGIN
   -- SET @i = @i + 1
   SET @tblold = @tbl
   SELECT @cnt = COUNT(*) FROM #list
   IF @cnt > 0
     BEGIN
  SET @list = @sch + '.' + @tbl+'|' + CONVERT(varchar, GETDATE(), 101) + '|' + CAST(@cnt AS varchar) + '|'
  SELECT @list = @list + cols + ', ' FROM #list
     PRINT @list
  DELETE FROM #list
  END
   END
 
    FETCH NEXT FROM getinfo into @sch, @tbl, @col
END
   SELECT @cnt = COUNT(*) FROM #list
   IF @cnt > 0
     BEGIN
  SET @list = @sch + '.' + @tbl+', ' + CONVERT(varchar, GETDATE(), 101) + ', ' + CAST(@cnt AS varchar) + ' '
  SELECT @list = @list + cols + ' ' FROM #list
     PRINT @list
  DELETE FROM #list
  END
CLOSE getinfo
DEALLOCATE getinfo
DROP TABLE #list
[/CODE]

There is more problems in my code… This one should work better:

SET NOCOUNT ON 
declare @col sysname, @tbl sysname, @sch sysname, @cmd varchar(max), @tblold sysname = ' ', @list varchar(max) = '', @cnt int = 0 
CREATE TABLE #list (cols sysname) 
DECLARE getinfo cursor for 
SELECT s.name, t.name, c.name FROM sys.tables t 
  JOIN sys.columns c ON t.Object_ID = c.Object_ID 
  JOIN sys.schemas s ON s.schema_id = t.schema_id 
WHERE c.is_nullable = 1 -- AND t.Name LIKE '%USA_SETLD%' 
ORDER BY t.name, s.name, c.name 
OPEN getinfo 
FETCH NEXT FROM getinfo into @sch, @tbl, @col 
SET @tblold = @sch + '.' + @tbl
WHILE @@FETCH_STATUS = 0 --AND @i < 10 
  BEGIN 
  -- SELECT @cmd = 'IF EXISTS (SELECT 1 FROM [' + @sch + '].[' + @tbl + ']) AND NOT EXISTS (SELECT 1 FROM [' + @sch + '].[' + @tbl + '] WHERE [' + @col + '] IS NOT NULL) BEGIN INSERT INTO #list VALUES (''' + @col + ''') end' 
  SELECT @cmd = 'IF NOT EXISTS (SELECT 1 FROM [' + @sch + '].[' + @tbl + '] WHERE [' + @col + '] IS NOT NULL) BEGIN INSERT INTO #list VALUES (''' + @col + ''') end' 
  EXEC(@cmd) 
  IF @sch + '.' + @tbl != @tblold 
    BEGIN 
    SELECT @cnt = COUNT(*) FROM #list 
    IF @cnt > 0 
      BEGIN 
      SET @list = @tblold+'|' + CONVERT(varchar, GETDATE(), 101) + '|' + CAST(@cnt AS varchar) + '|' 
      SELECT @list = @list + cols + ', ' FROM #list 
      SET @list = LEFT(@list, LEN(@list)-1)
      PRINT @list 
      DELETE FROM #list 
      END 
    SET @tblold = @sch + '.' + @tbl
    END 
  
  FETCH NEXT FROM getinfo into @sch, @tbl, @col 
  END 
SELECT @cnt = COUNT(*) FROM #list 
IF @cnt > 0 
  BEGIN 
  SET @list = @tblold+'|' + CONVERT(varchar, GETDATE(), 101) + '|' + CAST(@cnt AS varchar) + '|' 
  SELECT @list = @list + cols + ' ' FROM #list 
  SET @list = LEFT(@list, LEN(@list)-1)
  PRINT @list 
  DELETE FROM #list 
  END 
CLOSE getinfo 
DEALLOCATE getinfo 
DROP TABLE #list 

Select allOpen in new window

The output list contains empty tables which is possibly not correct. If you uncomment the only commented line and comment the next one it should not list them.

Anyway to do this as a list like this:

[/CODE]
select  quotename(s.name) + '.' + quotename(t.name) as [TableName],
        getdate() as [DateWhenNullValuesFound],
        (select count(*) from sys.columns c where c.[object_id] = t.[object_id] and c.is_nullable = 1) as [NumberOfColumnsThatHaveNulls],
        cast((select c.name + '' from sys.columns c where c.[object_id] = t.[object_id] and c.is_nullable = 1 for xml path('')) as varchar(max)) as [ColumnNamesforAllNullValues],
        (select count(*) from sys.columns c where c.[object_id] = t.[object_id]) as [NumberOfTotalColumnsFound],
        cast((select c.name + '' from sys.columns c where c.[object_id] = t.[object_id] and c.is_nullable = 1 for xml path('')) as varchar(max)) as [AllColumnNamesListed]
from sys.tables t
inner join sys.schemas s on t.[schema_id] = s.[schema_id]
where (t.Name Like '%USA_SETLD%')
order by 1
[/CODE]
Is it possible to change the above to only list columns with all rows as NULL?
I need this to work like a SELECT statement above instead of dumping into a list.

I need it to list physical columns and rows the information.

pcelba,
Is there a way to write the above to an array instead of printing out to a list?

Do you mean something like this?

SET NOCOUNT ON 
declare @col sysname, @tbl sysname, @sch sysname, @cmd varchar(max), @tblold sysname = ' ', @list varchar(max) = '', @cnt int = 0 
CREATE TABLE #list (tbl sysname, cols sysname) 
CREATE TABLE #outList (tbl sysname, dt date, cnt int, cols varchar(max))
DECLARE getinfo cursor for 
SELECT s.name, t.name, c.name FROM sys.tables t 
  JOIN sys.columns c ON t.Object_ID = c.Object_ID 
  JOIN sys.schemas s ON s.schema_id = t.schema_id 
WHERE c.is_nullable = 1 -- AND t.Name LIKE '%USA_SETLD%' 
ORDER BY s.name, t.name, c.name 
OPEN getinfo 
WHILE 1=1
  BEGIN 
  
  FETCH NEXT FROM getinfo into @sch, @tbl, @col 
  IF @@FETCH_STATUS != 0
    BREAK
  
  IF @tblold = ' '
    SET @tblold = @sch + '.' + @tbl
  IF @sch + '.' + @tbl != @tblold 
    BEGIN 
    SELECT @cnt = COUNT(*) FROM #list WHERE tbl = @tblold
    IF @cnt > 0 
      BEGIN 
      SET @list = '' -- @tblold+'|' + CONVERT(varchar, GETDATE(), 101) + '|' + CAST(@cnt AS varchar) + '|' 
      SELECT @list = @list + cols + ', ' FROM #list WHERE tbl = @tblold 
      SET @list = LEFT(@list, LEN(@list)-1)
      --PRINT @list 
   INSERT INTO #outList VALUES (@tblold, GETDATE(), @cnt, @list)
      DELETE FROM #list WHERE tbl = @tblold 
      END 
    SET @tblold = @sch + '.' + @tbl
    END 
  
  --SELECT @cmd = 'IF EXISTS (SELECT 1 FROM [' + @sch + '].[' + @tbl + ']) AND NOT EXISTS (SELECT 1 FROM [' + @sch + '].[' + @tbl + '] WHERE [' + @col + '] IS NOT NULL) BEGIN INSERT INTO #list VALUES (''' + @tblold + ''',''' + @col + ''') end' 
  SELECT @cmd = 'IF NOT EXISTS (SELECT 1 FROM [' + @sch + '].[' + @tbl + '] WHERE [' + @col + '] IS NOT NULL) BEGIN INSERT INTO #list VALUES (''' + @tblold + ''',''' + @col + ''') end' 
  EXEC(@cmd) 
  
  END 
SELECT @cnt = COUNT(*) FROM #list 
IF @cnt > 0 
  BEGIN 
  SET @list = '' -- @tblold+'|' + CONVERT(varchar, GETDATE(), 101) + '|' + CAST(@cnt AS varchar) + '|' 
  SELECT @list = @list + cols + ', ' FROM #list 
  SET @list = LEFT(@list, LEN(@list)-1)
  --PRINT @list 
  INSERT INTO #outList VALUES (@tblold, GETDATE(), @cnt, @list)
  DELETE FROM #list 
  END 
CLOSE getinfo 
DEALLOCATE getinfo 
SELECT * FROM #outList
DROP TABLE #list 
DROP TABLE #outList

Select allOpen in new window

This should return correct output finally (incl. empty tables).

Thank you extremely much.

So to be clear this includes not just null but empty, missing or just spaces in as the values.

No, this includes just columns containing NULL values in all rows. It also includes all columns from empty tables (this is possible to switch off by commenting line 37 and uncommenting line 36).

Empty values are not counted here.

Yes please I need it include missing and spacing along with NULLs.

If you would like to include empty values then it would be more complex because you must do it differently for various data types. E.g. nvarchar column can contain about 10 different spaces or it can be empty with data length = 0 which means no spaces or empty string surely different from NULL value. What about numeric columns? Does zero mean empty? Etc.

string only.  Zero does not mean null.

I'm trying some of these suggestions but the replace statement doesn't work.
https://stackoverflow.com/questions/24146631/sql-server-how-do-i-find-rows-with-whitespace-in-a-column 

I'm trying this...

[CODE]
SET NOCOUNT ON
declare @col sysname, @tbl sysname, @sch sysname, @cmd varchar(max), @tblold sysname = ' ', @list varchar(max) = '', @cnt int = 0
CREATE TABLE #list (tbl sysname, cols sysname)
CREATE TABLE #outList (tbl sysname, dt date, cnt int, cols varchar(max))
DECLARE getinfo cursor for
SELECT s.name, t.name, c.name FROM sys.tables t
  JOIN sys.columns c ON t.Object_ID = c.Object_ID
  JOIN sys.schemas s ON s.schema_id = t.schema_id
WHERE c.is_nullable = 1 AND t.Name LIKE '%USA_SETLD%'
ORDER BY s.name, t.name, c.name
OPEN getinfo
WHILE 1=1
  BEGIN
 
  FETCH NEXT FROM getinfo into @sch, @tbl, @col
  IF @@FETCH_STATUS != 0
    BREAK
 
  IF @tblold = ' '
    SET @tblold = @sch + '.' + @tbl
  IF @sch + '.' + @tbl != @tblold
    BEGIN
    SELECT @cnt = COUNT(*) FROM #list WHERE tbl = @tblold
    IF @cnt > 0
      BEGIN
      SET @list = '' -- @tblold+'|' + CONVERT(varchar, GETDATE(), 101) + '|' + CAST(@cnt AS varchar) + '|'
      SELECT @list = @list + cols + ', ' FROM #list WHERE tbl = @tblold
      SET @list = LEFT(@list, LEN(@list)-1)
      --PRINT @list
   INSERT INTO #outList VALUES (@tblold, GETDATE(), @cnt, @list)
      DELETE FROM #list WHERE tbl = @tblold
      END
    SET @tblold = @sch + '.' + @tbl
    END
 
  --SELECT @cmd = 'IF EXISTS (SELECT 1 FROM [' + @sch + '].[' + @tbl + ']) AND NOT EXISTS (SELECT 1 FROM [' + @sch + '].[' + @tbl + '] WHERE [' + @col + '] IS NOT NULL) BEGIN INSERT INTO #list VALUES (''' + @tblold + ''',''' + @col + ''') end'
  SELECT @cmd = 'IF NOT EXISTS (SELECT 1 FROM [' + @sch + '].[' + @tbl + '] WHERE [' + @col + '] IS NOT NULL AND ltrim(rtrim([' + @col + '])) <> '' ) BEGIN INSERT INTO #list VALUES (''' + @tblold + ''',''' + @col + ''') end'
  EXEC(@cmd)
 
  END
SELECT @cnt = COUNT(*) FROM #list
IF @cnt > 0
  BEGIN
  SET @list = '' -- @tblold+'|' + CONVERT(varchar, GETDATE(), 101) + '|' + CAST(@cnt AS varchar) + '|'
  SELECT @list = @list + cols + ', ' FROM #list
  SET @list = LEFT(@list, LEN(@list)-1)
  --PRINT @list
  INSERT INTO #outList VALUES (@tblold, GETDATE(), @cnt, @list)
  DELETE FROM #list
  END
CLOSE getinfo
DEALLOCATE getinfo
SELECT * FROM #outList
DROP TABLE #list
DROP TABLE #outList
[/CODE]

but I get the following:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'dbo'.
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string ') end'.

cause of:
[CODE]
SELECT @cmd = 'IF NOT EXISTS (SELECT 1 FROM [' + @sch + '].[' + @tbl + '] WHERE [' + @col + '] IS NOT NULL AND ltrim(rtrim([' + @col + '])) <> '' ) BEGIN INSERT INTO #list VALUES (''' + @tblold + ''',''' + @col + ''') end'
 
[/CODE]

It doesn't like my attempt at ltrim(rtrim(

Some apostrophes were missing, so:
 AND ltrim(rtrim([' + @col + '])) <> ''''

BUT you have to also exclude columns of incompatible data type...

I did try that above and no longer got an error but I did not get any results either.
please note attachment:

It works for me. Please review your cursor declaration. It is not visible on the image.

OK.  So now I have a different error shown here:

SET NOCOUNT ON 
declare @col sysname, @tbl sysname, @sch sysname, @cmd varchar(max), @tblold sysname = ' ', @list varchar(max) = '', @cnt int = 0 
CREATE TABLE #list (tbl sysname, cols sysname) 
CREATE TABLE #outList (tbl sysname, dt date, cnt int, cols varchar(max))
DECLARE getinfo cursor for 
SELECT s.name, t.name, c.name FROM sys.tables t 
  JOIN sys.columns c ON t.Object_ID = c.Object_ID 
  JOIN sys.schemas s ON s.schema_id = t.schema_id 
WHERE c.is_nullable = 1 -- AND t.Name LIKE '%USA_SETLD%' 
ORDER BY s.name, t.name, c.name 
OPEN getinfo 
WHILE 1=1
  BEGIN 
  
  FETCH NEXT FROM getinfo into @sch, @tbl, @col 
  IF @@FETCH_STATUS != 0
    BREAK
  
  IF @tblold = ' '
    SET @tblold = @sch + '.' + @tbl
  IF @sch + '.' + @tbl != @tblold 
    BEGIN 
    SELECT @cnt = COUNT(*) FROM #list WHERE tbl = @tblold
    IF @cnt > 0 
      BEGIN 
      SET @list = '' -- @tblold+'|' + CONVERT(varchar, GETDATE(), 101) + '|' + CAST(@cnt AS varchar) + '|' 
      SELECT @list = @list + cols + ', ' FROM #list WHERE tbl = @tblold 
      SET @list = LEFT(@list, LEN(@list)-1)
      --PRINT @list 
   INSERT INTO #outList VALUES (@tblold, GETDATE(), @cnt, @list)
      DELETE FROM #list WHERE tbl = @tblold 
      END 
    SET @tblold = @sch + '.' + @tbl
    END 
  
  --SELECT @cmd = 'IF EXISTS (SELECT 1 FROM [' + @sch + '].[' + @tbl + ']) AND NOT EXISTS (SELECT 1 FROM [' + @sch + '].[' + @tbl + '] WHERE [' + @col + '] IS NOT NULL) BEGIN INSERT INTO #list VALUES (''' + @tblold + ''',''' + @col + ''') end' 
  SELECT @cmd = 'IF NOT EXISTS (SELECT 1 FROM [' + @sch + '].[' + @tbl + '] WHERE [' + @col + '] IS NOT NULL AND ltrim(rtrim([' + @col + '])) <> '''') BEGIN INSERT INTO #list VALUES (''' + @tblold + ''',''' + @col + ''') end' 
  EXEC(@cmd) 
  
  END 
SELECT @cnt = COUNT(*) FROM #list 
IF @cnt > 0 
  BEGIN 
  SET @list = '' -- @tblold+'|' + CONVERT(varchar, GETDATE(), 101) + '|' + CAST(@cnt AS varchar) + '|' 
  SELECT @list = @list + cols + ', ' FROM #list 
  SET @list = LEFT(@list, LEN(@list)-1)
  --PRINT @list 
  INSERT INTO #outList VALUES (@tblold, GETDATE(), @cnt, @list)
  DELETE FROM #list 
  END 
CLOSE getinfo 
DEALLOCATE getinfo 
SELECT * FROM #outList
DROP TABLE #list 
DROP TABLE #outList

Select allOpen in new window

This is the full error message:

Msg 8116, Level 16, State 1, Line 1
Argument data type xml is invalid for argument 1 of rtrim function.
Msg 8116, Level 16, State 1, Line 1
Argument data type xml is invalid for argument 1 of rtrim function.
Msg 8116, Level 16, State 1, Line 1
Argument data type xml is invalid for argument 1 of rtrim function.
Msg 8116, Level 16, State 1, Line 1
Argument data type xml is invalid for argument 1 of rtrim function.
Msg 8116, Level 16, State 1, Line 1
Argument data type geometry is invalid for argument 1 of rtrim function.
Msg 8116, Level 16, State 1, Line 1
Argument data type geometry is invalid for argument 1 of rtrim function.
Msg 8116, Level 16, State 1, Line 1
Argument data type geometry is invalid for argument 1 of rtrim function.
Msg 8116, Level 16, State 1, Line 1
Argument data type geometry is invalid for argument 1 of rtrim function.
Msg 8116, Level 16, State 1, Line 1
Argument data type geometry is invalid for argument 1 of rtrim function.
Msg 8116, Level 16, State 1, Line 1
Argument data type geometry is invalid for argument 1 of rtrim function.

Select allOpen in new window

pcelba , So how do I change the syntax to exclude columns of incompatible data type as you say above?

Hey pcelba,
I'm trying to change it where I have an additional column of total no of columns:
by adding it as @tcol int = 0

but when I run it says:

Msg 8120, Level 16, State 1, Line 6
Column 'sys.schemas.name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

How do I fix below to have it run?

[CODE]
SET NOCOUNT ON
declare @col sysname, @tbl sysname, @sch sysname, @dtyp int, @cmd varchar(max), @tblold sysname = ' ', @list varchar(max) = '', @cnt int = 0, @tcol int = 0
CREATE TABLE #list (tbl sysname, cols sysname)
CREATE TABLE #outList (tbl sysname, dt date, cnt int, totcols int, cols varchar(max))
DECLARE getinfo cursor for
SELECT s.name, t.name, c.name, c.system_type_id, count(c.name) FROM sys.tables t
  JOIN sys.columns c ON t.Object_ID = c.Object_ID
  JOIN sys.schemas s ON s.schema_id = t.schema_id
WHERE c.is_nullable = 1 -- AND t.Name LIKE '%USA_SETLD%'
ORDER BY s.name, t.name, c.name
OPEN getinfo
WHILE 1=1
  BEGIN
 
  FETCH NEXT FROM getinfo into @sch, @tbl, @col, @dtyp, @tcol
  IF @@FETCH_STATUS != 0
    BREAK
 
  IF @tblold = ' '
    SET @tblold = @sch + '.' + @tbl
  IF @sch + '.' + @tbl != @tblold
    BEGIN
    SELECT @cnt = COUNT(*) FROM #list WHERE tbl = @tblold
    IF @cnt > 0
      BEGIN
      SET @list = '' -- @tblold+'|' + CONVERT(varchar, GETDATE(), 101) + '|' + CAST(@cnt AS varchar) + '|'
      SELECT @list = @list + cols + ', ' FROM #list WHERE tbl = @tblold
      SET @list = LEFT(@list, LEN(@list)-1)
      --PRINT @list
   INSERT INTO #outList VALUES (@tblold, GETDATE(), @cnt, @list)
      DELETE FROM #list WHERE tbl = @tblold
      END
    SET @tblold = @sch + '.' + @tbl
    END
 
  IF @dtyp IN (167, 175, 231, 239)
    SELECT @cmd = 'IF EXISTS (SELECT 1 FROM [' + @sch + '].[' + @tbl + ']) AND NOT EXISTS (SELECT 1 FROM [' + @sch + '].[' + @tbl + '] WHERE [' + @col + '] IS NOT NULL AND len([' + @col + ']) <> 0 ) BEGIN INSERT INTO #list VALUES (''' + @tblold + ''',''' + @col + ''') end'
  ELSE
    SELECT @cmd = 'IF EXISTS (SELECT 1 FROM [' + @sch + '].[' + @tbl + ']) AND NOT EXISTS (SELECT 1 FROM [' + @sch + '].[' + @tbl + '] WHERE [' + @col + '] IS NOT NULL) BEGIN INSERT INTO #list VALUES (''' + @tblold + ''',''' + @col + ''') end'
   
  -- SELECT @cmd = 'IF NOT EXISTS (SELECT 1 FROM [' + @sch + '].[' + @tbl + '] WHERE [' + @col + '] IS NOT NULL AND ltrim(rtrim([' + @col + '])) <> '''' ) BEGIN INSERT INTO #list VALUES (''' + @tblold + ''',''' + @col + ''') end'
  EXEC(@cmd)
 
  END
SELECT @cnt = COUNT(*) FROM #list
IF @cnt > 0
  BEGIN
  SET @list = '' -- @tblold+'|' + CONVERT(varchar, GETDATE(), 101) + '|' + CAST(@cnt AS varchar) + '|'
  SELECT @list = @list + cols + ', ' FROM #list
  SET @list = LEFT(@list, LEN(@list)-1)
  --PRINT @list
  INSERT INTO #outList VALUES (@tblold, GETDATE(), @cnt, @list)
  DELETE FROM #list
  END
CLOSE getinfo
DEALLOCATE getinfo
SELECT * FROM #outList
DROP TABLE #list
DROP TABLE #outList
[/CODE]

It cannot work this way. You should use separate query for total number of columns.
And study how to use GROUP BY: https://www.w3schools.com/sql/sql_groupby.asp 

The best you can do is to test the query used for cursor declaration in SSMS.