Life with SQL server

  --Creates an 'InLine' Table Valued Function (TVF) -- Author: krishang -- Date: 28 Feb -2011 -- Remark: this function is similar to the uIntIDTotable however takes a string and returns a table with the string value -- sperated by the delimiter. also includes an index which for the elements. create FUNCTION [dbo].[uSplit] ( @Delimiter varchar(5), @List varchar(max) ) RETURNS @TableOfValues table ( RowID smallint IDENTITY(1,1), [Value] varchar(max) ) AS BEGIN DECLARE @LenString int WHILE len( @List ) > 0 BEGIN SELECT @LenString = (CASE charindex( @Delimiter, @List ) WHEN 0 THEN len( @List ) ELSE ( charindex( @Delimiter, @List ) -1 ) END ) INSERT INTO @TableOfValues SELECT substring( @List, 1, @LenString )

/* create function */ CREATE FUNCTION [dbo].[IntIDToTable] (@vc_Ids varchar(1000), @vc_sperator char(1) ) -- Author Krishan Gurusinghe -- Remark : This function takes a list if indexes in the form of a string variable and then returns -- a table data type variable used for a select * from D in (select ID from intIDToTable(@d) -- RETURNS @IDTable TABLE (Id numeric(18,0)) BEGIN DECLARE @in_Index1 AS INT, --Used to store EmployeeID delimiter(',') position in string @vc_AnyId AS VARCHAR(15) /* initialize working variables */ SET @in_Index1 = CHARINDEX(@vc_sperator,@vc_Ids) /* loop through facility ids in delimited string */ WHILE (@in_Index1 > 0 OR LEN(@vc_Ids) > 0) BEGIN /* parse out single facility id for processing */ IF @in_Index1 > 0 BEGIN SET @vc_AnyId = Left(@vc_Ids,@in_Index1 - 1) SET @vc_Ids = Right(@vc_Ids,Len(@vc_Ids) - @in_Index1) END ELSE BEGIN SET @vc_AnyId = @vc_Ids SET @vc_

Figure out what tables have records in them. The following gives a summary. SELECT QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName] , SUM(sPTN.Rows) AS [RowCount] into utblListOftables FROM sys.objects AS sOBJ INNER JOIN sys.partitions AS sPTN ON sOBJ.object_id = sPTN.object_id WHERE sOBJ.type = 'U' AND sOBJ.is_ms_shipped = 0x0 AND index_id < 2 -- 0:Heap, 1:Clustered GROUP BY sOBJ.schema_id , sOBJ.name ORDER BY [TableName]; select * from utblListOftables The following gives the utilzation and update stats Select Object_Name(ix.[object_id]) as objectName , Sum(ddius.user_seeks) As 'table_seeks' , Sum(ddius.user_scans) As 'table_scans' , Sum(ddius.user_lookups) As 'table_lookups' , Sum(ddius.user_updates) As 'table_updates' , Sum(ddius.user_seeks + ddius.user_scans) As 'query

 I found the following function helpful when trying to figure out the days worked during COVID lock down. -- ============================================= -- Author: KrishanG -- Create date: 20/07/2022 -- Description: Get the number of working days between two dates includes these dates as well -- specify the date time as -- SET @StartDate = '2021/07/16' -- SET @EndDate = '2021/07/27' -- ============================================= CREATE FUNCTION [dbo].[fnGetWeekdays] ( -- Add the parameters for the function here @StartDate datetime, @EndDate datetime ) RETURNS int AS BEGIN -- Declare the return variable here DECLARE @Result int -- DECLARE @StartDate DATETIME -- DECLARE @EndDate DATETIME -- SET @StartDate = '2021/07/16' -- SET @EndDate = '2021/07/27' SELECT @Result= (DATEDIFF(dd, @StartDate, @EndDate) + 1) -(DATEDIFF(wk, @StartDate, @EndDate) * 2) -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END) -