Solved

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

Posted on 2013-05-25
6
364 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
  • 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 10

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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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 10

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

810 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