SQL Server: Trigger to track or Log DML queries executed on a specific table

There is a master table in my production server. This table is getting updated from various users from various sources. As this table is very huge and the changes/updates/inserts are in bulk, I cannot maintain old data and updated data track. So I needed a trigger to track DML queries executed on the table by particular user in particular date.

(1) First create a table with the schema. You can customise this table as per your needs.

CREATE TABLE [test]
(
    [ID] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
    [DeviceName] [varchar](100) NULL,
    [UserName] [varchar](100) NULL,
    [objectName] [varchar](100) NULL,
    [EventType] [varchar](400) NULL,
    [EventTime] [datetime] NULL,
    [CommandText] [varchar](max) NULL,
)

(2) Create the below Trigger on the table which you want to track query logs.

CREATE TRIGGER [dbo].[TriItemsDMLlog]
ON [dbo].[MasterTable]
AFTER UPDATE,DELETE,INSERT
AS
BEGIN
SET NOCOUNT ON

DECLARE @TABLENAME VARCHAR(20)
SET @TABLENAME='MasterTable'

DECLARE @inputbuffer TABLE
(EventType nvarchar(30),
Parameters int,
EventInfo nvarchar(4000))

INSERT INTO @inputbuffer EXEC('DBCC inputbuffer('+@@Spid+') with no_infomsgs')

DECLARE @INS INT
DECLARE @DEL INT

SELECT @INS=COUNT(*) FROM INSERTED
SELECT @DEL=COUNT(*) FROM DELETED

INSERT INTO MasterTableDMLQueryLog
(
UserName,
DeviceName,
objectname,
EventType,
EventTime,
CommandText
) SELECT SUSER_NAME(),HOST_NAME(),@TABLENAME,
CASE WHEN ISNULL(@INS,0)>0 AND ISNULL(@DEL,0)>0 THEN 'UPDATE'
when ISNULL(@INS,0)>0 AND ISNULL(@DEL,0)=0 THEN 'INSERT'
ELSE 'DELETE'END,GETDATE(),EventInfo FROM @inputbuffer;

SET NOCOUNT OFF

END

SQL Server: Pass Multiple values as parameter dynamically

Here is an example to show how multiple values can be passed as parameter which can be used in stored procedure.

CREATE PROC [dbo].[DynamicSQL_PassMultiVal](@EmpName varchar(500))
AS
BEGIN

DECLARE @SQLStatement varchar(max)
SET @EmpName = Replace(@EmpName,',',''',''')
SET @SQLStatement='select * from Employee where EmployeeName in (' + QuoteName(@EmpName) + ')'

EXEC (@SQLStatement)

END

Without using QUOTENAME() we can write the query as

ALTER PROC [dbo].[DynamicSQL_PassMultiVal](@EmpName varchar(500))
AS
BEGIN

DECLARE @SQLStatement varchar(max)
SET @EmpName = Replace(@EmpName,',',''',''')
SET @SQLStatement='select * from Employee where EmployeeName in (' + '''' + @EmpName + ''')'

EXEC (@SQLStatement)

END

Usage

[DynamicSQL_PassMultiVal] 'Bheem,Krishna'

SQL Server: Pass single value parameter dynamically

This is just an example to pass a single parameter dynamically.

CREATE PROC [dbo].[DynamicSQL_PassSingleVal](@EmpName varchar(500))
AS
BEGIN

DECLARE @SQLStatement varchar(max)
SET @SQLStatement ='select * from Employee where EmployeeName = ' + '''' + @EmpName + ''''

EXEC (@SQLStatement)

END

Usage

[DynamicSQL_PassSingleVal] 'Bheem'

Click here to see how to Pass Multiple values as parameter dynamically

SQL Server: Open Recordset in SQL Server from MS Access

We can open tables from MA Access, Excel and other MS Office applications. Here we shall see how records can be retrieved from MS Access
Supposing we have test.mdb file on your desktop(C:\Users\mine\Desktop\test.mdb) and has a table by name Table1 containing 4 records in it.

SELECT * FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="C:\Users\mine\Desktop\test.mdb"')...Table1;

