Link to home
Create AccountLog in
Avatar of Mehram
MehramFlag for Pakistan

asked on

MS SQL Show All Empty Column from Particular Table

I am using MS SQL, at the time of development I have created numbers of column but on time to time tuning the some column have not data and all rows are empty, for example.

Table Name: Test

Name  Class   Section  Object
Xx         xx       Null      Null
Xxx      xxx      Null      Null
Xxx      xxx      Null      Null

I want to see trough sql statement after give particular table name, all those column name which are null, to decide either I need this column for future or delete for better performance of database

Please help

Mehram
Avatar of dduser
dduser

Select count(*) from Test where Section is not null

Regards,

dduser
Avatar of Guy Hengel [angelIII / a3]
you have to check each individual column:

select count(*) from yourtable where section is not null
select count(*) from yourtable where object is not null
etc ...

for each query where the count(*) returns a value of 0, all rows have NULL value in that column.
alternative:

select count(*)
,sum(case when section is null then 0 else 1 end) as section_not_empty_count
,sum(case when object is null then 0 else 1 end) as object_not_empty_count
from yourtable
Avatar of Mehram

ASKER

Sir, actually I also do not know who many columns are created by me at the time of development, and the some columns are empty and no body have enter the data from the create date to till date. However now I am tuning my application and I want to check each table one by one:

So I can provide only table name and I want to see all those column which have no data in all rows.
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Mehram

ASKER

is there any link where i can learn about cursor solution?