Q9kCcxYb

1DECLARE @messageBody NVARCHAR(MAX)
2DECLARE @iterator int
3DECLARE @Rowcount int
4DECLARE @varTableA TABLE(tablecounter int IDENTITY(1,1), DueDate date, Account NVARCHAR(20), Retailer NVARCHAR(20), Interest float)
5
6
7INSERT INTO @varTableA (DueDate, Account, Retailer, Interest)
8SELECT DueDate, Account, Retailer, Interest
9FROM TableA 
10WHERE DueDate IS NULL OR Account IS NULL OR Retailer IS NULL OR Interest IS NULL
11
12SET @Rowcount = @@ROWCOUNT
13SET @iterator = 1
14
15
16WHILE(@iterator <= @Rowcount)
17BEGIN 
18
19SET @messageBody = (SELECT 'No values in following columns' + 'Duedate: ' + ISNULL(Duedate, 'No value') + CHAR(10) + CHAR(13) 
20+ 'Account: ' + ISNULL(Account, 'No value') + CHAR(10) + CHAR(13)
21+ 'Retailer: ' + ISNULL(Retailer, 'No Value') + CHAR(10) + CHAR(13)
22+ 'Interest: ' + ISNULL(Interest, 'No Value') + CHAR(10) + CHAR(13)
23
24                    FROM @varTableA 
25                    WHERE @tablecounter = @iterator)
26
27EXEC msdb.dbo.sp_send_dbmail @profilename = [name], @recipients = [reclist], @subject = [sub], @body = @messageBody
28SET @iterator = @iterator + 1
29END
30	
31CREATE TABLE dbo.table1(a INT, b INT);
32CREATE TABLE dbo.table2(a INT, b INT);
33CREATE TABLE dbo.table3(a INT, b INT);
34
35INSERT dbo.table1(a,b) VALUES(1,1),(1,2),(1,NULL);
36INSERT dbo.table2(a,b) VALUES(1,1),(NULL,NULL),(1,NULL);
37INSERT dbo.table3(a,b) VALUES(1,1),(1,2),(1,2);
38	
39CREATE TABLE #x
40(
41  t           NVARCHAR(512),                     -- table name
42  nullrows    INT NOT NULL DEFAULT 0,            -- number of rows with at least one NULL
43  nullvalues  INT NOT NULL DEFAULT 0,            -- total NULL values in table
44  nulldist    NVARCHAR(MAX) NOT NULL DEFAULT N'' -- how many NULLs in each column
45);
46
47INSERT #x(t) VALUES(N'dbo.table1'),(N'dbo.table2'),(N'dbo.table3');
48	
49DECLARE @sql NVARCHAR(MAX) = N'';
50
51
52SELECT @sql = @sql + N'
53  UPDATE #x SET nullrows = nullrows 
54    + (SELECT COUNT(*) FROM ' + t.t 
55    + N' WHERE (' + STUFF((SELECT N' OR ' 
56    + QUOTENAME(c.name) + N' IS NULL'
57  FROM sys.columns AS c
58  WHERE OBJECT_ID(t.t) = c.[object_id]
59  FOR XML PATH, TYPE).value(N'.[1]',N'nvarchar(max)'),
60    1, 4, N'') + N')) WHERE t = N''' + t.t + N''';'
61FROM #x AS t;
62
63EXEC sys.sp_executesql @sql;
64SET @sql = N'';
65
66SELECT @sql = @sql + N'
67  IF EXISTS (SELECT 1 FROM ' + t + N' WHERE ' 
68    + QUOTENAME(c.name) + N' IS NULL)
69  UPDATE #x SET nullvalues = nullvalues 
70    + (SELECT COUNT(*) FROM ' + t + N' WHERE ' 
71    + QUOTENAME(c.name) + N' IS NULL),
72    nulldistribution = nulldistribution + '''
73    + c.name + N':'' + RTRIM((SELECT COUNT(*) FROM ' 
74    + t + N' WHERE ' + QUOTENAME(c.name) + N' IS NULL))
75    + N'','' WHERE t = N''' + t + N''';'
76  FROM #x AS t
77  INNER JOIN sys.columns AS c
78  ON OBJECT_ID(t.t) = c.[object_id];
79
80EXEC sys.sp_executesql @sql;
81
82SELECT * FROM #x;
83	
84t             nullrows   nullvalues   nulldistribution
85dbo.table1    1          1            b:1,
86dbo.table2    2          3            a:1,b:2,
87dbo.table3    0          0
88	
89create table t1 (id int NOT NULL, a int, b varchar(10), c date);
90go
91
92insert t1(id, a, b, c)
93values (1, 1, 'a', '2015-10-01'), 
94       (2, NULL, 'a', '2015-10-01'), 
95       (3, 1, NULL, '2015-10-01'), 
96       (4, 1, 'a', NULL);
97go
98
99select * from t1;
100go
101
102;with x as
103(
104    select
105        ID, convert(varchar(max), a)
106          + convert(varchar(max), b) 
107          + convert(varchar(max), c) as HasNull
108    from t1
109)
110select
111    ID
112from x
113where x.HasNull is null;
114
115go
116drop table t1;