You may obtain this error if you are running first time.

Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

You need to execute below two queries and execute above query. It works without any issue.

sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

If you need to open recordset from access 2007 and above versions you have to use this query

SELECT *  FROM OPENDATASOURCE(
'Microsoft.ACE.OLEDB.12.0',
'Data Source="C:\Users\mine\Desktop\test.accdb"')...Table1;

SQL Server: SELECT syntax

• SELECT clause specifies the columns to be returned by the SELECT statement. 
• SELECT statement returns data from a database. 

Now confusion arises on the difference between SELECT clause and SELECT statement. To know the difference between SQL Clause and Statement click on the above link.

SQL Server: Insert/Update/Delete in VIEW table

When I was conducting interviews for SQL candidates having 1-2 years of experience, most of them had misunderstanding about SQL VIEW tables and answered me as "VIEW table is created from BASE tables and thus VIEW is a virtual table where we cant do any edits on VIEW tables". When I say we can do UPDATE/INSERT/DELETE operations on VIEW tables, they wonder and not ready to accept it. This is incorrect.

"we can perform DML operations like UPDATE/INSERT/DELETE on VIEW tables."

Note

UPDATE, DELETE, INSERTS cannot be performed when the VIEW involves constraints in it

SQL Server: Transpose rows to columns without PIVOT

In this section we will see how to transpose rows to columns without PIVOT concept. Rather than explanation I will show with an example.
Execute below queries to create a sample table

CREATE TABLE package
(
itemnumber INT,
qty INT,
uom VARCHAR(10)
)

INSERT INTO package (itemnumber,qty,uom) VALUES (1111,1,'EA')
INSERT INTO package (itemnumber,qty,uom) VALUES (1111,10,'BX')
INSERT INTO package (itemnumber,qty,uom) VALUES (1111,100,'CA')
INSERT INTO package (itemnumber,qty,uom) VALUES (2222,1,'EA')
INSERT INTO package (itemnumber,qty,uom) VALUES (2222,15,'BX')
INSERT INTO package (itemnumber,qty,uom) VALUES (2222,30,'CA')
INSERT INTO package (itemnumber,qty,uom) VALUES (2222,60,'CARTON')
INSERT INTO package (itemnumber,qty,uom) VALUES (3333,1,'EA')
INSERT INTO package (itemnumber,qty,uom) VALUES (3333,12,'DZ')

SELECT * FROM package

itemnumber	qty	uom
1111	1	EA
1111	10	BX
1111	100	CA
2222	1	EA
2222	15	BX
2222	30	CA
2222	60	CARTON
3333	1	EA
3333	12	DZ

The below query will transpose rows to columns.

SELECT itemnumber,
MAX(CASE WHEN serial = 1 THEN qty END) qty1,
MAX(CASE WHEN serial = 1 THEN uom END) uom1,
MAX(CASE WHEN serial = 2 THEN qty END) qty2,
MAX(CASE WHEN serial = 2 THEN uom END) uom2,
MAX(CASE WHEN serial = 3 THEN qty END) qty3,
MAX(CASE WHEN serial = 3 THEN uom END) uom3,
MAX(CASE WHEN serial = 4 THEN qty END) qty4,
MAX(CASE WHEN serial = 4 THEN uom END) uom4,
MAX(CASE WHEN serial = 5 THEN qty END) qty5,
MAX(CASE WHEN serial = 5 THEN uom END) uom5
FROM
(
SELECT itemnumber,qty,uom,
ROW_NUMBER() OVER(PARTITION BY itemnumber
ORDER BY itemnumber,qty) serial
FROM package
) d
GROUP BY itemnumber

itemnumber	qty1	uom1	qty2	uom2	qty3	uom3	qty4	uom4	qty5	uom5
1111	1	EA	10	BX	100	CA	NULL	NULL	NULL	NULL
2222	1	EA	15	BX	30	CA	60	CARTON	NULL	NULL
3333	1	EA	12	DZ	NULL	NULL	NULL	NULL	NULL	NULL

SQL Server: Populate all installed instances of MS SQL SERVER

I have Installed several versions of SQL server with different instance names.I needed all the instance names of MS SQL Server installed in My System. I found below script to achieve this

