Dynamic SQL - How can you use it to run a SELECT against all schema's and Tables?
Below is self explanatory code. Alternatively you can use sp_ineachdb (written by Aaron Bertrand).

 set nocount on
  -- create temp table .. 
 create table #commandsToRun (currentDB sysname, schemaName sysname, TableName sysname, columnName sysname, datatype sysname, commandToRun nvarchar(max))
 insert into #commandsToRun
SELECT   
        db_name() as CurrentDB, SCHEMA_NAME(tbl.schema_id) as SchemaName, tbl.name AS TableName,
         c.name AS ColumnName, tp.name as DataType, 
         N'select count('+c.name+') as TotalDateColumnAsNULL, '
                    + ''''+SCHEMA_NAME(tbl.schema_id)+'''' + ' as  SchemaName, '
                    + ''''+tbl.name + ''''+ ' AS TableName' + ''
                    +' from '+ db_name() +'.' + SCHEMA_NAME(tbl.schema_id) 
                    +'.'+ tbl.name +' with (nolock) where '
                    +c.name+' is NULL'as CommandToRun
FROM
  sys.columns c
JOIN sys.tables tbl ON
  c.object_id = tbl.object_id
JOIN sys.types tp on c.user_type_id = tp.user_type_id
WHERE
  ( c.name = 'Date' -- filter the column that you want to check 
  or tp.name = 'datetime' -- filter the datatype if you dont know the column names 
  )
-- check the contents of the table 
select * from #commandsToRun

-- generate exec() to run the sql generated in the CommandToRun

declare @sqltext nvarchar(max) = N''

select  @sqltext += N'exec ('''+replace(CommandToRun,'''','''''')+'''); '
from #commandsToRun

-- uncomment below to run the sql generated.
-- exec(@sqltext)