Advertisements

Archive

Archive for the ‘SQL’ Category

Sql Server useful commands

Coming…

Advertisements
Categories: SQL, SQL Server Tags: ,

Logging by Triggers in SQL Server

We can alternatively use SQL Server feature CDC (Change Data Capture), but here I am providing one more way of doing it (custom solution).

Db Objects: (two tables and two triggers)

  1. TargetTableName: Table whose data we want to log
  2. LogTableName: Table where data will be logged. This table structure will be same as TargetTableName, and additionally this table will have three extra columns namely
    • [log_action_type]
    • [log_action_date]
    • [log_action_user]
    • [log_action_hostname]
  3. tl_TargetTableName: Trigger to log DML changes (Update and Delete only)
  4. tu_LogTableName: Trigger to avoid Delete or Update operations operations on logging table
 
use dbname
go

alter table LogTableName add [log_action_type] 	[char](1) not null;
alter table LogTableName add [log_action_date] 	[datetime] not null default getdate();
alter table LogTableName add [log_action_user] 	[varchar](50) null;
alter table LogTableName add [log_action_hostname] 	[varchar](50) default host_name();

go

IF OBJECT_ID ('dbo.tu_LogTableName ','TR') IS NOT NULL
   DROP TRIGGER dbo.tu_LogTableName;
GO
-- =============================================
-- Author:		Umesh Patil	
-- Create date: 2014/08/26
-- Description:	Restirct Delete or Update operations operations on logging table
-- =============================================
CREATE TRIGGER dbo.tu_LogTableName  
   ON  dbo.LogTableName 
   FOR DELETE,UPDATE
AS 
BEGIN
	RAISERROR ('Can not perform Delete or Update operations on UserTableName table, either disable/delete the trigger',16,1);
	ROLLBACK TRANSACTION;
	RETURN;
END
GO

use dbname
go

IF OBJECT_ID ('dbo.tl_TargetTableName','TR') IS NOT NULL
   DROP TRIGGER dbo.tl_TargetTableName;
GO
-- =============================================
-- Author:		Umesh Patil	
-- Create date: 2014/08/26
-- Description:	Log DML changes (Update and Delete only)
-- =============================================
CREATE TRIGGER dbo.tl_TargetTableName 
   ON  dbo.TargetTableName
   FOR DELETE,UPDATE
AS 
BEGIN
	SET NOCOUNT ON;
	DECLARE @ActionType char(1)

	IF EXISTS (SELECT 1 FROM deleted)
		BEGIN
			IF EXISTS(SELECT 1 FROM inserted)
				SET @ActionType='U'
			ELSE
				SET @ActionType='D'
		END

	IF  @ActionType='U' OR  @ActionType='D'
		INSERT INTO [dbo].[TargetTableName]
           	([PrimaryKey_Column]
		  --include all columns
		,[log_action_type]
		,[log_action_date]
		,[log_action_user]
		  ,[log_action_hostname])
		select		
			[PrimaryKey_Column]
			--include all columns
			,@ActionType as [log_action_type]
			,getdate() as [log_action_date]
			,suser_sname() as [log_action_user]
			,host_name() as [log_action_hostname]
		from deleted
END
GO
Categories: SQL, SQL Server, T-SQL Tags: , ,

Find Non-ASCII character in SQL Server

Here is a simple command to find character.

SELECT
	NOTES
	,patindex('%[' + CHAR(127)+ '-' +CHAR(255)+']%' COLLATE Latin1_General_BIN,NOTES) as [Position]
	,substring(NOTES,patindex('%[' + CHAR(127)+ '-' +CHAR(255)+']%' COLLATE Latin1_General_BIN,NOTES),1) as [InvalidCharacter]
	,ascii(substring(NOTES,patindex('%[' + CHAR(127)+ '-' +CHAR(255)+']%' COLLATE Latin1_General_BIN,NOTES),1)) as [ASCIICode]
FROM
      tablename (NOLOCK)
WHERE
      Notes LIKE '%[' + CHAR(127)+ '-' +CHAR(255)+']%'  COLLATE Latin1_General_100_BIN2

Hope it helps.

Regards,
Umesh

Categories: Data Quality, SQL, T-SQL Tags: ,

Count number of rows in stored procedure

October 12, 2012 Leave a comment

              Many a times in our daily life of a SQL development/DBA profile, we have to spend our day across stored procedures, and we need to find out how many rows are there in any stored procedures, especially when we have to do some analysis or while  we have to review any stored procedure for evaluation or estimation, the very first thing which comes in everyone’s thought is, how big is the stored procedures the following are the simple ways to get the approximate number of rows in stored procedure. Read more…

SELECT * Vs SELECT Column Name in SQL Server Query

September 2, 2012 4 comments

People always keep saying do not use SELECT * exercise SELECT <column names> instead. But no one put more light on this and this becoming myth in SQL Server and other database management systems. Here are couple of reasons why should we avoid SELECT * and go with SELECT <column names>

Read more…

SET QUOTED_IDENTIFIER ON/OFF and SET ANSI_NULLS ON/OFF

June 9, 2012 Leave a comment

Most of us use those statements while creating stored procedures and functions. most of us don’t know what is the significance of those statements and how they are useful.

SET QUOTED_IDENTIFIER ON/OFF

Read more…

%d bloggers like this: