October 13, 2017 at 1:00 pm
Hi Experts,
Need some tsql help.
I wanted all the column names from all below 3 tables which has nulls. i.e. The if the column has a NULL value in table1 and table 2 and table 3, display that column in the output. From below sample data i need to get output as "c1" and "c2" because these are the 2 columns which has NULL values across all 3 tables. How can can we do that?
select
'app1' as apptype,
NULL as c1,
Null as c2,
1 as c3,
2 as c4
into #t1
select
'app2' as apptype,
NULL as c1,
Null as c2,
1 as c3,
2 as c4
into #t2
select
'app3' as apptype,
NULL as c1,
Null as c2,
1 as c3,
2 as c4
into #t3
select * from #t1
select * from #t2
select * from #t3
Thanks in advance.
Sam
October 13, 2017 at 2:00 pm
Since your 3 tables have the same layout couldn't you union them together into one table then check that one table's columns for NULL?
If you are working with real tables not temp ones you may be able to use the sys.columns and sys.tables to look at each column.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
October 16, 2017 at 4:14 am
SELECT CASE
WHEN (SUM(a.ISC1Null)+SUM(a.ISC2Null)+SUM(a.ISC3Null)+SUM(a.ISC4Null)) %3 = 0 THEN 'c1,c2,c3,c4'
WHEN (SUM(a.ISC1Null)+SUM(a.ISC2Null)+SUM(a.ISC3Null)) %3 = 0 THEN 'c1,c2,c3'
WHEN (SUM(a.ISC1Null)+SUM(a.ISC2Null)+SUM(a.ISC4Null)) %3 = 0 THEN 'c1,c2,c4'
WHEN (SUM(a.ISC1Null)+SUM(a.ISC3Null)+SUM(a.ISC4Null)) %3 = 0 THEN 'c1,c3,c4'
WHEN (SUM(a.ISC2Null)+SUM(a.ISC3Null)+SUM(a.ISC4Null)) %3 = 0 THEN 'c2,c3,c4'
WHEN (SUM(a.ISC1Null)+SUM(a.ISC2Null)) %3 = 0 THEN 'c1,c2'
WHEN (SUM(a.ISC1Null)+SUM(a.ISC3Null)) %3 = 0 THEN 'c1,c3'
WHEN (SUM(a.ISC1Null)+SUM(a.ISC4Null)) %3 = 0 THEN 'c1,c4'
WHEN (SUM(a.ISC2Null)+SUM(a.ISC3Null)) %3 = 0 THEN 'c2,c3'
WHEN (SUM(a.ISC2Null)+SUM(a.ISC4Null)) %3 = 0 THEN 'c2,c4'
WHEN (SUM(a.ISC3Null)+SUM(a.ISC4Null)) %3 = 0 THEN 'c3,c4'
WHEN (SUM(a.ISC1Null)) %3 = 0 THEN 'c1'
WHEN (SUM(a.ISC2Null)) %3 = 0 THEN 'c2'
WHEN (SUM(a.ISC3Null)) %3 = 0 THEN 'c3'
WHEN (SUM(a.ISC4Null)) %3 = 0 THEN 'c4'
END AS NullCols
FROM
(
SELECT *,
CASE
WHEN c1 IS NULL THEN
1
ELSE
NULL
END AS ISC1Null,
CASE
WHEN c2 IS NULL THEN
1
ELSE
NULL
END AS ISC2Null,
CASE
WHEN c3 IS NULL THEN
1
ELSE
NULL
END AS ISC3Null,
CASE
WHEN c4 IS NULL THEN
1
ELSE
NULL
END AS ISC4Null
FROM #t1
UNION ALL
SELECT *,
CASE
WHEN c1 IS NULL THEN
1
ELSE
NULL
END AS ISC1Null,
CASE
WHEN c2 IS NULL THEN
1
ELSE
NULL
END AS ISC2Null,
CASE
WHEN c3 IS NULL THEN
1
ELSE
NULL
END AS ISC3Null,
CASE
WHEN c4 IS NULL THEN
1
ELSE
NULL
END AS ISC4Null
FROM #t2
UNION ALL
SELECT *,
CASE
WHEN c1 IS NULL THEN
1
ELSE
NULL
END AS ISC1Null,
CASE
WHEN c2 IS NULL THEN
1
ELSE
NULL
END AS ISC2Null,
CASE
WHEN c3 IS NULL THEN
1
ELSE
NULL
END AS ISC3Null,
CASE
WHEN c4 IS NULL THEN
1
ELSE
NULL
END AS ISC4Null
FROM #t3
) AS a;
First solve the problem then write the code !
October 16, 2017 at 4:38 am
To the OP:
is this meant to be a dynamic statement, or are you tables really of this format? Are all your tables named "t#" and your columns named "c#"? I assume, that we can't really UNION ALL your tables to combine.
I could be wrong, but if so, then I would recommend moving to have columns and tables with meaningful names. Having to look up what column c24 in table t12, then column c9 in table t9, then c3 in t2 would be a logistical nightmare.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 16, 2017 at 4:50 am
SELECT
[Result] = REVERSE(STUFF(REVERSE('Columns containing NULL in all three tables: '
+ CASE WHEN SUM(c1) < SUM([RowCount]) THEN 'c1, ' ELSE '' END
+ CASE WHEN SUM(c2) < SUM([RowCount]) THEN 'c2, ' ELSE '' END
+ CASE WHEN SUM(c3) < SUM([RowCount]) THEN 'c3, ' ELSE '' END
+ CASE WHEN SUM(c4) < SUM([RowCount]) THEN 'c4, ' ELSE '' END),1,2,'')) + '.'
FROM (
SELECT [RowCount] = COUNT(*), c1 = COUNT(c1), c2 = COUNT(c2), c3 = COUNT(c3), c4 = COUNT(c4) from #t1
UNION ALL
SELECT COUNT(*), COUNT(c1), COUNT(c2), COUNT(c3), COUNT(c4) from #t2
UNION ALL
SELECT COUNT(*), COUNT(c1), COUNT(c2), COUNT(c3), COUNT(c4) from #t2
) d
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 18, 2017 at 4:12 am
Hi Mike/CTEGuy,
Assuming the temp tables as permanent tables (i.e. t1, t2 , t3 ), how can we make the script as dynamic. I want to reuse the code for any n-number of tables with same number of columns and datatypes ? Here in this case, I have 3 tables, but in reality I can have 5-6 tables and can have 150 columns. In that case, how can we re-write the tsql and make more dynamic ?
October 18, 2017 at 6:54 am
Just a heads-up on this... this type of query can cause statistics to be constructed on every column it touches. That could cause some pretty good delays for INSERTs, UPDATEs, and DELETEs if auto stats updates kick in. It may also make your DBA hate you because the extra per-column statistics need to be dealt with during statistics updates during maintenance periods.
Since it's a bit difficult to determine which statistics are actually being used for the normal course of daily queries (unless you also happen to have a system that uses Trace Flag 8666 to figure it all out, and that's not a trivial task), you need to determine if the columns have statistics on them BEFORE running the null-finder code and, if not, have code that will drop the statistics that are created for this evolution. Either that, or do it like Chris did in his code which, I believe, will avoid the creation of statics on each column because there are no WHERE clauses (or joins) in his code.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 18, 2017 at 9:23 am
I modified your data and tried to use ChrisM's code. I added a value to C2 in #T3, Chris's code still says C2 is NULL on all 3 tables. I then took his code and modified it to say if the count is zero, then it is NULL on all tables.
select
'app1' as apptype,
NULL as c1,
Null as c2,
1 as c3,
2 as c4
into #t1
;
select
'app2' as apptype,
NULL as c1,
Null as c2,
1 as c3,
2 as c4
into #t2
;
select
'app3' as apptype,
NULL as c1,
0 as c2,
1 as c3,
2 as c4
into #t3
;
-- ChrisM@Work code
SELECT
[Result] = REVERSE(STUFF(REVERSE('Columns containing NULL in all three tables: '
+ CASE WHEN SUM(c1) < SUM([RowCount]) THEN 'c1, ' ELSE '' END
+ CASE WHEN SUM(c2) < SUM([RowCount]) THEN 'c2, ' ELSE '' END
+ CASE WHEN SUM(c3) < SUM([RowCount]) THEN 'c3, ' ELSE '' END
+ CASE WHEN SUM(c4) < SUM([RowCount]) THEN 'c4, ' ELSE '' END),1,2,'')) + '.'
FROM (
SELECT [RowCount] = COUNT(*), c1 = COUNT(c1), c2 = COUNT(c2), c3 = COUNT(c3), c4 = COUNT(c4) from #t1
UNION ALL
SELECT COUNT(*), COUNT(c1), COUNT(c2), COUNT(c3), COUNT(c4) from #t2
UNION ALL
SELECT COUNT(*), COUNT(c1), COUNT(c2), COUNT(c3), COUNT(c4) from #t3
) d
;
-- revised code
SELECT
[Result] = REVERSE(STUFF(REVERSE('Columns containing NULL in all three tables: '
+ CASE WHEN SUM(c1) = 0 THEN 'c1, ' ELSE '' END
+ CASE WHEN SUM(c2) = 0 THEN 'c2, ' ELSE '' END
+ CASE WHEN SUM(c3) = 0 THEN 'c3, ' ELSE '' END
+ CASE WHEN SUM(c4) = 0 THEN 'c4, ' ELSE '' END),1,2,'')) + '.'
FROM (
SELECT [RowCount] = COUNT(*), c1 = COUNT(c1), c2 = COUNT(c2), c3 = COUNT(c3), c4 = COUNT(c4) from #t1
UNION ALL
SELECT COUNT(*), COUNT(c1), COUNT(c2), COUNT(c3), COUNT(c4) from #t2
UNION ALL
SELECT COUNT(*), COUNT(c1), COUNT(c2), COUNT(c3), COUNT(c4) from #t3
) d
;
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
October 18, 2017 at 12:14 pm
Thanks below86 for the correction. Can we make this dynamic script ? Should be able to pass on any number of tables but number of columns will be the same. if that is the case, how to make the above script as dynamic??
October 18, 2017 at 3:32 pm
vsamantha35 - Wednesday, October 18, 2017 12:14 PMThanks below86 for the correction. Can we make this dynamic script ? Should be able to pass on any number of tables but number of columns will be the same. if that is the case, how to make the above script as dynamic??
I think you could if you used the sys.columns and sys.tables tables. I don't have the time right now to figure out what that would look like.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
October 19, 2017 at 10:05 am
This code will get you close. Maybe you or someone else can start with this to figure out how to be able to set it up to handle multiple tables. Or maybe someone can come up with a better solution.
DECLARE @tablename VARCHAR(100);
SET @tablename = 'table name here';
SELECT table_name, column_name
INTO #temp
FROM information_schema.columns
WHERE table_name = @tablename
;
DECLARE @SQL VARCHAR(MAX);
SET @SQL = 'SELECT [Result] = REVERSE(STUFF(REVERSE(''Columns containing NULL in all tables: '''
;
SET @SQL = RTRIM(@SQL) + STUFF((
SELECT ' + CASE WHEN SUM(' + column_name + ') = 0 THEN ''' + column_name + ', '' ELSE '''' END'
FROM #temp
for xml path (''))
,1,2,'')
;
SET @SQL = RTRIM(@SQL) + '),1,2,''''))';
SET @SQL = RTRIM(@SQL) + ' FROM ( ';
-- LOOPHERE; -- loop back to here for each table
SET @SQL = RTRIM(@SQL) + (
SELECT
'SELECT COUNT(*) AS RowCounts, '+
stuff((
select ', ' + 'COUNT(' + t2.column_name + ') as ' + t2.column_name + CHAR(10)
from #temp AS t2
where t2.table_name = t.table_name
for xml path (''))
,1,2,'')+' from ' + t.table_name
from #temp AS t
group by t.table_name)
;
-- Add UNION ALL to @SQL -- SET @SQL = RTRIM(@SQL) + ' UNION ALL '
-- Go to LOOPHERE if there are more tables
SET @SQL = RTRIM(@SQL) + ') AS d';
-- you will want to execute the SQL in the @SQL variable
SELECT @SQL
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
October 20, 2017 at 6:51 am
Thanks a lot a below86 for taking time. Will try it on my own and see how it works. Many thanks.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply