Script to know updated, inserted or deleted records from tables in sql server: Last Table Data Modification

Sometimes we need the information about data modification report that is when records of a table had updated or deleted or inserted in a database. Now I'm going to write a script which will return table name, modification type and Modification time and total modified rows and modified by:

SELECT

    ISNULL( SCHEMA_NAME([schema_id]) + '.','') + ISNULL(OBJECT_NAME(parent_object_id),DBLA.AllocUnitName) AS TableName ,

      DBL.[Begin Time] AS ModificationTime,

      COUNT(*) Total_Rows_Affected,

      MAX(DBL.[Transaction Name]) AS ModificationType,
MAX(SUSER_SNAME(DBL.[Transaction SID])) ModifiedBy

FROM Sys.fn_dblog(NULL,NULL) DBL

INNER JOIN Sys.fn_dblog(NULL,NULL) DBLA

ON  LEFT(DBL.[Current LSN],17) = LEFT(DBLA.[Current LSN],17) LEFT JOIN sys.key_constraints C

ON  SCHEMA_NAME([schema_id]) + '.' + OBJECT_NAME(parent_object_id) + '.' + C.name = DBLA.AllocUnitName

WHERE DBL.[Transaction Name] + '_' + DBLA.Operation IN ('INSERT_LOP_INSERT_ROWS',

'UPDATE_LOP_MODIFY_ROW',

'DELETE_LOP_DELETE_ROWS')

GROUP BY

ISNULL( SCHEMA_NAME([schema_id]) + '.','') + ISNULL(OBJECT_NAME(parent_object_id),

DBLA.AllocUnitName),DBL.[Begin Time]

Sample Output:

TableName	ModificationTime	Total_Rows_Affected	ModificationType
dbo.tblP2	2014/04/29 22:56:09:287	4	UPDATE
dbo.tblP1	2014/04/29 22:54:57:383	3	UPDATE
dbo.tblP1	2014/04/29 22:53:04:750	1	UPDATE
dbo.tblP2	2014/04/29 23:03:20:810	4	DELETE
dbo.tblP1	2014/04/29 22:30:08:947	4	INSERT
dbo.tblP1	2014/04/29 23:03:11:407	3	DELETE
dbo.tblP	2014/04/29 22:29:08:827	4	INSERT
dbo.tblP1	2014/04/29 23:01:33:433	1	DELETE
dbo.tblP	2014/04/29 22:26:59:743	1	INSERT
dbo.tblP	2014/04/29 22:28:08:623	2	INSERT
dbo.tblP2	2014/04/29 22:31:27:680	4	INSERT
dbo.tblP	2014/04/29 21:55:30:880	1	INSERT
dbo.tblP	2014/04/29 22:27:44:353	1	INSERT

Note: Here meaning of

a. INSERT_LOP_INSERT_ROWS:  To get inserted rows information

b. UPDATE_LOP_MODIFY_ROW: :  To get updated rows information

c. DELETE_LOP_DELETE_ROWS: :  To get deleted rows information

So you can customize your query by removing above three string in the IN clause.

XQuery [query()]: 'order by' requires a singleton (or empty sequence), found operand of type 'xs:integer +'

If we will execute following XQuery in sql server:

DECLARE @xml AS XML = ''

SELECT @xml.query('

      let $value := (1,2,3,5,6)

      order by $value descending

      return $value'

)

    

We may get error message something like this:

Msg 2389, Level 16, State 1, Line 5

XQuery [query()]: 'order by' requires a singleton (or empty sequence), found operand of type 'xs:integer +'

Cause: We cannot user order by clause using let since it needs singleton value. So we must have to user for clause to use order by clause.

Solution:

Correct your XQuery like this:

DECLARE @xml AS XML = ''

SELECT @xml.query('

      for $value in (1,2,3,5,6)

      order by $value descending

      return $value'

)

Output:

6 5 3 2 1

XQuery [query()]: The name "" does not denote a namespace

If we will execute following XQuery in sql server:

DECLARE @s AS XML = '

      <P:Root xmlns:P="http://www.exacthelp.com">

            <P:Student Name="Scott"/>

            <P:Stuent Name="Greg"/>

      </P:Root>'

SELECT @s.query('P:Root/P:Student')

We may get error message something like this:

Msg 2229, Level 16, State 1, Line 6

XQuery [query()]: The name "P" does not denote a namespace.

Cause: It is incorrect to use namespace in Xquery in this way.

Solution:

I'm telling four solutions. Correct  Follow any one solution which you want:

1.

