0

All tables in a DB have the fields creationdate and revisiondate, which are date fields just as you'd think. Looking for a SQL query to find all instances where creationdate > '2017-02-01'. I'm not able to find an example where you loop through each table to return all new records as of X date in a DB. The DB has 1000 tables so I need to be able to search dynamically. The one table version of the query is (select * from tableA where creationdate > '2017-02-01') I just need to do that against all tables. Thanks!!!!

Jeff Smith
  • 1
  • 1
  • http://stackoverflow.com/questions/9679997/sql-server-sp-msforeachtable-usage-to-select-only-those-tables-which-meet-some-c – cha Feb 09 '17 at 01:11

1 Answers1

0
SELECT schema.column_1, schema.column2
FROM   table_name_1
UNION
SELECT schema.column_same_datatype, schema.column2_same_datatype
FROM   table_name_2
WHERE  creation_date > '2017-02-01';

NOTE: YOu should have precaution about date format. I think the most common date format is DD-MM-YYYY.

  • Thanks for helping. I'm looking to query on the field creationdate across all tables in the DB vs. just a single or two tables of known name. My DB has 1000 tables, each has creationdate and revisiondate on each record. – Jeff Smith Feb 08 '17 at 23:06