SQL Server – How to Query Which Table Is Inserted Mostly

sql serversql server 2014sql-server-2012sql-server-2016

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?

Best 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!

Related Solutions

SQL Server 2012 – How to Inhibit Trigger Execution

When the utility runs, just have it (or whatever calls the utility) run this command:

DISABLE TRIGGER dbo.trigger_name ON dbo.table_name;

And then afterward:

ENABLE TRIGGER dbo.trigger_name ON dbo.table_name;

It would be better to have the utility do this just around the statement(s) affecting this table specifically (and wrap that in a transaction), since the trigger is disabled for all users, not just the utility.

Another way - if the utility passes a specific application name in the connection string, or is known to be the only thing connecting as a certain user or from a certain host - is to check sys.dm_exec_connections/sys.dm_exec_sessions within the trigger.

ALTER TRIGGER dbo.trigger_name
ON dbo.table_name
FOR INSERT, ...
AS
BEGIN
  SET NOCOUNT ON;

  IF NOT EXISTS 
  (
    SELECT 1 FROM sys.dm_exec_connections
    WHERE client_net_address = '10.65.10.5' AND session_id = @@SPID

    /* or 
    SELECT 1 FROM sys.dm_exec_sessions
    WHERE host_name = N'MELVIN' AND session_id = @@SPID
    */

    /* or 
    SELECT 1 FROM sys.dm_exec_sessions
    WHERE program_name = N'My Utility App Name' AND session_id = @@SPID
    */
 )
  BEGIN
    -- trigger logic here
  END
END

If the utility logs in as a specific login separate from the main app, then you could say:

IF USER_NAME() <> N'user name' /* or SUSER_SNAME() or ORIGINAL_LOGIN() */
BEGIN
  -- trigger logic here 
END

If the utility always handles multiple rows, and the main app always only handles one row, then you could do this:

IF (SELECT COUNT(*) FROM inserted) = 1
BEGIN
  -- trigger logic here
END

If the utility could call a stored procedure instead of using ad hoc SQL, your life would be a lot easier, because you could lock the table in a transaction, disable the trigger, do your thing, then enable the trigger again. (Having this control is yet another benefit of using stored procedures and not compiling your SQL code into an app.)

If you can't fix any of these things, then you may want to look into disabling the trigger and achieving its goal in some other way. Without understanding exactly what the trigger does, it's difficult to be any more precise than that.

Sql-server – Uexpected values inserted in table

If the FuncWeight field in the #Temp2 table is truly defined as float(3), then the most likely problem is that the datatype of @constVal is DECIMAL(x, 0) or just DECIMAL. Either of those would round up the 0.7 values to be just 1, though the 0.3 and 0.35 values would round down to 0.

Nothing in that posted code or data indicates what could force all values of FunctionWeight to come out as 1. Since some of the FunctionWeight values are 1, I have to assume that the "snapshot of #Temp2" image was taken after testing FunctionName values that have non-1 values for FunctionWeight.

Another possibility is that we are not being shown all rows in the FunctionWeights table and that there is duplication of the FunctionName values where the duplicated rows have a value of 1 for FuncWeight. In that case the last record read by the select @constVal = FunctionWeight from dbo.FunctionWeights query would be the value stored in @constVal, though that would not always be the "duplicated" row since there is no ORDER BY on the query.

Best Answer

Related Solutions

SQL Server 2012 – How to Inhibit Trigger Execution

Sql-server – Uexpected values inserted in table

Related Question