DECLARE @GetAllInstances TABLE
( Value nvarchar(100),
InstanceNames nvarchar(100),
Data nvarchar(100))

Insert into @GetAllInstances
EXECUTE xp_regread
@rootkey = 'HKEY_LOCAL_MACHINE',
@key = 'SOFTWARE\Microsoft\Microsoft SQL Server',
@value_name = 'InstalledInstances'

Select InstanceNames from @GetAllInstances

SQL Server: Trim all columns of a table at a time

I had a situation of trimming all columns of a table. My table had 80 columns. So I had to specify each column in the query to trim which was very hectic and irritating.

UPDATE mytable SET col1 = LTRIM(RTRIM(col1)), col2 = LTRIM(RTRIM(col1))... till col80

I found below script as the solution to avoid this.

USE MyDatabase

DECLARE @SQL VARCHAR(MAX)
DECLARE @TableName NVARCHAR(128)
SET @TableName = 'mytable'

SELECT @SQL = COALESCE(@SQL + ',[', '[') +
COLUMN_NAME + ']=LTRIM(RTRIM([' + COLUMN_NAME + ']))'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @SchemaName AND TABLE_NAME = @TableName AND DATA_TYPE Like '%char%'

SET @SQL = 'UPDATE [' + @TableName + '] SET ' + @SQL
EXEC (@SQL)

When you need to pass table name whcih has to perform LTRIM and RTRIM for all columns just create a stored procedure

CREATE PROCEDURE TrimAllColumnsOfTable @TableName Varchar(100)
AS
BEGIN

DECLARE @SQL VARCHAR(MAX)
SELECT @SQL = COALESCE(@SQL + ',[', '[') +
              COLUMN_NAME + ']=LTRIM(RTRIM([' + COLUMN_NAME + ']))'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @SchemaName AND TABLE_NAME = @TableName
    AND DATA_TYPE Like '%char%'

SET @SQL = 'UPDATE [' + @TableName + '] SET ' + @SQL

EXEC (@SQL)

END

If you have different schema names apart from default dbo schema like [abc].[TableName] and [xyz.pqr].TableName etc. then you must use below SP

CREATE PROCEDURE [dbo].[TrimAllColumnsOfTable] @SchemaName Varchar(100),@TableName Varchar(100)
AS
BEGIN

DECLARE @SQL VARCHAR(MAX)
SELECT @SQL = COALESCE(@SQL + ',[', '[') +
              COLUMN_NAME + ']=LTRIM(RTRIM([' + COLUMN_NAME + ']))'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @SchemaName AND TABLE_NAME = @TableName
    AND DATA_TYPE Like '%char%'

SET @SQL = 'UPDATE [' + @SchemaName + '].[' + @TableName + '] SET ' + @SQL

EXEC (@SQL)

END

We can use this script to trim all char,nchar, varchar and nvarchar columns of all tables across all databases in a server

SELECT 'UPDATE [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] ' + 'SET [' + COLUMN_NAME + '] = LTRIM(RTRIM([' + COLUMN_NAME + '])) ' + 'WHERE [' + COLUMN_NAME + '] <> LTRIM(RTRIM([' + COLUMN_NAME + ']))' + CHAR(13) + CHAR(10) + 'GO' FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE IN ('varchar', 'nvarchar') ORDER BY TABLE_NAME, COLUMN_NAME

Search a function

To populate all User-Defined functions in the current database we can use the query

SELECT *
FROM sys.objects
WHERE type IN ('FN', 'IF', 'TF')

To search specific User-Defined function by name we can use

SELECT *
FROM sys.objects
WHERE type IN ('FN', 'IF', 'TF') and name LIKE '%zero%'

SQL Server: Remove Duplicate records using CTE

Execute below scripts to create a new table by name Sampletable.

CREATE TABLE Sampletable(Emp_Name VARCHAR(50),Emp_ShortName VARCHAR(3),Emp_City VARCHAR(50))

Insert few records

