Is it possible to query all tables in 1 SQL query where 2 particular columns are NULL

Hello,

I need to create a single SQL query which returns all the tables where the columns CREATED_By and UPDATED_BY are NULL.

I was able to do it on individual table which returns the rows where value is NULL, but i want all Tables Names only in database with NULL value, no need for the entire data to be returned.

Please help me on this!!

I am trying something like this, but unsuccessful,

SELECT DISTINCT `TABLE_NAME` FROM INFORMATION_SCHEMA.COLUMNS WHERE `COLUMN_NAME` LIKE ("%CREATED_BY%" and "%UPDATED_BY%")IS NULL or `COLUMN_NAME` LIKE ("%CREATED_BY%" and "%UPDATED_BY%") = ' ' ORDER BY `UPDATED_BY` DESC

  • Hi Siva, 

    Technically, you could probably do this with a stored procedure having output parameters, or a tabular function.  This is going to be expensive to run.

    What's your use case?
    Are you planning something to do routinely, or are you planning cleanup?
    Do you really want every table in the db?

  • Hi Siva,

    As Robert mentioned, you can do this via a stored proc call by writing dynamic SQL and taking a parameter for "TABLE_NAME". It will replace the actual select query's table name in run time by passing an input. If you want to do this for each table then a looping function can be utilized.

    Keep in mind the size of tables and the number of tables you're looping on though.

 Discussion posts and replies are publicly visible