Catégorie : TSQL

Catch and log ddl update on multiple databases

  • Classé dans TSQL

  • 2018/04/10

  • 2018/04/10

database_sql_watch_TSQL

This is to catch and log ddl changes on multiple databases on the same server.

Step 1 : Create the new schema audit

-----------------------------------------------------------------------------------------------------------------------
--  Use tab stop value = 2 et indent spaces = 2
-----------------------------------------------------------------------------------------------------------------------
--  Name				: database_sql_watch
--	Author			:	Thierry Bertin
--							:	www.abcdata.ca
--	Date				:	2018-04-10
--	Comment			:	To catch and log ddl change on multiple databases
-----------------------------------------------------------------------------------------------------------------------
--  Change Log	:	Date				-	Author					-	Description
--                -----------------------------------------------------------------------------------------------------
--								2018-04-10	-	Thierry Bertin 	-	Create Schema, Table, and database trigger for sql_watch
-----------------------------------------------------------------------------------------------------------------------

-- To be created on each database to be watched
create schema Audit
							authorization dbo;
go

Step 2 : Create the new audit table on the database that support audit activity

-----------------------------------------------------------------------------------------------------------------------
--  Use tab stop value = 2 et indent spaces = 2
-----------------------------------------------------------------------------------------------------------------------
--  Name				: database_sql_watch
--	Author			:	Thierry Bertin
--							:	www.abcdata.ca
--	Date				:	2018-04-10
--	Comment			:	To catch and log ddl change on multiple databases
-----------------------------------------------------------------------------------------------------------------------
--  Change Log	:	Date				-	Author					-	Description
--                -----------------------------------------------------------------------------------------------------
--								2018-04-10	-	Thierry Bertin 	-	Create Schema, Table, and database trigger for sql_watch
-----------------------------------------------------------------------------------------------------------------------

-- To be created on the database that support audit activity
create table Audit.SQL_Change_Log
(
		SQL_Change_Log_Id 		integer 						identity (1 , 1) 				not null
	,	Database_Name 				varchar (256) 															not null
	,	Event_Type 						varchar	(50) 																not null
	,	Object_Name 					varchar	(256) 															not null
	,	Object_Type 					varchar	(25) 																not null
	,	Sql_Command 					varchar	(7000) 															not null
	,	Event_Date 						datetime 																		not null 	default (getdate ())
	,	Login_Name 						varchar	(256) 															not null
)
on [primary];
go

Step 3 : Create the database trigger on each database to be watched

-----------------------------------------------------------------------------------------------------------------------
--  Use tab stop value = 2 et indent spaces = 2
-----------------------------------------------------------------------------------------------------------------------
--  Name				: database_sql_watch
--	Author			:	Thierry Bertin
--							:	www.abcdata.ca
--	Date				:	2018-04-10
--	Comment			:	To catch and log ddl change on multiple databases
-----------------------------------------------------------------------------------------------------------------------
--  Change Log	:	Date				-	Author					-	Description
--                -----------------------------------------------------------------------------------------------------
--								2018-04-10	-	Thierry Bertin 	-	Create Schema, Table, and database trigger for sql_watch
-----------------------------------------------------------------------------------------------------------------------

-- To be created on each database to be watched
create trigger catch_sql_object_change                                    
on database                                                      
for 
	create_procedure
, alter_procedure
, drop_procedure
, create_table
, alter_table
, drop_table
, create_function
, alter_function
, drop_function                   
, create_view
, alter_view
, drop_view            
as                                                               
begin                                                                 
	set nocount on;
	                                                
  declare @data xml;                                                

	set @data = eventdata ()                                          
                                                                 
	insert 
	into 							[database name].Audit.SQL_Change_Log	
										(Database_Name , Event_Type , Object_Name , Object_Type , Sql_Command , Login_Name)               
	values						(	
												@data.value	('(/EVENT_INSTANCE/DatabaseName)	[1]' 	, 'varchar	(256)')
											, @data.value	('(/EVENT_INSTANCE/EventType)			[1]' 	, 'varchar	(50)')
											, @data.value ('(/EVENT_INSTANCE/ObjectName)		[1]'	, 'varchar	(256)')
											,	@data.value	('(/EVENT_INSTANCE/ObjectType)		[1]'	, 'varchar	(25)')
											,	@data.value	('(/EVENT_INSTANCE/TSQLCommand)		[1]'	, 'varchar	(7000)')
											,	@data.value	('(/EVENT_INSTANCE/LoginName)			[1]'	, 'varchar	(256)')    
										);
	set nocount off;
end;
go

enable trigger catch_sql_object_change on database;
go
-----------------------------------------------------------------------------------------------------------------------

Lien Permanent pour cet article : https://www.abcdata.ca/abc/?p=106

TSQL – How to partition a table

  • Classé dans TSQL

  • 2018/04/09

  • 2018/04/09

database_partition_package

Five TSQL scripts to partition a table by date to split data on 3 partitions like Cold, Warm, and Hot categories.

Step 1 : Create 3 news partitions on database

-----------------------------------------------------------------------------------------------------------------------
--  Use tab stop value = 2 et indent spaces = 2
-----------------------------------------------------------------------------------------------------------------------
--  Name				: database_partition_cold_warm_hot
--	Author			:	Thierry Bertin
--							:	www.abcdata.ca
--	Date				:	2018-04-09
--  Comment			: Replace [default file location] and [database name] by the right names
-----------------------------------------------------------------------------------------------------------------------
--  Change Log	:	Date				-	Author					-	Description
--                -----------------------------------------------------------------------------------------------------
--								2018-04-09	-	Thierry Bertin 	-	Create a 3 partitions for Cold, Warm, Hot Data
-----------------------------------------------------------------------------------------------------------------------
alter database	[database name]
			add filegroup [Part_Cold_Data];
go

alter database	[database name]
			add file
			(
					name				= [Part_Cold_Data]
				,	filename		= '[default file location]\[database name]_Part_Cold_Data.ndf'
				,	size				=	500MB
				,	maxsize			=	'unlimited'
				, filegrowth	=	500MB
			)
			to	filegroup	[Part_Cold_Data];
go


alter database	[database name]
			add filegroup [Part_Warm_Data];
go

alter database	[database name]
			add file
			(
					name				= [Part_Warm_Data]
				,	filename		= '[default file location]\[database name]_Part_Part_Warm_Data.ndf'
				,	size				=	500MB
				,	maxsize			=	'unlimited'
				, filegrowth	=	500MB
			)
			to	filegroup	[Part_Warm_Data];
go


alter database	[database name]
			add filegroup [Part_Hot_Data];
go

alter database	[database name]
			add file
			(
					name				= [Part_Hot_Data]
				,	filename		= '[default file location]\[database name]_Part_Part_Hot_Data.ndf'
				,	size				=	500MB
				,	maxsize			=	'unlimited'
				, filegrowth	=	500MB
			)
			to	filegroup	[Part_Hot_Data];
go
-----------------------------------------------------------------------------------------------------------------------

Step 2 : Create a partition function on database

-----------------------------------------------------------------------------------------------------------------------
--  Use tab stop value = 2 et indent spaces = 2
-----------------------------------------------------------------------------------------------------------------------
--  Name				: database_partition_function_cold_warm_hot
--	Author			:	Thierry Bertin
--							:	www.abcdata.ca
--	Date				:	2018-04-09
--  Comment			: Parition function is related to paritions schemes
-----------------------------------------------------------------------------------------------------------------------
--  Change Log	:	Date				-	Author					-	Description
--                -----------------------------------------------------------------------------------------------------
--								2018-04-09	-	Thierry Bertin 	-	Create partition function to partitionning tables on datetime column
-----------------------------------------------------------------------------------------------------------------------
drop partition function Cold_Warm_Hot_PF;

create partition function Cold_Warm_Hot_PF (datetime) 
as range right 
for values	(		--	Cold	Data
								'2016-12-31'
								--	Warm	Data
							, '2017-12-31'
								--	Hot	Data
							, '2018-01-01'
						);
go
-----------------------------------------------------------------------------------------------------------------------

Step 3 : Create a partition scheme on database

-----------------------------------------------------------------------------------------------------------------------
--  Use tab stop value = 2 et indent spaces = 2
-----------------------------------------------------------------------------------------------------------------------
--  Name				: database_partition_scheme_cold_warm_hot
--	Author			:	Thierry Bertin
--							:	www.abcdata.ca
--	Date				:	2018-04-09
--  Comment			: Parition names are related to paritions created on script that create the partitions
-----------------------------------------------------------------------------------------------------------------------
--  Change Log	:	Date				-	Author					-	Description
--                -----------------------------------------------------------------------------------------------------
--								2018-04-09	-	Thierry Bertin 	-	Create a partition scheme to partitionning tables
-----------------------------------------------------------------------------------------------------------------------
create partition	scheme Cold_Warm_Hot_PS 
as partition			Cold_Warm_Hot_PF 
to								(		--	Cold	Data
											Part_Cold_Data 
											--	Warm	Data
											, Part_Warm_Data 
											--	Hot	Data
											, Part_Hot_Data
									);
go
-----------------------------------------------------------------------------------------------------------------------

Step 4 : Create partition index on the table

-----------------------------------------------------------------------------------------------------------------------
--  Use tab stop value = 2 et indent spaces = 2
-----------------------------------------------------------------------------------------------------------------------
--  Name				: Create_Index_Clustered
--	Author			:	Thierry Bertin
--							:	www.abcdata.ca
--	Date				:	2018-04-09
--	Comment			:	To add data table to partitions
-----------------------------------------------------------------------------------------------------------------------
--  Change Log	:	Date				-	Author					-	Description
--                -----------------------------------------------------------------------------------------------------
--								2018-04-09	-	Thierry Bertin 	-	Create clustered index on table to partition this table
-----------------------------------------------------------------------------------------------------------------------

--	Remove existing clustered index
alter	table						dbo.[your table name to be partitionned]
											drop constraint	[your table name to be partitionned]_PK
											with (online = off);
go

--	Add the same index nonclustered
alter	table						dbo.[your table name to be partitionned]
											add constraint	[your table name to be partitionned]_PK
											primary	key	nonclustered 
											([your table name to be partitionned]_id asc) -- Column that is used as primary key
											with (pad_index = off , statistics_norecompute = off , ignore_dup_key = off , allow_row_locks = on , allow_page_locks = on) 
											on [large_table_index_01]; -- default partition for indexes on large table to not have indexes on Primary partition
go

--	Add new clustered index for partitionning
create	clustered index [your table name to be partitionned]_Part
												on		[your table name to be partitionned] (date) -- Partitionning by date column on table [your table name to be partitionned]
												with	(pad_index = off , statistics_norecompute = off , ignore_dup_key = off , allow_row_locks = on , allow_page_locks = on)
												on		Cold_Warm_Hot_PS (date);
go
-----------------------------------------------------------------------------------------------------------------------

Step 5 : Validate that data is split on partitions

-----------------------------------------------------------------------------------------------------------------------
--  Use tab stop value = 2 et indent spaces = 2
-----------------------------------------------------------------------------------------------------------------------
--  Name				: Partition_Validate
--	Author			:	Thierry Bertin
--							:	www.abcdata.ca
--	Date				:	2018-04-09
--	Comment			:	To validate that data is correctly spread into the partitions
-----------------------------------------------------------------------------------------------------------------------
--  Change Log	:	Date				-	Author					-	Description
--                -----------------------------------------------------------------------------------------------------
--								2018-04-09	-	Thierry Bertin 	-	To validate the partitionning
-----------------------------------------------------------------------------------------------------------------------
declare					@Table_Name								varchar		(200);

set	@Table_Name = 'your partitionned table name';

select					distinct	
								o.name								as	table_name
							,	i.name								as	index_name
							, p.partition_number
							, fg.name								as	filegroup_name
							, p.rows								as  rows#
from						sys.partitions p 
								inner	join	sys.objects o 
								on					o.object_id	=	p.object_id
								inner	join	sys.indexes i 
								on					i.object_id	=	p.object_id 
								and					i.index_id	=	p.index_id								
								inner	join	sys.allocation_units au 
								on					au.container_id = p.hobt_id 
														inner	join	sys.filegroups fg 
														on					fg.data_space_id = au.data_space_id 
where						o.name = @Table_Name;
-----------------------------------------------------------------------------------------------------------------------

Lien Permanent pour cet article : https://www.abcdata.ca/abc/?p=96

TSQL – Listing Filegroups linked Objects

  • Classé dans TSQL

  • 2018/03/29

  • 2018/03/29

database_filegroup_objects_get_TSQL

-- =================================================
-- Best viewing if using tabs = 2 to your editor
-- =================================================
-- Author				:	Thierry Bertin
-- 							: www.abcdata.ca
-- Create date	: 2018-03-29
-- Description	: List Filegroups Objects 
-- =================================================
declare @FileGroup_Name																nvarchar	(250);

set @FileGroup_Name = N'%';

select					a.name														as	Name_of_Filegroup	
							, object_name (b.object_id)					as	Name_of_Object
							,	b.name														as	Index_Name
							,	b.type_desc												as	Index_Type
							,	c.type														as	Object_Type
							,	c.type_desc												as	Object_Description
from						sys.filegroups as a 
								left	outer	join	sys.indexes as b 
								on								a.data_space_id = b.data_space_id
																	inner	join	sys.all_objects as c 
																	on					c.object_id = b.object_id
where						lower (a.name) like @FileGroup_Name				
order by				5 , 1 , 2;

Lien Permanent pour cet article : https://www.abcdata.ca/abc/?p=82

TSQL – Compute Tables Indexes Fragmentation

  • Classé dans TSQL

  • 2018/03/28

  • 2018/03/28

database_tables_indexes_fragmentation_compute_TSQL

This script compute tables indexes fragmentation to keep a view on.

-- =================================================
-- Best viewing if using tabs = 2 to your editor
-- =================================================
-- Author				:	Thierry Bertin
-- 							: www.abcdata.ca
-- Create date	: 2018-03-28
-- Description	: Compute tables indexes fragments
-- =================================================

declare @db_id																				smallint;
declare @table_name_pattern														nvarchar	(250);
declare @frag_floor_percent														integer;
declare @record_count_min															integer;

set @db_id							= db_id	(N'my_db_name');
set @table_name_pattern = '%table_name_pattern%';
set @frag_floor_percent = 40;
set @record_count_min	  = 10000;

select				object_name	(a.object_id) as	TableName
						, c.name										as	IndexName
						,	a.Index_type_desc
						, a.avg_fragmentation_in_percent
						, a.avg_fragment_size_in_pages
						,	a.avg_page_space_used_in_percent
						, a.record_count
						,	a.ghost_record_count
						,	a.fragment_count
						,	a.avg_fragment_size_in_pages
from					sys.dm_db_index_physical_stats	(@db_id	, null , null , null , 'DETAILED') a
							inner	join	sys.tables b
							on					b.object_id				= a.object_id
							and					b.is_ms_shipped		= 0
							inner	join	sys.indexes c 
							on					c.object_id = a.object_id 
							and					c.index_id	= a.index_id
where					lower (left (object_name	(a.object_id) , 4)) like	@table_name_pattern
and						a.avg_fragmentation_in_percent								>			@frag_floor_percent
and						a.record_count																>			@record_count_min								
order	by			7 desc;

Index with almost 50% of fragmentation need to reorganized to become relevant to be selected and used by query optimizer.

Lien Permanent pour cet article : https://www.abcdata.ca/abc/?p=53

TSQL – Count SQL Objects by Types

  • Classé dans TSQL

  • 2018/03/28

  • 2018/03/28

database_object_by_type_count_TSQL

-- =================================================
-- Best viewing if using tabs = 2 to your editor
-- =================================================
-- Author				:	Thierry Bertin
-- 							: http:\\www.abcdata.ca
-- Create date	: 2018-03-28
-- Description	: Count SQL Objects by Type
-- =================================================
select			case 	type
							when	'AF'	then	'Aggregate function (CLR)'
							when	'C'		then	'CHECK constraint'
							when	'D'		then	'DEFAULT (constraint or stand-alone)' 
							when	'F'		then	'FOREIGN KEY constraint' 
							when	'FN'	then	'SQL scalar function' 
							when	'FS'	then	'Assembly (CLR) scalar-function' 
							when	'FT'	then	'Assembly (CLR) table-valued function' 
							when	'IF'	then	'SQL inline table-valued function'
							when	'IT'	then	'Internal table'
							when	'P'		then	'SQL Stored Procedure'
							when	'PC'	then	'Assembly (CLR) stored-procedure'
							when	'PG'	then	'Plan guide'
							when	'PK'	then	'PRIMARY KEY constraint'
							when	'R'		then	'Rule (old-style, stand-alone)'
							when	'RF'	then	'Replication-filter-procedure'
							when	'S'		then	'System base table'
							when	'SN'	then	'Synonym'
							when	'SO'	then	'Sequence object'
							when	'SQ'	then	'Service queue'
							when	'TA'	then	'Assembly (CLR) DML trigger'
							when	'TF'	then	'SQL table-valued-function'
							when	'TR'	then	'SQL DML trigger'
							when	'TT'	then	'Table type'	
							when	'U'		then	'Table (user-defined)'
							when	'UQ'	then	'UNIQUE constraint'
							when	'V'		then	'View'
							when	'X'		then	'Extended stored procedure'
							else							'Unknow'
						end									as	Object
					, count (*)						as	#
from				sys.objects
group by	  type
order by	  type;

Lien Permanent pour cet article : https://www.abcdata.ca/abc/?p=47

TSQL – Listing SQL Objects by Type

  • Classé dans TSQL

  • 2018/03/28

  • 2018/03/28

database_object_by_type_get_TSQL

A simple script to list sql objects by types :

-- =================================================
-- Best viewing if using tabs = 2 to your editor
-- =================================================
-- Author				:	Thierry Bertin
-- 							: www.abcdata.ca
-- Create date	: 2018-03-28
-- Description	: Get SQL Objects by Type
-- =================================================
select						type 
								,	name
from							sys.objects
where							type	 in (
																	'AF'		--	Aggregate function (CLR)
																,	'C'			--	CHECK constraint
																,	'D'			--	DEFAULT (constraint or stand-alone) 
																, 'F'			--	FOREIGN KEY constraint 
																, 'FN'		--	SQL scalar function 
																, 'FS'		--	Assembly (CLR) scalar-function 
																, 'FT'		--	Assembly (CLR) table-valued function 
																, 'IF'		--	SQL inline table-valued function
																, 'IT'		--	Internal table
																, 'P'			--	SQL Stored Procedure
																, 'PC'		--	Assembly (CLR) stored-procedure
																, 'PG'		--	Plan guide
																,	'PK'		--	PRIMARY KEY constraint
																, 'R'			--	Rule (old-style, stand-alone)
																, 'RF'		--	Replication-filter-procedure
																,	'S'			--	System base table
																, 'SN'		--	Synonym
																, 'SO'		--	Sequence object
																, 'SQ'		--	Service queue
																,	'TA'		--	Assembly (CLR) DML trigger
																,	'TF'		--	SQL table-valued-function
																,	'TR'		--	SQL DML trigger
																, 'TT'		--	Table type	
																, 'U'			--	Table (user-defined)
																, 'UQ'		--	UNIQUE constraint
																, 'V'			--	View
																,	'X'			--	Extended stored procedure
															)
