SQL Server - 检索数据库中每个表的最后一条记录

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).

我正在寻找在数据库的每个表中输入的最后一条记录。我知道如何一次一个地返回每个表的最后一条记录。但是,我需要循环遍历所有表(大约10,000)并找到最后发布的记录。我可以通过脚本执行此操作(即将所有表放入一个数组,然后为每个表执行一次)。

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".

除了从表查询中获取最后一篇文章并使用系统表之外,还有其他方法吗?例如,我可以使用“SELECT name FROM Sys.tables”获取所有表名,将其放入数组,然后使用foreach循环获取最后一条消息“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.

我希望在上次更新表时系统表中有一个字段。我不想为每个查询编写脚本,我宁愿运行SQL查询。我没有找到这样的。

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

这是我能做的(不是实际的语法)但我想在不查询表的情况下使用SQL查询 - 这可能吗?

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

5 个解决方案

#1

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

您可以使用sp_MSforeachtable存储过程对数据库中的所有表执行查询。

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

例如,exec sp_MSforeachtable'从顶部选择1 *?'

#2

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/.

桌子最后更新时有内置的方式: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: http://sqlblog.com/blogs/aaron_bertrand/archive/2008/05/06/when-was-my-database-table-last-accessed.aspx

请注意,如果重新启动SQL Server,这是不可靠的。 DMV被清除,因此它可能看起来像一个从未被访问过的表,实际上它可能在重启前5分钟被访问过。 Aaron Bertrand对这一事实发表了评论,并在此发表了博客:http://sqlblog.com/blogs/aaron_bertrand/archive/2008/05/06/when-was-my-database-table-last-accessed.aspx

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.

或者,如果您需要事务一致的答案,则可以遍历所有表名并为所有表发出查询。这似乎是一种可行的方法。

#3

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

您需要设置Change Data Capture或具有LastUpdate类型列才能可靠地获取此数据。

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.

对主键的任何类型的查询都不起作用,因为主键按该数据类型的升序或降序排序,而不是基于插入记录的时间。

#4

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.

我有一种丑陋的方式来做你想做的事情(如果你希望你的结果在一个查询中sp_MSforeachtable不起作用)...它会在我的数据库上永远处理,因为日期没有编入索引,但它应该有效。

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)

#5

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;

#1