Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 376
  • Last Modified:

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

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
Easwaran Paramasivam
Asked:
Easwaran Paramasivam
  • 2
  • 2
  • 2
2 Solutions
 
Surendra NathCommented:

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
 
HuaMinChenBusiness AnalystCommented:
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
 
Easwaran ParamasivamAuthor Commented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Surendra NathCommented:
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
 
HuaMinChenBusiness AnalystCommented:
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
 
Easwaran ParamasivamAuthor Commented:
Thanks.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now