Solved

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

Posted on 2013-05-25
6
368 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Help Required 3 117
too many installs coming along with SQL 2016? 1 27
SQL Database Restore 2008 R2 1 27
Using this function 4 38
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.
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.

685 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