INSERT INTO Sampletable(Emp_Name,Emp_ShortName,Emp_City) VALUES('Shivakumar', 'SKR','Bangalore')
INSERT INTO Sampletable(Emp_Name,Emp_ShortName,Emp_City) VALUES('KumarKrishnan', 'KKN','Chennai')
INSERT INTO Sampletable(Emp_Name,Emp_ShortName,Emp_City) VALUES('Sunil', 'SNL','Shimoga')
INSERT INTO Sampletable(Emp_Name,Emp_ShortName,Emp_City) VALUES('Job John', 'JJN','Trivendrum')
INSERT INTO Sampletable(Emp_Name,Emp_ShortName,Emp_City) VALUES('Lokesh', 'LKS','Bangalore')
INSERT INTO Sampletable(Emp_Name,Emp_ShortName,Emp_City) VALUES('Vinod Raj', 'VRJ','Belgam')
INSERT INTO Sampletable(Emp_Name,Emp_ShortName,Emp_City) VALUES('Shivakumar', 'SKR','Bangalore')
INSERT INTO Sampletable(Emp_Name,Emp_ShortName,Emp_City) VALUES('Vinod Raj', 'VRJ','Belgam')

Select to check the records of your Sampletable

select * from Sampletable

Your results will be as shown as below.



Execute the below CTE script to check what exactly the query does.

With CTE
As
(
SELECT Emp_Name, Emp_ShortName, Emp_City,ROW_NUMBER() OVER(PARTITION BY Emp_Name, Emp_ShortName, Emp_City ORDER BY Emp_Name) AS DuplicateCount
FROM Sampletable
)
SELECT * FROM CTE WHERE DuplicateCount>1

Results will be like



Once it is confirmed those are the duplicated records, go ahead and run below script to delete duplicated records.

With CTE
As
(
SELECT Emp_Name, Emp_ShortName, Emp_City,ROW_NUMBER() OVER(PARTITION BY Emp_Name, Emp_ShortName, Emp_City ORDER BY Emp_Name) AS DuplicateCount
FROM Sampletable
)
DELETE FROM CTE WHERE DuplicateCount>1

SQL Server: CTE - Common Table Expressions

A CTE is a temporary result set derived from a single SELECT, INSERT, UPDATE, DELETE or CREATE VIEW statement. The scope of the result set is with in the execution time. CTE is similar to derived tables but not physical SQL objects.

Syntax:

With CTE_Name(ColumnName1,ColumnName2,...) As
(
CTE query definition
)
SELECT ColumnList from CTE_Name

Example:

 

Rename Column and rename sql server objects such as Tables, SP's

Here is syntax of the script for renaming any column

SP_RENAME 'TableName.[OldColumnName]' , '[NewColumnName]', 'COLUMN'

Here is syntax of the script for renaming any sql server objects such as Tables, SPs, Functions etc.

SP_RENAME '[OldTableName]' , '[NewTableName]'

Once any of the above scripts are executed the below caution statement will come.
Caution: Changing any part of an object name could break scripts and stored procedures.

It indicates, the rename of the object or object column has to be done in associated objects like Stored Procedures, Functions etc

Note:
If you have any constraints in the column like Primary Key constraint, Not Null constraint or indexing, rename will be performed forcefully. The constraints will remain as it is.
-------------------------------------------------------------------------------------------------------------
Other properties of SP_RENAME
-------------------------------------------------------------------------------------------------------------
Scenario 1: When I tried renaming a column in a table by name Table1 as below, the column Emp Nm got renamed to [EmployeeName] i.e. with square braces.

SP_RENAME 'Table1.[Emp Nm]' , Table1.[EmployeeName]', 'COLUMN';

So I wanted to eliminate those braces then I have to write below script to do so.

SP_RENAME 'Table1.[[EmployeeName]]]' , 'EmployeeName', 'COLUMN'

SQL Server: Search string / text in all stored procedures of a database

Here we are searching text named date_of_inv which is a column Name as well.

SELECT * FROM
SYS.PROCEDURES
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%DATE_OF_INV%'

Here we are searching string LocID which is just a string and not the column name.

SELECT * FROM
SYS.PROCEDURES
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%LOCID%'

The above two queries is specific to a database and will not search all the databases.