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'