DO NOT RUN THIS IN PRODUCTION!!!! It is slow and will consume massive amounts of resources. Get a copy to a dev server and run this there.

I used a VERY SLOW approach here because the very nature of this request is painful because we have to look in so many places. This is a slight twist on a script that I have posted many times here on SSC. My query to find indexed columns looks far simpler than Lowell's, which make me wonder if that part of this query is not going to produce the desired results.

--Need a table to hold the results

if OBJECT_ID('IndexedColumnsWithNull') is not null

drop table IndexedColumnsWithNull

create table IndexedColumnsWithNull

(

TableName varchar(255),

ColumnName varchar(255)

)

declare @table_name varchar(2000)

declare @sSQL nvarchar(4000)

declare @result varchar(20)

declare @column_name varchar(2000)

--Need to look at all the indexed columns

declare SearchList cursor for

select t.name as TableName

, c.name as ColumnName

from sys.indexes i

inner join sys.index_columns ic on ic.object_id = i.object_id and ic.index_id = i.index_id

inner join sys.columns c on c.object_id = ic.object_id and c.column_id = ic.column_id

inner join sys.tables t on t.object_id = i.object_id

where i.is_primary_key = 0 --primary key can't be null

and c.is_nullable = 1 --don't look at the column if it is not nullable

group by t.name, c.name

order by t.name, c.name

open SearchList

fetch next from SearchList into @table_name, @column_name

while(@@fetch_status = 0)

begin

select @sSQL = 'if exists (select ''' + @table_name + ''' as TableName, [' + @column_name + '] from ' + @table_name + ' where [' + @column_name + '] IS NULL) INSERT IndexedColumnsWithNull select ''' + @table_name + ''' as TableName, [' + @column_name + '] from ' + @table_name + ' where [' + @column_name + '] IS NULL'

exec sp_executesql @sSQL

--select @ssql

fetch next from SearchList into @table_name, @column_name

end

close SearchList

deallocate SearchList

--Here is the results

select * from IndexedColumnsWithNull

Now this all looks well good but DO NOT RUN THIS IN PRODUCTION!!!! It is slow and will consume massive amounts of resources. Get a copy to a dev server and run this there.