Hour:Minutes

Given a datetime value (a field that is of type datetime), to display only hour : minutes , with out seconds and date.

DECLARE
@dt_MyDate datetime = ‘20230825 06:05:45’;

SELECT
FORMAT(@dt_MyDate, N’HH:mm’) as [HH:mm];
/* HH:mm in 24h format
hh:mm tt in AM PM format
*/

SELECT
CONVERT(char(5), @dt_MyDate, 108) as [HH:mm];

S

Index usage stats

Every sql server developer/dba should have some “utility” scripts, for looking at particular point.
One of this script is about index usage , that is looking to a table and get information about index, how they are used, statistics …

set nocount on
DECLARE 
	@objname nvarchar(128);
select OBJECT_NAME(i.object_id) as tblName
	,i.name	
	,i.is_disabled as esteDezactivat
	,STUFF(ca.IndexColumnsNames,1,1,'') as IndexColumns
	,STUFF(ia.IncludedColumnsNames,1,1,'') as IncludedColumns
	,STATS_DATE(ius.object_id, i.index_id) as  Stat_Date
	,ps.reserved_page_count as noPages
	,ius.user_seeks
	,ius.user_scans
	,ius.user_lookups
	,ius.last_user_seek
	,ius.last_user_scan
	--,i.*
	,case when i.index_id  =1 then 1 else 0 end as is_clustered
	,i.is_unique 
	,i.is_primary_key
	,i.is_unique_constraint
	,i.is_disabled
	,i.has_filter
	,i.filter_definition
	,ps.data_compression_desc
	,'ALTER INDEX ' +  QUOTENAME(i.name) + ' ON ' + QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id )) + '.'+ QUOTENAME(OBJECT_NAME(i.object_id)) + ' DISABLE;' as DisableStmt
	,'DROP INDEX ' +  QUOTENAME(i.name) + ' ON ' + QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id )) + '.'+ QUOTENAME(OBJECT_NAME(i.object_id)) + ';'  as dropStmt
from
	sys.objects as o
	left join sys.indexes as i
		on o.object_id = i.object_id
	left join sys.dm_db_index_usage_stats as ius
		ON ius.object_id = i.object_id
			AND ius.index_id = i.index_id	
			AND ius.database_id = DB_ID(DB_Name())
	cross apply
		(select ', '+ c.name +''
			from sys.index_columns as ic
					inner join sys.columns as c
						on c.object_id = ic.object_id
						and c.column_id = ic.column_id
			where ic.object_id = i.object_id
				and ic.index_id = i.index_id
				and ic.is_included_column = 0
			order by ic.key_ordinal	
			for xml path('')
		)ca(IndexColumnsNames)
	outer apply
		(select ', '+ c.name +''
			from sys.index_columns as ic
					inner join sys.columns as c
						on c.object_id = ic.object_id
						and c.column_id = ic.column_id
			where ic.object_id = i.object_id
				and ic.index_id = i.index_id
				and ic.is_included_column = 1
			order by ic.index_column_id ASC
			for xml path('')
		)ia(IncludedColumnsNames)
	left join (select		
					ps.object_id
					,ps.index_id
					, sum(ps.reserved_page_count) as reserved_page_count
					,p.data_compression_desc as data_compression_desc
				from sys.dm_db_partition_stats  as ps
					inner join sys.partitions as p
						ON ps.partition_id = p.partition_id
						AND ps.object_id = p.object_id
				group by ps.object_id,ps.index_id,p.data_compression_desc
				)ps
		on ps.object_id = i.object_id
			and ps.index_id = i.index_id
where
	1=1
	--ius.user_seeks  = 0  
	--and ius.user_scans = 0
	--and ius.user_lookups = 0
	--and i.type > 1 -- nonclustered index
	--and i.is_unique = 0 -- is not unique
	--and i.is_primary_key = 0 -- not PK
	--and i.is_unique_constraint = 0 --not Unique Constraint
	/********and i.is_disabled = 0 -- is active*******/
	--and i.has_filter = 0
	--and database_id = DB_ID(DB_Name())  -- database actuala
	and OBJECT_NAME(o.object_id) = @objname 
order by OBJECT_NAME(ius.object_id),IndexColumns,IncludedColumns, QUOTENAME(i.name);

Thanks,
S