Solved

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

Posted on 2013-05-25
6
372 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
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.

 
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

623 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