Solved

How to select tables where one particular column only has NULL values

Posted on 2013-05-25
6
370 Views
Last Modified: 2013-06-02
In my database I would like to select tables where column DataRowVersion  is null.

If any tables contain DataRowVersion column as non-null (even one row) I should not select the table.

I would like to get the table name alone. How to achieve that? Please assist.
0
Comment
Question by:Easwaran Paramasivam
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39196564

1.

run the below script in your SSMS
select 'SELECT  ' + '''' + T.TABLE_SCHEMA+ T.TABLE_NAME + '''' + ' WHERE EXISTS ( SELCT 1 FROM ' + T.TABLE_SCHEMA+ T.TABLE_NAME + ' WHERE DataRowVersion is null  UNION ALL '
from INFORMATION_SCHEMA.TABLES T
JOIN INFORMATION_SCHEMA.COLUMNS C
ON T.TABLE_NAME = C.TABLE_NAME
AND c.COLUMN_NAME ='DataRowVersion'

Open in new window

the above code generates some select statements as below

select ...... union all
select ...... union all
select ...... union all
select ...... union all
select ...... union all

now copy them back into your SSMS and remove the last union all, it should look like as below

select ...... union all
select ...... union all
select ...... union all
select ...... union all
select ......

and execute it, it should give you the table names you desired...
In case you got an error, let us know the exact steps you followed and also the resultant select statements.
0
 
LVL 11

Expert Comment

by:HuaMinChen
ID: 39197768
Try

SELECT
    c.name 'Column Name',
    t.Name 'Data type',
    c.max_length 'Max Length',
    c.precision ,
    c.scale ,
    c.is_nullable,
    ISNULL(i.is_primary_key, 0) 'Primary Key'
FROM    
    sys.columns c
INNER JOIN
    sys.types t ON c.system_type_id = t.system_type_id
LEFT OUTER JOIN
    sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN
    sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE
    c.is_nullable=1
      and c.name='DataRowVersion';
0
 
LVL 16

Author Comment

by:Easwaran Paramasivam
ID: 39200936
I hope my question is not understood properly. I've a table TableA which has NULLABLE column DataRowVersion. If any one of the value of the column is not null then I do not want to return the table name. If all values in the column are NULL then I need to return the table. How to achieve that?
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 16

Assisted Solution

by:Surendra Nath
Surendra Nath earned 250 total points
ID: 39200945
Similar to your previous question with update,  please follow the same steps

1) Execute the below query, you will get some select statements..
2) Copy them back to query window and remove the last union all (only union all for the last statement).... refer my previous comment for more information on this

select 'SELECT  ' + '''' + T.TABLE_SCHEMA+ T.TABLE_NAME + '''' + ' WHERE NOT EXISTS ( SELECT 1 FROM ' + T.TABLE_SCHEMA+ T.TABLE_NAME + ' WHERE DataRowVersion is not null )  UNION ALL '
from INFORMATION_SCHEMA.TABLES T
JOIN INFORMATION_SCHEMA.COLUMNS C
ON T.TABLE_NAME = C.TABLE_NAME
AND c.COLUMN_NAME ='DataRowVersion'

Open in new window

0
 
LVL 11

Accepted Solution

by:
HuaMinChen earned 250 total points
ID: 39203326
Hi EaswaranP,
You can have a cursor using my above script. Then have a loop to further run the generated script to check whether the column in the relevant table is null or not.

Read:
WHILE (Transact-SQL)
http://msdn.microsoft.com/en-us//library/ms178642.aspx

DECLARE CURSOR (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms180169.aspx

sp_executesql (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms188001.aspx
0
 
LVL 16

Author Closing Comment

by:Easwaran Paramasivam
ID: 39214842
Thanks.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question