December 16, 2004 at 6:49 am
Hi,
Please write an SQL Statement which will analyse a Table which has many fields and list those fields who have more than 60% NULL value entered in them
SAMAR
December 16, 2004 at 7:08 am
Can you please post what you have done so we can assist you (instead of us providing all the work and no credit)????
Good Hunting!
AJ Ahrens
webmaster@kritter.net
December 16, 2004 at 8:05 am
Use master
GO
CREATE TABLE [NullsPourcentage] (
[PkNullPourcentage] [int] IDENTITY (1, 1) NOT NULL ,
[DBName] [varchar] (128) COLLATE French_CI_AS NOT NULL ,
[TableName] [varchar] (258) COLLATE French_CI_AS NOT NULL ,
[ColName] [varchar] (256) COLLATE French_CI_AS NOT NULL ,
[Colid] [smallint] NOT NULL ,
[TotalLines] [int] NOT NULL ,
[NullLines] [int] NOT NULL ,
[NonNullLines] [int] NOT NULL ,
[NullsPourcentage] [decimal](18, 3) NOT NULL ,
CONSTRAINT [PK_NullsPourcentage] PRIMARY KEY CLUSTERED
(
[PkNullPourcentage]
) WITH FILLFACTOR = 50 ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE PROCEDURE [dbo].[sp_GetNullsPourcentage] @sTableName varchar(256) = 'ScanAllUserTables', @NullsPourcentageToReturn as tinyint = 0, @OrderByTableName as bit = 0
AS
SET NOCOUNT ON
Declare @Count as int
Declare @NullLines as int
Declare @Results table (TableName varchar(258) not null, ColName varchar(256) not null, Colid smallint not null, TotalLines int not null, NullLines int not null, NonNullLines int not null, NullsPourcentage decimal (18,3) not null)
Declare @ExecSQL as nvarchar(1000)
Declare @FullTableName as varchar(300)
Declare @ColName varchar(258),
@Colid smallint
Declare @Tables table (FullTableName varchar(388) primary key not null, TableName varchar(128))
if not exists (Select * from dbo.sysobjects where name = @sTableName and xtype = 'U') and @sTableName 'ScanAllUserTables'
begin
Declare @Error as varchar(500)
SET @Error = 'This table doesn''t exists in this database (''' + @sTableName + ''')'
raiserror (@Error,13 , 1)
return
end
else if @sTableName = 'ScanAllUserTables'
BEGIN
Insert into @Tables (FullTableName, TableName) (Select '[' + user_name(O.UID) + '].[' + O.Name + ']' as FullTableName, O.Name as TableName from dbo.sysobjects O where O.Status >=0 and XType = 'U')
END
else
BEGIN
Insert into @Tables (FullTableName, TableName) (Select '[' + user + '].[' + @sTableName + ']' as FullTableName, @sTableName as TableName)
END
SET @FullTableName = ''
SET @sTableName = ''
Select top 1 @FullTableName = FullTableName, @sTableName = TableName from @Tables where FullTableName > @FullTableName order by FullTableName
While @sTableName ''
begin
--Set @Count = dbo.CountALL(@sTableName)
SET @ExecSQL = 'Select @Count = Count(*) from [' + User + '].[' + @sTableName + ']'
EXEC sp_executesql @ExecSQL, N'@Count int output', @Count output
Declare Columns_curs CURSOR FAST_FORWARD LOCAL
FOR Select Name as ColName, Colid from dbo.syscolumns where id = object_id(@sTableName) and IsNullable = 1 order by Colid
open Columns_curs
fetch next from Columns_curs into @ColName, @Colid
while @@fetch_status = 0
begin
SET @ExecSQL = 'Select @NullLines = Count(*) from [' + User + '].[' + @sTableName + '] where [' + @ColName + '] is null'
EXEC sp_executesql @ExecSQL, N'@NullLines int output', @NullLines output
Insert into @Results (TableName, ColName, Colid, TotalLines, NullLines, NonNullLines, NullsPourcentage) values (@sTableName, @ColName, @Colid, @Count, @NullLines, @Count - @NullLines, case @Count when 0 then 0 else @NullLines * 100.0 / @Count end)
fetch next from Columns_curs into @ColName, @Colid
end
close Columns_curs
deallocate Columns_curs
SET @sTableName = ''
Select top 1 @FullTableName = FullTableName, @sTableName = TableName from @Tables where FullTableName > @FullTableName order by FullTableName
end
if Exists (Select * from @Results where NullsPourcentage > 100)
begin
print 'warning your statistic are out of date for some tables'
Select Distinct 'dbcc dbreindex (['+ TableName + '])' as Reindex from @Results where NullsPourcentage > 100
end
Delete from dbo.NullsPourcentage where DBName = Db_Name()
Insert into NullsPourcentage (DBName, TableName, ColName, Colid, TotalLines, NullLines, NonNullLines, NullsPourcentage)
(Select DB_Name() as DBName, TableName, ColName, Colid, TotalLines, NullLines, NonNullLines, NullsPourcentage from @Results)
if @OrderByTableName = 1
Select DB_Name() as DBName, TableName, ColName, Colid, TotalLines, NullLines, NonNullLines, NullsPourcentage from @Results where NullsPourcentage >= @NullsPourcentageToReturn order by TableName, NullsPourcentage desc
else if @OrderByTableName = 0
Select DB_Name() as DBName, TableName, ColName, Colid, TotalLines, NullLines, NonNullLines, NullsPourcentage from @Results where NullsPourcentage >= @NullsPourcentageToReturn order by NullsPourcentage desc
SET NOCOUNT OFF
GO
Use Northwind
go
exec sp_GetNullsPourcentage 'Customers', 60 --for 60% nulls
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply