How can we check value of a columns of a table are populated or not by using a stored procedure?

Iam asking this because iam very much new to stored procedure. Can any help me in writing a stored procedure for checking whether value of a columns of a table are populated or not by using a stored procedure?

for example:- I have a table named student. it contains 5 columns.in this almost 3 columns have values and other two开发者_C百科 columns do not have values in it. So by stored procedure how can we do this?

---

You can use this to get the column names that does not have any values.

declare @T table (Col1 int, Col2 int, Col3 int, Col4 int, Col5 int)

insert into @T values (1,    1,    1,    null, null)
insert into @T values (2,    2,    null, null, null)
insert into @T values (3,    null, null, null, null)
insert into @T values (null, null, null, null, null)

select [Column]
from 
  (select
     count(Col1) as Col1,
     count(Col2) as Col2,
     count(Col3) as Col3,
     count(Col4) as Col4,
     count(Col5) as Col5
   from @T) as T
unpivot
   ([Count] for [Column] in 
      (Col1, Col2, Col3, Col4, Col5)
) as unpvt
where [Count] = 0

Result

Column
------
Col4
Col5

---

You can use @@ROWCOUNT (Transact-SQL) to get the number of rows affected by the last statement.

---

If this is a one time check, you could use the output of following script to find tables with columns with only NULL values.

Script

SELECT  'IF NOT EXISTS (SELECT * FROM ' 
        + so.name
        + ' WHERE '
        + sc.name
        + ' IS NOT NULL) PRINT '''
        + so.name
        + '.'
        + sc.name
        + ''''
FROM    sys.columns sc
        INNER JOIN sys.objects so ON so.object_id = sc.object_id        
WHERE   so.type = 'U'

Results

 IF NOT EXISTS (SELECT * FROM Table WHERE Column IS NOT NULL) PRINT 'Table.Column'