Thierry Bertin

Author's posts

Stored Procedure Template, Azure version

azure_stored_procedure_template

Because SQL Server version on Azure plateforme dosn’t allow to write in sql server log, the template for stored procedure with sql errors catching is different.

In place off writing sql error message on sql server log, the sp write into an audit table.

Create Audit.Error_Log table

-- ==============================================
-- Author				:	Thierry Bertin - www.abcdata.ca
-- Create date	: 2018-04-27
-- Description	: Create Table Audit.Error_Log
-- ==============================================
create table Audit.Error_Log
(
		Error_Log_Id					integer 	identity (1, 1)					not null
 , 	Point       					varchar 	(2) 										not nulL
 , 	Type        					varchar 	(20) 										not nuLL
 , 	Number      					integer 													not null
 , 	Severity    					integer 													not null
 , 	State       					integer 													not null
 , 	Procedure   					varchar 	(50) 										not nuLL
 , 	Line        					varchar 	(50) 										not nuLL
 , 	Message     					varchar 	(500) 									not nULL
 , 	xact_State  					integer 													not null
 , 	Upd_DT      					datetime 													not null
 , 	constraint	pk_error_log	primary key clustered (error_log_id	asc)
);
go
-----------------------------------------------------------------------------------------------------------------------

Create Audit.Error_Log_Add stored procedure

-- ==============================================
-- Author				:	Thierry Bertin - www.abcdata.ca
-- Create date	: 2018-04-19
-- Description	: Insert into Audit.Error_Log
-- ==============================================
create procedure [Audit].Error_Log_Add
(
		@error_point																			varchar		(2)
	, @error_type																				varchar		(20)
	, @error_number																			integer
	, @error_severity																		integer
	, @error_state																			integer
	, @error_procedure																	varchar 	(50)
	, @error_line																				varchar 	(50)
	, @error_message																		varchar 	(500)
	, @xact_state																				integer
)
as
begin
	--	Suppress unwanted output
	set nocount on;
	
	insert
	into						[Audit].Error_Log
								(
									point		
								,	type			
								, number		
								, severity	
								, state		
								, [procedure]
								, line			
								, message	
								, xact_state			
								, Upd_DT
								)
	values				(
									@error_point		
								, @error_type			
								, @error_number		
								, @error_severity	
								, @error_state		
								, @error_procedure
								, @error_line			
								, @error_message	
								, @xact_state
								, getdate ()			
								);
		
	--  Reset nocount option
	set nocount off

	--  End Stored procedure
	return (0);

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

Create Audit.Error_Lst stored procedure to list errors log between two dates

-- ==============================================
-- Author				:	Thierry Bertin - www.abcdata.ca
-- Create date	: 2018-04-19
-- Description	: Template SP Azure like
-- ==============================================
create procedure Audit.Error_Log_Lst
(
		@Begin_DT																					datetime					= null
	, @End_DT																						datetime					= null
)
as
begin
	--	Suppress unwanted output
	set nocount on;
	
	--  Mandatory 																			--	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
		if (@Begin_DT is null)
		begin
			set @Begin_DT = dateadd (day , -5 , getdate ());
		end

		if (@End_DT is null)
		begin
			set @End_DT = dateadd (day , +1 , getdate ());
		end

		--	Query 01
		set @error_point 	= '01';
		set @error_type 	= 'select';
		select					Point
									, Type
									, Number
									, Severity
									, State
									, [Procedure]
									, Line
									, Message
									, xact_state
									, Upd_DT
		from						Audit.Error_Log
		where						Upd_DT between @Begin_DT and @End_DT
		order by				Upd_DT desc;
				
		--	Commit transaction
		if (@@trancount > 0)
		begin
			commit transaction;
		end
	end try
	begin catch
		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;

	--  Error on select
	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;

		execute Audit.Error_Log_Add @error_point , @error_type , @error_number , @error_severity , @error_state , @error_procedure , @error_line , @error_message , @xact_state;

		--  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);
		return (99);
	end
	
	--  Reset nocount option
	set nocount off

	--  End Stored procedure
	return (0);

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

And the stored procedure template

-- ==============================================
-- Author				:	Thierry Bertin - www.abcdata.ca
-- Create date	: 2018-04-19
-- Description	: Template SP Azure like
-- ==============================================
create procedure Schema.SP_Name_Add (Get / Lst / Set / Del)
(
	--	insert parameters ...
)
as
begin
	--	Suppress unwanted output
	set nocount on;
	
	--  Mandatory 																			--	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
		set @error_point 	= '01';
		set @error_type 	= 'insert';
		--	insert query here
				
		--	Commit transaction
		if (@@trancount > 0)
		begin
			commit transaction;
		end
	end try
	begin catch
		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;

	--  Error on select
	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;

		execute Audit.Error_Log_Add @error_point , @error_type , @error_number , @error_severity , @error_state , @error_procedure , @error_line , @error_message , @xact_state;

		--  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);
		return (99);
	end
	
	--  Reset nocount option
	set nocount off

	--  End Stored procedure
	return (0);

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

Lien Permanent pour cet article : http://www.abcdata.ca/abc/?p=112

Catch and log ddl update on multiple databases

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 : http://www.abcdata.ca/abc/?p=106

TSQL – How to partition a table

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 : http://www.abcdata.ca/abc/?p=96

TSQL – Listing Filegroups linked Objects

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 : http://www.abcdata.ca/abc/?p=82

TSQL – Compute Tables Indexes Fragmentation

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								&gt;			@frag_floor_percent
and						a.record_count																&gt;			@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 : http://www.abcdata.ca/abc/?p=53

TSQL – Count SQL Objects by Types

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 : http://www.abcdata.ca/abc/?p=47

TSQL – Listing SQL Objects by Type

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 : http://www.abcdata.ca/abc/?p=44

TSQL – Template for Stored Procedure

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 : http://www.abcdata.ca/abc/?p=26