I have a database and there is not any trigger which follows the transactions in said database.
Now, I have to make a report that shows which table has mostly inserted so far. How can I do that?
-
As I understand you simply retrieve number of records in a table which equals number of inserts.– Rajesh RanjanCommented Jul 18, 2019 at 10:39
-
Approximate insertion count can be obtained by: checking records count increase (if no deletions), checking autoincrement (if exists) value increase.– AkinaCommented Jul 18, 2019 at 10:40
-
@RajeshRanjan We can say it like that. I want to produce an report which tells us most inserted table between two certain dates.– OmerCommented Jul 18, 2019 at 10:48
-
@akina Is this approach true for everytime? Cause, there are different tables which doesn't include any identity column.– OmerCommented Jul 18, 2019 at 10:50
-
No identity - its checking not applicable. Is this approach true for everytime? No of course.– AkinaCommented Jul 18, 2019 at 10:52
|
Show 4 more comments
1 Answer
To know mostly inserted table you can simply retrieve number of rows/records in the table.
You can use following query to find it. Mostly inserted tables will be displayed on top of the records.
SELECT
o.Name TableName,SUM(ps.row_count) Total_Rows
FROM
sys.dm_db_partition_stats ps
inner join sys.objects o
on ps.object_id=o.object_id
WHERE o.type='U'
GROUP BY
o.Name
ORDER BY 2 DESC
Edit:1
As you described you requirement in comments, "you need find mostly inserted tables between a date range. It can be done using several method.
- Using AFTER TRIGGER on the tables.
- Using IDENTITY type column along with a column which records insert date & time (like Amina commented).
- Using CDC you can record date & time of every changes in the table.
- enabling Temporal Table (SQL Server 2016 +) feature on a table to record every changes.
- Track Data Changes (SQL Server 2017)
Thanks!
answered Jul 18, 2019 at 10:41
