Reputation:
I'm having a large database CDS_CQM i want to find list of columns in the database with only null values Any helps are welcome
Upvotes: 1
Views: 105
Reputation: 364
I think this will help you find list of all columns which has no data in it
Create Procedure spGetColumnsWithAllNullValues
(
@Database Varchar (100),
@Schema Varchar (100),
@Table Varchar (100)
)
As Begin
Declare @Column Varchar (100)
Declare @Columns Table
(
ColumnName Varchar (100)
)
Declare @Results Table
(
ColumnName Varchar (100)
)
Declare @Temp Table
(
Result Bit
)
Insert @Columns
Select COLUMN_NAME
From INFORMATION_SCHEMA.COLUMNS
Where IS_NULLABLE = 'YES'
And TABLE_CATALOG = @Database
And TABLE_SCHEMA = @Schema
And TABLE_NAME = @Table
Declare cur Cursor For
Select ColumnName
From @Columns
Open cur
While (1 = 1)
Begin
Fetch Next From cur Into @Column
If (@@FETCH_STATUS <> 0) Break
Declare @sql NVarchar(Max) = N'Select Case When Exists (Select * From '
+ QuoteName(@Database) + '.'
+ QuoteName(@Schema) + '.'
+ QuoteName(@Table)
+ ' Where ' + QuoteName(@Column) + ' Is Not Null) Then 0 Else 1 End'
Delete @Temp
Insert @Temp Execute (@sql)
Insert @Results
(ColumnName)
Select @Column
From @Temp
Where Result = 1
End
Close cur
Deallocate cur
Select ColumnName
From @Results
Order By ColumnName
End
Upvotes: 1
Reputation: 2735
If you Need to find out the columns with NULL constraint
, then try like below
select *from INFORMATION_SCHEMA.columns
where IS_NULLABLE = 'yes'
Upvotes: 0