-
Notifications
You must be signed in to change notification settings - Fork 1
/
Null Function.txt
69 lines (43 loc) · 1.46 KB
/
Null Function.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
create table NullColSum (
tableName varchar(255),
SrcCnt bigint,
trgcnt bigint
)
Create procedure nullcheck @table varchar(255)
as
begin
DECLARE @Sql nvarchar(max)
DECLARE @COUNT INT
DECLARE @Nullcounttrg bigINT
DECLARE @NullcountSrc bigINT
declare @CurCol varchar(255)
set @sql='use Finlive_trg'
exec(@sql)
print @table
select COLUMN_NAME,DATA_TYPE,row_number() over (order by ([Column_NAME]) desc) as Rownum
into #allcol
from INFORMATION_SCHEMA.COLUMNS where upper(TABLE_NAME)=upper(@table)
--select * from #allcol
set @COUNT=1
set @Nullcounttrg=0
set @NullcountSrc=0
delete from NullColSum
while(@COUNT <= (select count(*) from #allcol))
begin
select @CurCol=COLUMN_NAME from #allcol where Rownum=@COUNT
drop table NullTrg
set @Sql='select count(*) as Nullcounttrg into Nulltrg from [Finlive_trg].[finware].'+@table+' where '+@CurCol+' is Null;'
exec(@sql)
select @Nullcounttrg=Nullcounttrg from Nulltrg
drop table NullSrc
set @Sql='select count(*) as NullcountSrc into NullSrc from [Finlive_src].[dbo].'+@table+'_src where '+@CurCol+' is Null;'
exec(@sql)
select @NullcountSrc=NullcountSrc from NullSrc
insert into NullColSum
values(@CurCol,@NullcountSrc,@Nullcounttrg)
set @COUNT=@COUNT+1
end
select * from NullColSum where SrcCnt!=trgcnt
end--proc end
/*After the procedure is made just run the below statement*/
execute nullcheck @table='database.Table_name'