Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Pace
Constraint Violating Yak Guru
264 Posts |
 Posted - 2009-02-24 : 07:23:27
|
Hi Experts, Is it possible to scan all the tables in database and return the table and column name IF, the entire column in that table is null?Thanks,Pace"Impossible is Nothing"  |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-02-24 : 08:13:09
|
| declare @sql varchar(max)select @sql=isnull(@sql,'') + replace(replace('if not exists(select * from [ ! ] where [ ? ] is not null) print '' ! . ? ''; ',' ! ', table_name), ' ? ', column_name)from information_schema.columns c where is_nullable='Yes'and exists(select * from information_Schema.tables where table_name=c.table_name and table_type='BASE TABLE')exec(@sql)Warning: not tested. This will find all nullable columns and generate SQL to test for non-null values, if it doesn't find any it will print the table and column name.edit: I just realized that this will also pick up empty tables. If you don't want empty tables in the mix let me know and I'll modify it (unless you want to figure it out yourself) edit again: If you have objects not under the dbo schema then you'll need this version:declare @sql varchar(max)select @sql=isnull(@sql,'') + replace(replace(replace('if not exists(select * from [ % ].[ ! ] where [ ? ] is not null) print '' % . ! . ? ''; ',' ! ', table_name), ' ? ', column_name),' % ', table_schema)from information_schema.columns c where is_nullable='Yes'and exists(select * from information_Schema.tables where table_name=c.table_name and table_schema=c.table_schema and table_type='BASE TABLE')exec(@sql) |
 |
|
|
|
|
|
|
|
Query DB for entire null column