Post reply

Finding the last records inserted into all tables in a database

  • July 27, 2015 at 10:49 am

    #319267

    I have a CRM database that has a lot of tables and would like to be able to extract the last 'x' records in descending order from each table based on a common a field 'modifiedon' that is in every table and is auto populated by the system.

  • July 27, 2015 at 10:52 am

    #1815199

    i would use the table and column metadata to generate the desired queries,a nd run them individually.

    select

    'SELECT TOP 10 * FROM '

    + quotename(object_name(object_id))

    + ' ORDER BY ModifiedON DESC'

    FROM sys.columns

    where name = 'ModifiedOn'

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 27, 2015 at 11:08 am

    #1815201

    Thank you for the quick reply but I need the actual tables not the system tables.

    Database = 'xxxCRM'

    Example table = accountbase (field = 'modifiedon' or even 'createdon' for each record in the table.

    Account Name CreatedOn ModifiedOn

    Barney Ruble '07/27/2015' '07/25/2017'

  • July 27, 2015 at 11:26 am

    #1815214

    bryan.holmstrom (7/27/2015)

    Thank you for the quick reply but I need the actual tables not the system tables.

    Database = 'xxxCRM'

    Example table = accountbase (field = 'modifiedon' or even 'createdon' for each record in the table.

    Account Name CreatedOn ModifiedOn

    Barney Ruble '07/27/2015' '07/25/2017'

    yes exactly. run my query.

    it finds the "actual" table names, and generates the queries you would need.

    so if the table [accountbase] has a column named [ModifiedOn], it would be among the generated commands.

    my query helps if you have lots and lots of tables with that column; if it was only two or three tables, you could do it manually, but you want to leverage any datasources(like the list of columns) when you can.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 29, 2015 at 10:38 am

    #1815806

    Just wanted to thank you...your solution was perfect

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply