Compare Sql Server Table Constraints

Hello friends, after a long period of time I’m back into my blog. As due to continuous projects didn’t get a long stretch to sit with my blog. 

In a BI project I faced a lot challenges for migrating some huge databases which ended up with lot of dependencies on sql server schema and object comparison tools(Redgate Sql Data compare,Sql compare,Xsql) to make my SSIS to execute smoothly. Now I’m in a workshop for synchronizing two databases. In the middle of that I’m sharing some useful sql queries which will be useful for happy coding.

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_GET_ALL_CONSTRAINTS]') 
and objectproperty(id, N'isprocedure') = 1)
DROP PROCEDURE [dbo].[SP_GET_ALL_CONSTRAINTS]
go
CREATE PROC SP_GET_ALL_CONSTRAINTS
(
	@sourceServer nvarchar(1000),
	@targetServer nvarchar(1000)
)
--@targetServer db to be migrated(old db) eg 12.3
--@sourceServer is the new database having all new schemas scripts   eg:12.4
as
SELECT	
	'NEW'				AS CHANGE,
	DatabaseName		AS DATABASENAME,
	TABLE_NAME			AS TABLENAME,
	COLUMN_NAME			AS COLUMNNAME,
	CONSTRAINT_NAME		AS CONSTRAINTNAME,
	'N-A'				AS NEW_CONSTRAINTNAME,
	CONSTRAINT_TYPE		AS CONSTRAINTTYPE, 
	ORDINAL_POSITION	AS ORDINALPOSITION,
	REF_PK_KEY			AS REFERENCEPKKEY,
	ParentTable			AS ParentTable,
	ParentField			AS ParentField,
	TYPE_CLUSTER		AS TYPECLUSTER,
	CASE CONSTRAINT_TYPE WHEN 'PRIMARY KEY'
			THEN 'ALTER TABLE dbo.' + TABLE_NAME + ' ADD CONSTRAINT' + ' ' + CONSTRAINT_NAME + ' PRIMARY KEY (' + COLUMN_NAME + ')' 
			WHEN 'FOREIGN KEY' THEN 
						'ALTER TABLE dbo.' + TABLE_NAME + ' ADD CONSTRAINT '+ CONSTRAINT_NAME + ' ' + 
						CONSTRAINT_TYPE +  '(' + REF_PK_KEY + ') REFERENCES ' + ParentTable + '(' + ParentField + ')' 
			ELSE 'ALTER TABLE dbo.' + TABLE_NAME + ' ADD CONSTRAINT ' + CONSTRAINT_NAME + ' UNIQUE (' + COLUMN_NAME  + ')'
			END AS Script
FROM
	tbl_ALL_CONSTRAINTS
WHERE
	DatabASeName = @targetServer
AND
	CONSTRAINT_NAME NOT IN
	(
		SELECT 	CONSTRAINT_NAME
		FROM 	tbl_ALL_CONSTRAINTS
		WHERE	DatabaseName = @sourceServer
		)
UNION ALL
-- select the changed columns
SELECT	
	'MODIFIED'			AS CHANGE,
	v1.DatabaseName		AS DATABASENAME,
	v1.TABLE_NAME		AS TABLENAME,
	v1.COLUMN_NAME		AS COLUMNNAME,
	v1.CONSTRAINT_NAME	AS CONSTRAINTNAME,
	v2.CONSTRAINT_NAME	AS NEW_CONSTRAINTNAME,
	v1.CONSTRAINT_TYPE	AS CONSTRAINTTYPE, 
	v1.ORDINAL_POSITION AS ORDINALPOSITION,
	v1.REF_PK_KEY		AS REFERENCEPKKEY,
	v1.ParentTable		AS ParentTable,
	v1.ParentField		AS ParentField,
	v1.TYPE_CLUSTER		AS TYPECLUSTER,
	CASE v1.CONSTRAINT_TYPE WHEN 'PRIMARY KEY'
			THEN 'ALTER TABLE dbo.' + v1.TABLE_NAME + ' DROP CONSTRAINT  ' 
					+ v1.CONSTRAINT_NAME  + ' ALTER TABLE dbo.' + v1.TABLE_NAME 
					+ ' ADD CONSTRAINT' + ' ' + v2.CONSTRAINT_NAME 
					+ ' PRIMARY KEY (' + v1.COLUMN_NAME + ')' 
			WHEN 'FOREIGN KEY' THEN 
						'ALTER TABLE dbo.' + v1.TABLE_NAME + ' DROP CONSTRAINT ' + v1.CONSTRAINT_NAME  +  ' ALTER TABLE dbo.' + v1.TABLE_NAME 
						+ ' ADD CONSTRAINT '+ v2.CONSTRAINT_NAME + ' ' + v1.CONSTRAINT_TYPE  + ' (' + v1.COLUMN_NAME + ') REFERENCES ' 
						+ v1.ParentTable + '(' + v1.ParentField + ')'
			ELSE 'ALTER TABLE dbo.' + v1.TABLE_NAME + ' DROP CONSTRAINT  ' 
					+ v1.CONSTRAINT_NAME + ' ALTER TABLE dbo.' + v1.TABLE_NAME + ' ADD CONSTRAINT ' + v1.CONSTRAINT_NAME + ' UNIQUE (' + v1.COLUMN_NAME  + ')'
			END AS Script
FROM 	
	tbl_ALL_CONSTRAINTS v1
JOIN
	tbl_ALL_CONSTRAINTS v2
ON
	v1.TABLE_NAME  = v2.TABLE_NAME
AND 
	v1.COLUMN_NAME=v2.COLUMN_NAME
AND
	v1.CONSTRAINT_TYPE=v2.CONSTRAINT_TYPE
AND	
	v1.DatabaseName = @sourceServer
AND	
	v2.DatabaseName = @targetServer
AND 
	v1.CONSTRAINT_NAME<>v2.CONSTRAINT_NAME
GO
SET ANSI_NULLS ON 
GO
	

The below script will populate all the sql server metadata information

----QUERY to Find out All the Constraints in Detailed
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_CreateAllConstraintsInTable]') and objectproperty(id, N'isprocedure') = 1)
drop procedure [dbo].[sp_CreateAllConstraintsInTable]
go
CREATE PROCEDURE sp_CreateAllConstraintsInTable 
@sourceServer  nvarchar(30), --Database to be compared (New Database)  
@targetServer  nvarchar(30)  --Database to be compared with(Old Database)  
as 
declare @sql varchar(max)

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbl_ALL_CONSTRAINTS]') and objectproperty(id, N'istable') = 1)
drop table [dbo].[tbl_ALL_CONSTRAINTS]

select @sql=

'	SELECT 
	u.TABLESCHEMA,
	u.DatabaseName,
	u.TABLE_NAME,
	u.COLUMN_NAME,
	u.CONSTRAINT_NAME,
	u.CONSTRAINT_TYPE,
	u.ORDINAL_POSITION,
	u.REF_PK_KEY,
	u.ParentTable,
	u.ParentField,
	u.TYPE_CLUSTER into tbl_ALL_CONSTRAINTS
	FROM 
	(
		(SELECT 
			TAB_CON.CONSTRAINT_SCHEMA AS TABLESCHEMA,
			TAB_CON.TABLE_CATALOG as DatabaseName,
			TAB_CON.TABLE_NAME AS TABLE_NAME,
			KEY_COL.COLUMN_NAME AS COLUMN_NAME, 
			TAB_CON.CONSTRAINT_NAME AS CONSTRAINT_NAME,
			TAB_CON.CONSTRAINT_TYPE AS CONSTRAINT_TYPE,
			KEY_COL.ORDINAL_POSITION AS ORDINAL_POSITION,
			RC.UNIQUE_CONSTRAINT_NAME  AS REF_PK_KEY,
			ccu.table_name AS ParentTable, 
			ccu.column_name as ParentField, 
			ISNULL(CLUST_TYPE.TYPE_DESC,'+ '''NOT APPLICABLE'''+') AS TYPE_CLUSTER 
		FROM '+
			@sourceServer+'.INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TAB_CON 
		INNER JOIN 
			 '+@sourceServer+'.INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KEY_COL  
		ON 
			TAB_CON.TABLE_SCHEMA =  KEY_COL.TABLE_SCHEMA  
		AND 
			TAB_CON.TABLE_NAME = KEY_COL.TABLE_NAME   
		AND 
			TAB_CON.CONSTRAINT_NAME = KEY_COL.CONSTRAINT_NAME  
		LEFT JOIN 
			 '+@sourceServer+'.INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC  
		ON 
			RC.CONSTRAINT_NAME =  TAB_CON.CONSTRAINT_NAME
		AND 
			TAB_CON.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA  
		LEFT JOIN  '+@sourceServer+'.INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
		ON 
			RC.unique_constraint_schema = ccu.constraint_schema 
		AND 
			RC.unique_constraint_catalog = ccu.constraint_catalog 
		AND 
			RC.unique_constraint_name = ccu.constraint_name  
		LEFT JOIN  
			(SELECT IDX.OBJECT_ID,IDX.NAME,IDX.TYPE_DESC,SCHEMA_ID FROM SYS.INDEXES IDX JOIN SYS.OBJECTS OBJ ON IDX.[OBJECT_ID] = OBJ.[OBJECT_ID] ) 
		AS 
			CLUST_TYPE ON OBJECT_NAME(CLUST_TYPE.OBJECT_ID) = TAB_CON.TABLE_NAME 
		AND 
			CLUST_TYPE.NAME = TAB_CON.CONSTRAINT_NAME  
		AND 
			SCHEMA_NAME( CLUST_TYPE.[SCHEMA_ID]) = TAB_CON.CONSTRAINT_SCHEMA  
			)
			union all
		(SELECT 
			TAB_CON.CONSTRAINT_SCHEMA AS TABLESCHEMA,
			TAB_CON.TABLE_CATALOG as DatabaseName,
			TAB_CON.TABLE_NAME AS TABLE_NAME,
			KEY_COL.COLUMN_NAME AS COLUMN_NAME, 
			TAB_CON.CONSTRAINT_NAME AS CONSTRAINT_NAME,
			TAB_CON.CONSTRAINT_TYPE AS CONSTRAINT_TYPE,
			KEY_COL.ORDINAL_POSITION AS ORDINAL_POSITION,
			RC.UNIQUE_CONSTRAINT_NAME  AS REF_PK_KEY,
			ccu.table_name AS ParentTable, 
			ccu.column_name as ParentField, 
			ISNULL(CLUST_TYPE.TYPE_DESC,'+ '''NOT APPLICABLE'''+') AS TYPE_CLUSTER 
		FROM  
			 '+@targetServer+'.INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TAB_CON 
		INNER JOIN 
			'+@targetServer+'.INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KEY_COL  
		ON 
			TAB_CON.TABLE_SCHEMA =  KEY_COL.TABLE_SCHEMA  
		AND 
			TAB_CON.TABLE_NAME = KEY_COL.TABLE_NAME   
		AND 
			TAB_CON.CONSTRAINT_NAME = KEY_COL.CONSTRAINT_NAME  
		LEFT JOIN 
			'+@targetServer+'.INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC  
		ON 
			RC.CONSTRAINT_NAME =  TAB_CON.CONSTRAINT_NAME
		AND 
			TAB_CON.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA  
		LEFT JOIN '+@targetServer+'.INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
		ON 
			RC.unique_constraint_schema = ccu.constraint_schema 
		AND 
			RC.unique_constraint_catalog = ccu.constraint_catalog 
		AND 
			RC.unique_constraint_name = ccu.constraint_name  
		LEFT JOIN  
			(SELECT IDX.OBJECT_ID,IDX.NAME,IDX.TYPE_DESC,SCHEMA_ID FROM SYS.INDEXES IDX JOIN SYS.OBJECTS OBJ ON IDX.[OBJECT_ID] = OBJ.[OBJECT_ID] ) 
		AS 
			CLUST_TYPE ON OBJECT_NAME(CLUST_TYPE.OBJECT_ID) = TAB_CON.TABLE_NAME 
		AND 
			CLUST_TYPE.NAME = TAB_CON.CONSTRAINT_NAME  
		AND 
			SCHEMA_NAME( CLUST_TYPE.[SCHEMA_ID]) = TAB_CON.CONSTRAINT_SCHEMA  
		)
	) u
	ORDER BY 
		u.TABLESCHEMA, u.CONSTRAINT_TYPE DESC, 
		u.TABLE_NAME,u.ORDINAL_POSITION'
exec (@sql)
go

Get this function also from here

Restoring backup of Partitioned Database in sql server 2008

If we need to restore a database having some 'n' number of partition its very tedious to give all the partitioned filegroup to restore into a particular location in the database server.The below example we demonstrate to perform the task.Run the script in the master database.

IF Exists (SELECT '1' from sysobjects where name = 'RESTOREBKP_2008' and type ='p')
BEGIN
DROP PROCEDURE dbo.RESTOREBKP_2008
END
GO
CREATE PROCEDURE RESTOREBKP_2008
(
@DBName varchar(max), -- DATABASENAME
@BackupPath varchar(max), -- BACKUPFILEPATH
@Path varchar(max) -- PHYSICALPATH
)
AS
BEGIN

DECLARE
--Variables declaration
@ResFilesOnly Nvarchar(max),
@ResQuery Nvarchar(max),
@STR AS nVARCHAR(MAX),
@Logicalname as nvarchar(max),
@physicalname as nvarchar(max)

--- LOOP Variable
DECLARE @I AS INT, @J AS INT


set @ResFilesOnly='RESTORE FILELISTONLY
FROM DISK = '''@BackupPath''''

set @I = 1

DECLARE @RESTORE AS TABLE(
LogicalName nvarchar(128), PhysicalName nvarchar(500), Type char(1), FileGroupName nvarchar(128),
Size numeric(20,0), MaxSize numeric(38,0), FileID bigint, CreateLSN numeric(38,0),
DropLSN numeric(25,0) NULL, UniqueID uniqueidentifier, ReadOnlyLSN numeric(25,0) NULL,
ReadWriteLSN numeric(25,0) NULL, BackupSizeInBytes bigint, SourceBlockSize int,
FileGroupID int, LogGroupGUID uniqueidentifier NULL, DifferentialBaseLSN numeric(38,0) NULL,
DifferentialBaseGUID uniqueidentifier, IsReadOnly bit, IsPresent bit,TDEThumbprint varchar(500)
)
INSERT INTO @RESTORE
EXEC sp_executesql @ResFilesOnly

SELECT @J = MAX(FILEID) FROM @RESTORE
SET @STR = ''
WHILE @I <= @J
BEGIN
SELECT
@Logicalname = LTRIM(RTRIM(Logicalname)), @physicalname = LTRIM(RTRIM(REVERSE(LEFT(REVERSE(physicalname),CHARINDEX('\',REVERSE(physicalname))-1))))
FROM
@RESTORE
WHERE
FILEID = @I
SET @STR = @STR + 'MOVE N''' + @Logicalname + ''' TO N''' + @Path + @physicalname + ''', '

SET @I = @I + 1
END

--Restore Backup Query
set @ResQuery = 'RESTORE DATABASE ' + @dbname + ' FROM DISK = '''+ @BackupPath + ''' WITH '+ @STR +
N' NOUNLOAD, STATS = 10'

EXECUTE SP_EXECUTESQL @ResQuery
END

Execute this Stored Procedure in your master database as given below

EXEC RESTOREBKP_2008 '', '', ''

Note:-In case of any clarification please mail me to mail@renjucool.com

See this article in MSDN by me http://code.msdn.microsoft.com/Sql2008PartionedDb

Table Comparison in Sql Server

SQL Server is having a command line tool (TableDiff) to compare the data in two tables.It will perform the following task.

1. A row by row comparison between a source table in an instance of Microsoft SQL Server acting as a replication Publisher and the destination table at one or more instances of SQL Server acting as replication Subscribers.
   
2. Perform a fast comparison by only comparing row counts and schema.
   
3. Perform column-level comparisons.
   
4. Generate a Transact-SQL script to fix discrepancies at the destination server to bring the source and destination tables into convergence.
   
5. Log results to an output file or into a table in the destination database.

eg:

 

 "C:\Program Files\Microsoft SQL Server\90\COM\TableDiff.exe" -sourceserver "SERVERNAME" -sourcedatabase "DBNAME" -sourceschema "dbo" -sourcetable "SOURCETABLE1" -sourceuser "sa" -sourcepassword "PASSWORD" -destinationserver "SERVERNAME" -destinationdatabase "DBNAME" -destinationschema "dbo" -destinationtable "SOURCETABLE2" -destinationuser "sa" -destinationpassword "PASSWORD" -dt -o "C:\Documents and Settings\renjuraj\My Documents\diff.xls"

 

Run the above command in command prompt.Please refer your sql server installation path(C:\Program Files\Microsoft SQL Server\90\COM\TableDiff.exe).

Longest Running Procedures in Sql Server

Its useful to determine which SProc/Code have the greatest impact on the Server. Sometimes, that is determined by examining the I/O cost,sometimes by the Exectution Duration. In this eg, Total Impact is determined by examining the length of execution and the frequency of execution.

SELECT TOP 10 
 temp.text as ProcedureName, 
 s.execution_count as ExecutionCount, 
 isnull( s.total_elapsed_time / s.execution_count, 0 ) as AvgExecutionTime, 
 s.total_worker_time / s.execution_count as AvgWorkerTime, 
 s.total_worker_time as TotalWorkerTime, 
 s.max_logical_reads as MaxLogicalReads, 
 s.max_logical_writes as MaxLogicalWrites, 
 s.creation_time as CreationDateTime, 
 s.total_physical_reads as PhysicalReads,
 isnull( s.execution_count / datediff( second, s.creation_time,getdate()), 0 )as CallsPerSecond
FROM sys.dm_exec_query_stats s 
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) temp 
ORDER BY 
-- s.total_elapsed_time DESC 
s.execution_count desc

Index to be recreated in Sql Server

When your database grows, the index fragmentation becomes too high,that will scale down the performance of the sql server. To overcome that we need to re-build the index, that should be done at the down time of the server.Here there is a query that will list all the index to be recreated/rebuild.It uses the following tables(sys.indexes,sys.tables,sys.schemas,sys.dm_db_index_physical_stats,sys.partitions). For detailed checkup change the last NULL in the dm_db_index_physical_stats call to 'SAMPLED' or even 'DETAILED'

SELECT 'ALTER INDEX [' + ix.name + '] ON [' + s.name + '].[' + t.name +'] ' +
CASE WHEN ps.avg_fragmentation_in_percent > 40 THEN 'REBUILD'
ELSE 'REORGANIZE' END +
CASE WHEN pc.partition_count > 1 THEN ' PARTITION = ' +
cast(ps.partition_number as nvarchar(max)) ELSE '' END
FROM sys.indexes AS ix INNER JOIN sys.tables t
ON t.object_id = ix.object_id
INNER JOIN sys.schemas s
ON t.schema_id = s.schema_id
INNER JOIN (SELECT object_id, index_id,avg_fragmentation_in_percent, partition_number
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL,NULL, NULL, NULL)) ps
ON t.object_id = ps.object_id AND ix.index_id = ps.index_id
INNER JOIN (SELECT object_id, index_id, COUNT(DISTINCT
partition_number) AS partition_count
FROM sys.partitions
GROUP BY object_id, index_id) pc
ON t.object_id = pc.object_id AND ix.index_id = pc.index_id
WHERE ps.avg_fragmentation_in_percent > 10 AND
ix.name IS NOT NULL

Bugs in Sql Server

Today i got noticed a bug in sql server 2005(also in SSMS 08) while executing a simple select statement

select 3 --(*)

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near '--(*'.

If we put a space or any other characters before the "(" there is no error. Also
select 3 --)* not returning error.Any way this is a bug in sql server as it does not parse the comment statements



Also i got an error message while Parsing an sql script as

."Net SqlClient Data Provider: Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded."

But the script is executing successfully and giving the accurate results!!!!

Last modified Db User Objects & Tables without Trigger in Sql server

If you need to check the details of all DB User Objects by Last Modified Date try the following query


select name, modify_date, case when type_desc = 'USER_TABLE'
then 'Table'when type_desc = 'SQL_STORED_PROCEDURE' then 'Stored Procedure'
when type_desc in ('SQL_INLINE_TABLE_VALUED_FUNCTION', 'SQL_SCALAR_FUNCTION', 'SQL_TABLE_VALUED_FUNCTION')
then 'Function'end as type_desc from sys.objects where type in ('U', 'P', 'FN', 'IF', 'TF')and is_ms_shipped = 0
order by 2 desc  
Find the below query that will list all tables without triggers in sql server.This is helpful when someone modify the table
make sure that there aren't any tables that have triggers on them so that stuff doesn't start to break after you make changes.
 
SELECT ob1.name FROM sysobjects ob1 LEFT JOIN sysobjects ob2 ON
ob1.id =ob2.parent_obj AND ob2.xtype = 'TR'
WHERE ob2.name IS NULL AND ob1.xtype = 'U'
ORDER BY ob1.name

Using spt_values to generate junk datas

master..spt_values can be used if you need to populate a table with 100 numbers from 1,or if you need to generate some Junk/Test data.Refer the query below that will generate data of various datatypes

select
abs(checksum(newid()))%10000 as n_id,
abs(checksum(newid()))*rand()/10000 as f_float,
substring(replace(cast(newid() as varchar(36)),'-',''),1,abs(checksum(newid()))%15) as s_string,
dateadd(day,0,abs(checksum(newid()))%100000) as d_date,
abs(checksum(newid()))%2 as b_byte
from master..spt_values
where type='p' and number between 1 and 200

Trigger to monitor the entire database server

Please find this trigger script to monitor the entire database activities and to log its records into audit tables.
It will work on Sql Server 2008.
 
USE [master]
-------Create table -------------------------
-- DROP TABLE AuditTrigger
CREATE TABLE [dbo].[AuditTrigger](
[OID] [bigint] IDENTITY(1,1) NOT NULL,
[Details] [varchar](max) NULL,
[Host] [varchar](20) NULL
)
GO
-----Create proc to insert into AuditTrigger table
IF EXISTS(SELECT 1 FROM sysobjects where name = 'PInsertAuditTrigger')
BEGIN
DROP PROCEDURE PInsertAuditTrigger
END
GO
CREATE PROC PInsertAuditTrigger(@Details varchar(max),@hostname VARCHAR(MAX))
AS
BEGIN
INSERT INTO master.dbo.AuditTrigger(DETAILS,HOST) SELECT @DETAILS,@HOSTNAME
END
-------- Trigger to monitor tables
GO
CREATE TRIGGER [Table_Trigger]
ON ALL SERVER
FOR create_table,alter_table,drop_table
AS
BEGIN
print 'Create/Alter table is not allowed - '+host_name()
rollback;
DECLARE @host_name varchar(max),@Details varchar(max)
SELECT @host_name = Host_name(),@Details = 'Create/Alter table'
exec dbo.PInsertAuditTrigger @Details,@host_name
END
-------- Trigger to monitor procedures
GO
CREATE TRIGGER [Proc_Trigger]
ON ALL SERVER
FOR create_procedure,alter_procedure,drop_procedure
AS
BEGIN
print 'Create/Alter procedure is not allowed - '+host_name()
rollback;
DECLARE @host_name varchar(max),@Details varchar(max)
SELECT @host_name = Host_name(),@Details = 'Create/Alter procedure'
exec dbo.PInsertAuditTrigger @Details,@host_name
END
-------- Trigger to monitor login,roles and database
GO
CREATE TRIGGER [Misc_Trigger]
ON ALL SERVER
FOR Create_role,drop_role,alter_role,create_database,drop_database,alter_database,create_view,alter_view,drop_view,create_login,drop_login
AS
BEGIN
print 'Illegal operation is not allowed - '+host_name()
rollback;
DECLARE @host_name varchar(max),@Details varchar(max)
SELECT @host_name = Host_name(),@Details = 'Illegal operation'
exec dbo.PInsertAuditTrigger @Details,@host_name
END
GO

Removing all active connections from the db

Use Master

Go

Declare @dbname sysname

Set @dbname = 'DBNAME'

Declare @spid int Select @spid = min(spid) from master.dbo.sysprocesses

where dbid = db_id(@dbname)

While @spid Is Not Null Begin

Execute ('Kill ' + @spid)

Select @spid = min(spid) from master.dbo.sysprocesses

where dbid = db_id(@dbname) and spid > @spid

End