order by					type , name;

Lien Permanent pour cet article : https://www.abcdata.ca/abc/?p=44

TSQL – Template for Stored Procedure

  • Classé dans TSQL

  • 2018/03/28

  • 2018/03/28

stored_procedure_template_TSQL

This file include a tsql stored procedure template, with :

  • Error catching
  • Log error in SQL Sever Log
  • Transaction management
-- =================================================
-- Best viewing if using tabs = 2 to your editor
-- =================================================
-- Author				:	Thierry Bertin
-- 							: www.abcdata.ca
-- Create date	: 2018-03-26
-- Description	: Template for TSQL Stored procedure
-- =================================================
create procedure dbo.table_name_get
(
	-- parameters ...
		@param_01																					varchar		(10)
	, @param_02																					varchar		(10)
	, @param_03																					integer						output
)
as
begin
	--	Suppress unwanted output
	set nocount on;
	
	--  Mandatory to manage SQL error loging						--	Don't delete  --
	declare @error_point																varchar		(2);
	declare	@error_type																	varchar		(20);
	declare	@error_data																	varchar		(1000);
	declare	@error_number																integer;
	declare	@error_severity															integer;
	declare	@error_state																integer;
	declare	@error_procedure														varchar 	(50);
	declare	@error_line																	varchar 	(50);
	declare	@error_message															varchar 	(500);
	declare @xact_state																	integer;

	--	Working Variables

	begin try
		--	Initialize a transaction
		begin transaction;

		--	Initialize Variables

		--	Query 01  --  Flag for error catching
		set @error_point 	= '01';
		set @error_type 	= 'select';
		select					Col_01
									, Col_02
									, Col_03
									, ...
		from						dbo.table_name
		where						where_clause;

		--	Query 02  --  Flag for error catching
		set @error_point 	= '02';
		set @error_type 	= 'select';
		select					Col_01
									, Col_02
									, Col_03
									, ...
		from						dbo.table_name
		where						where_clause;
		
		--	Commit transaction
		if (@@trancount > 0)
		begin
			commit transaction;
		end
	end try
	begin catch
		--  Save error values
		select					@error_number 		= error_number()
									, @error_severity 	= error_severity()
									, @error_state 			= error_state()
									, @error_procedure 	= error_procedure()
									, @error_line 			= error_line()
									, @error_message 		= error_message()
									, @xact_state 			= xact_state();
	end catch;

	--  Set error message, log and manage transaction
	if (@error_number <> 0)
	begin
		set @error_data 		= 'error on '					+ @error_type
												+ ' / error code = ' 	+ cast (@error_number 		as varchar (10))
												+ ' / severity = ' 		+ cast (@error_severity 	as varchar (10))
												+ ' / state = ' 			+ cast (@error_state 			as varchar (10)) 
												+ ' / xact_state = ' 	+ cast (@xact_state 			as varchar (10)) 
												+ ' / procedure = ' 	+ @error_procedure
												+ ' / line = ' 				+ cast (@error_line 			as varchar (10))
												+ ' / message = "' 		+ @error_message + '"';
			
		set @error_data 	= 'SP Error at query : ' + @error_point + ' - ' + @error_data;

		--  Reset nocount option
		set nocount off
			
		--	Rollback transaction
		if (@@trancount > 0)
		begin
			if (@xact_state	=	-1)
			begin
				rollback transaction;
			end
			else
			begin
				if	(@xact_state = 1)
				begin
					commit transaction;
				end
			end
		end

		--  Stop SP and write error message into sql server log
		raiserror (@error_data , 16 , 1) with log;
		return (99);
	end
	
	--  Reset nocount option
	set nocount off

	--  End Stored procedure
	return (0);

end
go
-----------------------------------------------------------------------------------------------------------------------

Lien Permanent pour cet article : https://www.abcdata.ca/abc/?p=26