SELECT @s.query

      ('declare default element namespace "http://www.exacthelp.com";

       /Root/Student')

2.

      

SELECT @s.query

      ('declare namespace P = "http://www.exacthelp.com";

       /P:Root/P:Student')

3.

;WITH XMLNAMESPACES(DEFAULT 'http://www.exacthelp.com')    

SELECT @s.query('/Root/Student')

4.

;WITH XMLNAMESPACES('http://www.exacthelp.com' AS P) 

SELECT @s.query('/P:Root/P:Student')     

XQuery [query()]: Heterogeneous sequences are not allowed: found 'xs:integer' and 'element(,xdt:untyped) *'

If we will execute following XQUERY sequence script in the sql server:

DECLARE @s AS XML = ''

SELECT @s.query('1,2,a,b')

We may get error message something like this:

Msg 2210, Level 16, State 1, Line 2

XQuery [query()]: Heterogeneous sequences are not allowed: found 'xs:integer +' and 'element(a,xdt:untyped) *'

Cause: Sql server doesn't support  a sequence with different types. In the above sequence 1 and 2 are of integer type while a and b are unknown type. All values in a sequence must be of same time.

Solution: Convert those values to higher data type. We can convert all the values in the sequence to string. For example:

DECLARE @s AS XML = ''

SELECT @s.query('"1","2","a","b"')

Output:

1 2 a b

XQuery: The name supplied to sql:variable('') is not a valid SQL variable name. Variable names must start with the '@' symbol followed by at least one character.

If we will execute following XQUERY script in the sql server:

DECLARE @xlMovie AS XML =

'

<Movies>

<tblMovie ntMovieID="1" vcMovieName="PAIN &amp; GAIN" />

<tblMovie ntMovieID="2" vcMovieName="OBLIVION" />

<tblMovie ntMovieID="3" vcMovieName="IRON MAN 3" />

<tblMovie ntMovieID="4" vcMovieName="EVIL DEAD" />

<tblMovie ntMovieID="5" vcMovieName="MAN OF STEEL" />

</Movies>

'

SELECT @xlMovie.query('sql:variable("$vcDbName")')

We may get error message like:

Msg 9519, Level 16, State 1, Line 12

XQuery: The name supplied to sql:variable('$vcDbName') is not a valid SQL variable name. Variable names must start with the '@' symbol followed by at least one character.

Cause: The local variable which you are using in the XQuery is invalid. In this example there is any local variable $vcDbName is not valid variable name.

Solution:  In sql server local variable name must start with @ symbol. And we must have to fist declare it. If you want to use system function then it must be valid system function. For example:

DECLARE @vcDbName AS SYSNAME = 'ExactHelp'

DECLARE @xlMovie AS XML =

'

<Movies>

<tblMovie ntMovieID="1" vcMovieName="PAIN &amp; GAIN" />

<tblMovie ntMovieID="2" vcMovieName="OBLIVION" />

<tblMovie ntMovieID="3" vcMovieName="IRON MAN 3" />

<tblMovie ntMovieID="4" vcMovieName="EVIL DEAD" />

<tblMovie ntMovieID="5" vcMovieName="MAN OF STEEL" />

</Movies>

'

SELECT @xlMovie.query('sql:variable("@vcDbName")')

Same output:

ExactHelp

XQuery: Unable to resolve sql:variable(''). The variable must be declared as a scalar TSQL variable

If we will execute following XQUERY script in the sql server:

DECLARE @xlMovie AS XML =

'

<Movies>

<tblMovie ntMovieID="1" vcMovieName="PAIN &amp; GAIN" />

<tblMovie ntMovieID="2" vcMovieName="OBLIVION" />

<tblMovie ntMovieID="3" vcMovieName="IRON MAN 3" />

<tblMovie ntMovieID="4" vcMovieName="EVIL DEAD" />

<tblMovie ntMovieID="5" vcMovieName="MAN OF STEEL" />

</Movies>

'

SELECT @xlMovie.query('sql:variable("@vcDbName")')

We may get error message like:

Msg 9501, Level 16, State 2, Line 12

XQuery: Unable to resolve sql:variable('@vcDbName'). The variable must be declared as a scalar TSQL variable.

Cause: The local variable which you are using in the XQuery is invalid. In this example there is any not local variable @vcDbName

Solution:  We must have to first declare the local variable before using in XQUERY. For example:

DECLARE @vcDbName AS SYSNAME = 'ExactHelp'

DECLARE @xlMovie AS XML =

'

<Movies>

<tblMovie ntMovieID="1" vcMovieName="PAIN &amp; GAIN" />

<tblMovie ntMovieID="2" vcMovieName="OBLIVION" />

<tblMovie ntMovieID="3" vcMovieName="IRON MAN 3" />

<tblMovie ntMovieID="4" vcMovieName="EVIL DEAD" />

<tblMovie ntMovieID="5" vcMovieName="MAN OF STEEL" />

</Movies>

'

SELECT @xlMovie.query('sql:variable("@vcDbName")')

Same output:

ExactHelp

XQuery: '' referenced by sql:variable() is not a valid system function name.

If we will execute following XQUERY script in the sql server:

DECLARE @xlMovie AS XML =

'

<Movies>

<tblMovie ntMovieID="1" vcMovieName="PAIN &amp; GAIN" />

<tblMovie ntMovieID="2" vcMovieName="OBLIVION" />

<tblMovie ntMovieID="3" vcMovieName="IRON MAN 3" />

<tblMovie ntMovieID="4" vcMovieName="EVIL DEAD" />

<tblMovie ntMovieID="5" vcMovieName="MAN OF STEEL" />

</Movies>

'

SELECT @xlMovie.query('sql:variable("@@SERVERNAME()")')

We may get error message like:

Msg 9520, Level 16, State 1, Line 12

XQuery: '@@SERVERNAME()' referenced by sql:variable() is not a valid system function name.

Cause: The system function which you are using in the XQuery is invalid. In this example, sql server doesn't support any such system function like: @@SERVERNAME()

Solution:  Use valid system function. For example:

DECLARE @xlMovie AS XML =

'

<Movies>

<tblMovie ntMovieID="1" vcMovieName="PAIN &amp; GAIN" />

<tblMovie ntMovieID="2" vcMovieName="OBLIVION" />

<tblMovie ntMovieID="3" vcMovieName="IRON MAN 3" />

<tblMovie ntMovieID="4" vcMovieName="EVIL DEAD" />

<tblMovie ntMovieID="5" vcMovieName="MAN OF STEEL" />

</Movies>

'

SELECT @xlMovie.query('sql:variable("@@SERVERNAME")')

Same output:

RITESH-PC\SQLSERVER

How to create sql function in sql server management studio (SSMS)

In sql server it is very easy to create function. We can create a function in sql server management studio (SSMS) in following way:

Creating scalar-valued function using server management studio (SSMS):

Step 1: Expand your database name node in SSMS. Then expand Programmability node as shown in the following screenshot:

Step 2: Right click on Functions node and click on New -> Scalar-valued Function...

Step 3: It will generate following sql script in new query page:

-- ================================================

-- Template generated from Template Explorer using:

-- Create Scalar Function (New Menu).SQL

--

-- Use the Specify Values for Template Parameters

-- command (Ctrl-Shift-M) to fill in the parameter

-- values below.

--

-- This block of comments will not be included in

-- the definition of the function.

-- ================================================

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:        <Author,,Name>

-- Create date: <Create Date, ,>

-- Description:   <Description, ,>

-- =============================================

CREATE FUNCTION <Scalar_Function_Name, sysname, FunctionName>

(

      -- Add the parameters for the function here

      <@Param1, sysname, @p1> <Data_Type_For_Param1, , int>

)

RETURNS <Function_Data_Type, ,int>

AS

BEGIN

      -- Declare the return variable here

      DECLARE <@ResultVar, sysname, @Result> <Function_Data_Type, ,int>

      -- Add the T-SQL statements to compute the return value here

      SELECT <@ResultVar, sysname, @Result> = <@Param1, sysname, @p1>

      -- Return the result of the function

      RETURN <@ResultVar, sysname, @Result>

END

GO

Step 4: Now edit above script like stored procedure name, parameters, procedure body etc according to your requirement. For example:

-- =============================================

-- Author:        exacthelp.com

-- Create date: 04/27/2014

-- Description:   My first scalar valued function

-- =============================================

CREATE FUNCTION My_First_Function

(

      -- Add the parameters for the function here

      @ntParameter AS INTEGER

)

RETURNS INTEGER

AS

BEGIN

      -- Declare the return variable here

      DECLARE @Value AS INTEGER

      -- Add the T-SQL statements to compute the return value here

      SELECT @Value = @ntParameter * 10

      -- Return the result of the function

      RETURN @Value

END

GO

Step 5: To create My_First_Function function press F5 button of your keyboard or click on Execute button in SSMS.

Step 6: After creating it will be stored under Programmability ->Functions ->Scalar-Valued-Functions node. To re-open or edit it expand programmability node and right click on your function name and choose modify option.

You will get the script of your stored procedure. In this example it will be script of My_First_Function. This will be something like this:

USE [ExactHelp]

GO

/****** Object:  UserDefinedFunction [dbo].[My_First_Function]    Script Date: 04/27/2014 00:42:22 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:        exacthelp.com

-- Create date: 04/27/2014

-- Description:   My first scalar valued function

-- =============================================

ALTER FUNCTION [dbo].[My_First_Function]

(

      -- Add the parameters for the function here

      @ntParameter AS INTEGER

)

RETURNS INTEGER

AS

BEGIN

      -- Declare the return variable here

      DECLARE @Value AS INTEGER

      -- Add the T-SQL statements to compute the return value here

      SELECT @Value = @ntParameter * 10

      -- Return the result of the function

      RETURN @Value

END

Step 7: You can modify you script and to save it, press F5 button.