none
Display all tables and its column from database if particular column value is NULL

    Question

  • I have a requirement in which I need to display certain columns from table in database, if

    1)- One of the column returns the value NULL within last 24 hours of data warehouse load

    E:G;

    I have table test1, test2,test3 in a database 'dummy'

    All the tables in database has common columns i-e 'modified_date','building_id','Create_date'.

    If modified_date column from any table returns NULL and it is within 24hours of 'Create_Date', I should display/return rows with

    Table Name, building_id , Create_date of that table from a database.

    I'm thinking to use INFORMATION_SCHEMA.COLUMNS to get all table info in a database but not sure how to join it with table having modified_date is NULL within 24hours of Create_date.

    I would appreciate your help.


    ZK

    Wednesday, July 09, 2014 2:56 AM

Answers

  • Using some code by Satheesh:

    if object_id('tempDB..#temp', N'U') IS NOT NULL drop table #temp
    create table #Temp(
    Tablename sysname,
    modified_date datetime,
    building_id int,
    Create_date datetime);
    
    create table Test1(
    modified_date datetime,
    buildingId int, -- different column name
    Create_date datetime);
    
    create table Test2(
    modified_date datetime,
    building_id int,
    Create_date datetime);
    
    Insert into Test1 
    values
    (NULL,1, dateadd(hh,-4,getdate())), --expect this row
    (NULL,2, dateadd(hh,-26,getdate()));--not in the final result
    
    Insert into Test2 
    values
    (GetDate(),3, dateadd(hh,-4,getdate())),--not in the final result
    (NULL,4, dateadd(hh,-8,getdate())); -- in the final result
    
    declare @cmd varchar(4000);
    SET @cmd=(SELECT 'INSERT INTO #TEMP SELECT '  +
    
    QUOTENAME(table_name, '''') +
     ',modified_date, ' + 
     
     CASE WHEN EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS T1
      WHERE T1.TABLE_SCHEMA = T.TABLE_SCHEMA AND T.TABLE_NAME = T1.TABLE_NAME
      AND T1.COLUMN_NAME = 'building_id') THEN 'building_id' ELSE 'NULL' end + ', Create_date FROM '+ 
     QUOTENAME(table_schema) + '.' + quotename(table_name) +
      +' WHERE modified_date IS NULL AND Create_Date BETWEEN DateAdd(HH,-24, getdate()) AND GETDATE() ;
      ' 
      FROM INFORMATION_SCHEMA.Columns T 
      WHERE T.COLUMN_NAME = 'modified_date' AND EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS T1
      WHERE T1.TABLE_SCHEMA = T.TABLE_SCHEMA AND T.TABLE_NAME = T1.TABLE_NAME
      AND T1.COLUMN_NAME = 'CREATE_DATE')
      
      FOR XML PATH(''),type).value('.', 'nvarchar(max)');
    print @cmd;
    execute (@cmd)
    
    select * from #temp	

    This assumes that tables have both MODIFIED_DATE and CREATE_DATE, but Building_ID column is optional.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, July 09, 2014 5:21 PM
    Moderator

All replies

  • I think I can do this by using while loop or running cursor but not sure how. Here is what I'm thinking:

    1)- First get the table name along with Identity column (which returns ID for each table) using INFORMATION_SCHEMA.TABLE and store into temp table

    2)- Run cursor/ while loop to run dynamic sql  using ID column

    3)- In while loop, it should return columns building_id , Create_date from each table where modified_date IS NULL.


    ZK

    Wednesday, July 09, 2014 4:44 AM
  • If modified_date column from any table returns NULL and it is within 24hours of 'Create_Date', I should display/return rows with 

    I assume the Create_Date is with in 24 hours of current date.

    Is that you are looking for ?

    drop Table #temp
    create table #Temp(
    Tablename sysname,
    modified_date datetime,
    building_id int,
    Create_date datetime);
    
    declare @cmd varchar(4000);
    SET @cmd=(SELECT 'INSERT INTO #TEMP SELECT '''+name+''',modified_date,building_id,Create_date FROM '+name +' WHERE modified_date IS NULL AND Create_Date BETWEEN DateAdd(HH,-24, getdate()) AND GETDATE() ;' from sys.tables T FOR XML PATH(''));
    print @cmd;
    exec (@cmd)
    
    select * from #temp	
    



    Satheesh
    My Blog | How to ask questions in technical forum


    Wednesday, July 09, 2014 4:45 AM
  • Satheesh, you are close but it is not returning any rows other than table name "test1". Am I missing something?

    Here is the resultset (was in XML format):

    INSERT INTO #TEMP SELECT 'test1',modified_date,building_id,Create_date FROM test1 WHERE modified_date IS NULL AND Create_date BETWEEN DateAdd(HH,-24, getdate()) AND GETDATE() ;

    INSERT INTO #TEMP SELECT 'test2',modified_date,building_id,Create_date FROM test2 WHERE modified_date IS NULL AND Create_date BETWEEN DateAdd(HH,-24, getdate()) AND GETDATE() ;

    Yes, createDate is with in 24 hours of current date.



    ZK


    • Edited by SQL_Admirer Wednesday, July 09, 2014 4:58 AM entered correct name
    Wednesday, July 09, 2014 4:58 AM
  • Here is result set I'm looking for if the column 'modified_date' of any table in database is NULL within last 24 hours.

    Hopefully this would help understanding my requirement.

    Thanks in advance.


    ZK

    Wednesday, July 09, 2014 5:04 AM
  • I tested this query with sample data and i think its working fine. Can you tell me if I understood something wrong?

    drop table test1;
    drop table test2;
    
    create table Test1(
    modified_date datetime,
    building_id int,
    Create_date datetime);
    
    create table Test2(
    modified_date datetime,
    building_id int,
    Create_date datetime);
    
    Insert into Test1 
    values
    (NULL,1, dateadd(hh,-4,getdate())), --expect this row
    (NULL,2, dateadd(hh,-26,getdate()));--not in the final result
    
    Insert into Test2 
    values
    (GetDate(),3, dateadd(hh,-4,getdate())),--not in the final result
    (NULL,4, dateadd(hh,-8,getdate())); -- in the final result
    
    drop table #temp
    create table #Temp(
    Tablename sysname,
    modified_date datetime,
    building_id int,
    Create_date datetime);
    
    declare @cmd varchar(4000);
    SET @cmd=(SELECT 'INSERT INTO #TEMP SELECT '''+name+''',modified_date,building_id,Create_date FROM '+name +' WHERE modified_date IS NULL AND Create_Date BETWEEN DateAdd(HH,-24, getdate()) AND GETDATE() ;' from sys.tables T FOR XML PATH(''));
    print @cmd;
    exec (@cmd)
    
    select * from #temp	
    
    


    Satheesh
    My Blog | How to ask questions in technical forum


    Wednesday, July 09, 2014 5:23 AM
  • whats if table has multiple records with modified_date as NULL? do you need all of them?


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Wednesday, July 09, 2014 5:48 AM
  • Yes, if their Create_Date is within 24hrs of getdate().


    ZK

    Wednesday, July 09, 2014 5:52 AM
  • Yes, if their Create_Date is within 24hrs of getdate().


    ZK

    then you can do something like this

    Create a table as below

    create table Temp(
    Tablename sysname,
    modified_date datetime,
    building_id int,
    Create_date datetime);

    then fill it as below

    EXEC sp_MSforeachtable 'IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ''?'' AND COLUMN_NAME = ''modified_date'') INSERT Temp SELECT ''?'',NULL,building_id,create_date FROM ? WHERE modified_date IS NULL AND Create_Date >= DATEADD(hh,-24,GETDATE()) AND Create_date <= GETDATE()'


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Wednesday, July 09, 2014 6:12 AM
  • Hi,

    Got a solution for u :-)

    DECLARE @sql VARCHAR(4000)
    DROP TABLE #t
    CREATE TABLE #t(name VARCHAR(100),id INT,mod_date DATE,created_date DATE)
    SELECT @sql='INSERT INTO #t '
    SELECT @sql=@sql+'SELECT '''+a.TABLE_NAME+''' AS name,ID,mod_date,created_date FROM ' +a.TABLE_NAME+ ' WHERE mod_date 
    IS NULL AND DATEDIFF(dd,created_date,GETDATE())>1 UNION ' 
    FROM INFORMATION_SCHEMA.TABLES a JOIN INFORMATION_SCHEMA.COLUMNS b  ON a.TABLE_NAME=b.TABLE_NAME AND b.COLUMN_NAME ='mod_date'
    SET @sql=SUBSTRING(@sql,1,len(@sql)-6)
    PRINT(@sql)
    EXEC (@sql)

    ___________________________________
    Mark this as answered if it really helped..
    Wednesday, July 09, 2014 9:04 AM
  • I figured out there is another issue. There are total 60 tables. Out of 60 tables, 55 has got modified_date , 53 has got building_id and 57 has got Created_date column.

    I'm getting error for missing columns. 

    IS it possible to return Null for the columns if they are missing in that table. I'm just concern to meet the condition COLUMN_NAME='modified_date'. This means only 55 table could be returned of they have Null as modified_date returned,, with other column like building_id could be returned as NULL (if this column doesn't exist).


    ZK

    Wednesday, July 09, 2014 4:49 PM
  • Using some code by Satheesh:

    if object_id('tempDB..#temp', N'U') IS NOT NULL drop table #temp
    create table #Temp(
    Tablename sysname,
    modified_date datetime,
    building_id int,
    Create_date datetime);
    
    create table Test1(
    modified_date datetime,
    buildingId int, -- different column name
    Create_date datetime);
    
    create table Test2(
    modified_date datetime,
    building_id int,
    Create_date datetime);
    
    Insert into Test1 
    values
    (NULL,1, dateadd(hh,-4,getdate())), --expect this row
    (NULL,2, dateadd(hh,-26,getdate()));--not in the final result
    
    Insert into Test2 
    values
    (GetDate(),3, dateadd(hh,-4,getdate())),--not in the final result
    (NULL,4, dateadd(hh,-8,getdate())); -- in the final result
    
    declare @cmd varchar(4000);
    SET @cmd=(SELECT 'INSERT INTO #TEMP SELECT '  +
    
    QUOTENAME(table_name, '''') +
     ',modified_date, ' + 
     
     CASE WHEN EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS T1
      WHERE T1.TABLE_SCHEMA = T.TABLE_SCHEMA AND T.TABLE_NAME = T1.TABLE_NAME
      AND T1.COLUMN_NAME = 'building_id') THEN 'building_id' ELSE 'NULL' end + ', Create_date FROM '+ 
     QUOTENAME(table_schema) + '.' + quotename(table_name) +
      +' WHERE modified_date IS NULL AND Create_Date BETWEEN DateAdd(HH,-24, getdate()) AND GETDATE() ;
      ' 
      FROM INFORMATION_SCHEMA.Columns T 
      WHERE T.COLUMN_NAME = 'modified_date' AND EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS T1
      WHERE T1.TABLE_SCHEMA = T.TABLE_SCHEMA AND T.TABLE_NAME = T1.TABLE_NAME
      AND T1.COLUMN_NAME = 'CREATE_DATE')
      
      FOR XML PATH(''),type).value('.', 'nvarchar(max)');
    print @cmd;
    execute (@cmd)
    
    select * from #temp	

    This assumes that tables have both MODIFIED_DATE and CREATE_DATE, but Building_ID column is optional.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, July 09, 2014 5:21 PM
    Moderator
  • I figured out there is another issue. There are total 60 tables. Out of 60 tables, 55 has got modified_date , 53 has got building_id and 57 has got Created_date column.

    I'm getting error for missing columns. 

    IS it possible to return Null for the columns if they are missing in that table. I'm just concern to meet the condition COLUMN_NAME='modified_date'. This means only 55 table could be returned of they have Null as modified_date returned,, with other column like building_id could be returned as NULL (if this column doesn't exist).


    ZK

    So are you telling you need cases without modified_date column existing as well?

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Wednesday, July 09, 2014 5:29 PM
  • Yes, your solution works. Is it possible to remove those tables from the resultset that doesn't have all three columns in the table?

    I mean modified_date ,building_Id , and Create_date should all be present else we are going to ignore the table in our resultset.


    ZK

    Wednesday, July 09, 2014 6:28 PM
  • Yes, it's possible. You can add 2 EXISTS subqueries to check for both names (right now I am checking for one name). There are alternative methods of checking for both columns using relational division, but for your case 2 EXISTS will probably be a simplest one.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    Wednesday, July 09, 2014 7:14 PM
    Moderator
  • Appreciate your help guys! :)

    ZK

    Friday, July 11, 2014 4:26 PM