CODEWITHSUNDEEP
sqlsql-serversql-server-2008
user1188241
user1188241

Reputation: 123

SQL Server - Retrieve last record for each table in a database

I am looking for the last record entered in each table of the database. I know how to return the last record for each table one at a time. However, I need to loop through all the tables (about 10,000) and find the last record posted. I can do this by scripting (i.e. get all tables into an array and then doing a for each).

Is there a way other than getting the last post from a table query and use a system table? For example, I can use "SELECT name FROM Sys.tables" to get all the table names, put that into an array, and then a foreach loop to get the last message "SELECT TOP 1 date FROM table_A ORDER BY date DESC".

I am hoping there is a field in a system table that holds when tables were last updated. I prefer not to script a for each query, I would rather run a SQL query. I have not found such.

This is what I can do (not actual syntax) but I want to use SQL query without querying the table - is this possible?

Array = "SELECT name Sys.tables"
foreach item in Array execute "SELECT TOP 1 date FROM item ORDER BY date DESC"

Upvotes: 1

Views: 6047

Answers (5)

usr
usr

Reputation: 171178

There is built-in way to well when a table was last updated: http://blog.sqlauthority.com/2009/05/09/sql-server-find-last-date-time-updated-for-any-table/.

Beware that this is not reliable if you restart SQL Server. The DMV is cleared out, so it may look like a table has never been accessed when in fact it may have been accessed 5 minutes before the restart. Aaron Bertrand added a comment on this fact and blogged about it here:

Alternatively, if you need a transactionally consistent answer, you can loop through all table names and issue a query for all of them. It seems to be a feasible way to do this.

Upvotes: 0

S. S. Rawat
S. S. Rawat

Reputation: 6111

string query = "Select * from table";

SqlDataAdapter da = new SqlDataAdapter(query, con); // con is connection 

ds = new DataSet();
da.Fill(ds);

int last = Convert.ToInt32(ds.Tables[0].Rows.Count);

string id = ds.Tables[0].Rows[last-1]["State"].ToString();

textBox1.Text = id;

Upvotes: 0

RealUlysse
RealUlysse

Reputation: 31

I have a ugly way to do what you want (If you want your result in a single query sp_MSforeachtable would not work) ... it seam to take forever to do on my database because the date is not indexed but it should work.

DECLARE @Req AS NVARCHAR(MAX)
SET @Req = (SELECT LEFT(ids,LEN(ids) - 6) 
           FROM
           (SELECT 'SELECT (SELECT Top 1 date FROM ' + t.name + ' ORDER BY date DESC) UNION ' 
            FROM Sys.tables t 
            INNER JOIN Sys.columns c ON t.object_id = c.object_id
                AND c.name = 'date'
            WHERE t.schema_id = 1
            FOR XML PATH('')) a(ids)
           WHERE ids IS NOT NULL
             AND LEN(ids) > 7)
EXEC(@Req)

Upvotes: 0

Vince Pergolizzi
Vince Pergolizzi

Reputation: 6584

You need to setup Change Data Capture or have LastUpdate type columns to be able to get this data reliably.

Any kind of query on the primary key won't work as primary keys are ordered in ascending or descending order for that data type and not based on the time the record was inserted.

Upvotes: 0

Raghuram Duraisamy
Raghuram Duraisamy

Reputation: 136

You can use sp_MSforeachtable stored procedure to execute a query on all tables in a database.

For example, exec sp_MSforeachtable 'select top 1 * from ?'

Upvotes: 3

Related Questions