SQL Server – Generate Calendar using TSQL

Introduction

Recently, I was asked to develop a SSRS based report for the Event Management module in MS Dynamics CRM 2011. The idea was to show a Calendar for the selected month and each cell of the calendar should display the scheduled events of that day.

Showing the events in the required format in each cell was not a big issue. The main challenge was to generate a dynamic grid of Calendar. Luckily, the CRM was deployed on-premises and I got a chance to use TSQL to generate the Calendar.

Implementation

Below is the TSQL which I came up with to generate the Calendar -

DECLARE @Month AS INT = 4 --Set the MONTH for which you want to generate the Calendar.

DECLARE @Year AS INT = 2013 --Set the YEAR for which you want to generate the Calendar.

--Find and set the Start & End Date of the said Month-Year

DECLARE @StartDate AS DATETIME = CONVERT(VARCHAR,@Year) + RIGHT('0' + CONVERT(VARCHAR,@Month),2) + '01'

DECLARE @EndDate AS DATETIME = DATEADD(DAY,-1,DATEADD(MONTH,1,@StartDate))

;WITH Dates AS (

  SELECT 

    @StartDate Dt

  UNION ALL

  SELECT 

    DATEADD(DAY,1,Dt) 

  FROM 

    Dates 

  WHERE 

    DATEADD(DAY,1,Dt) <= @EndDate

),Details AS (

  SELECT 

    DAY(Dt) CDay,

    DATEPART(WK,Dt) CWeek,

    MONTH(Dt) CMonth,

    YEAR(Dt) CYear,

    DATENAME(WEEKDAY,Dt) DOW,

    Dt 

  FROM 

    Dates

)

--Selecting the Final Calendar

SELECT

  Sunday,

  Monday,

  Tuesday,

  Wednesday,

  Thursday,

  Friday,

  Saturday

FROM

  (SELECT CWeek,DOW,CDay FROM Details) D

PIVOT

(

  MIN(CDay)

  FOR DOW IN (Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday)

) AS PVT

ORDER BY

  CWeek

Output:

Hope, this will help!

SQL Server – Generating PERMUTATIONS using T-Sql

Were you ever asked to generate string Permutations using TSql? I was recently asked to do so, and the logic which I could manage to come up at that point is shared in the below script.

DECLARE @Value AS VARCHAR(20) = 'ABCC' --Mention the text which is to be permuted

DECLARE @NoOfChars AS INT = LEN(@Value)

DECLARE @Permutations TABLE(Value VARCHAR(20)) --Make sure the size of this Value is equal to your input  string length (@Value)

;WITH NumTally AS (--Prepare the Tally Table to separate each character of the Value.

  SELECT 1 Num

  UNION ALL

  SELECT 

    Num + 1 

  FROM 

    NumTally 

  WHERE 

    Num < @NoOfChars

),Chars AS ( --Separate the Characters

SELECT

  Num,

  SUBSTRING(@Value,Num,1) Chr

FROM

  NumTally  

)

--Persist the Separated characters.

INSERT INTO @Permutations

SELECT Chr FROM Chars

--Prepare Permutations

DECLARE @i AS INT = 1

WHILE(@i < @NoOfChars)

BEGIN

  --Store the Permutations

  INSERT INTO @Permutations

  SELECT DISTINCT --Add DISTINCT if required else duplicate Permutations will be generated for Repeated  Chars.

    P1.Value + P2.Value

  FROM 

    (SELECT Value FROM @Permutations WHERE LEN(Value) = @i) P1 

  CROSS JOIN 

    (SELECT Value FROM @Permutations WHERE LEN(Value) = 1) P2

  --Increment the Counter.      

  SET @i += 1  

  --Delete the Incorrect Lengthed Permutations to keep the table size under control.

  DELETE FROM @Permutations WHERE LEN(Value) NOT IN (1,@i)

END

--Delete InCorrect Permutations.

SET @i = 1

WHILE(@i <= @NoOfChars)

BEGIN

  --Deleting Permutations which has not used "All the Chars of the given Value".

  DELETE 

  FROM 

    @Permutations

  WHERE

    Value NOT LIKE '%' + SUBSTRING(@Value,@i,1) +'%'

  --Deleting Permutations which have repeated incorrect character.  

  DELETE 

  FROM 

    @Permutations

  WHERE

    LEN(Value) - LEN(REPLACE(Value,SUBSTRING(@Value,@i,1),'')) != 

    LEN(@Value) - LEN(REPLACE(@Value,SUBSTRING(@Value,@i,1),''))

  SET @i += 1  

END

--Selecting the generated Permutations. 

SELECT Value FROM @Permutations

Hope, this script helps!

Please share your suggestions if you have any to improve this logic.

SQL Server – TSql to find Records matching certain criteria in all the tables of a DB.

Generally, we try to find out records matching a certain criteria from a single or few tables. However, there are times when we need to find out records matching a criteria from all the tables of a SQL Database and today I will explain you a simple way to retrieve those records.

Recently, I was asked by my colleague, who was working on a MS Dynamics CRM migration project, to let him know the records which were created after a particular date in the source. So that, he could analyze only those records and strategize the Migration process.

I quickly opened up the SSMS and came up with the below script -

USE <DBName> --Replace this with the actual DBName

Go

DECLARE @ColumnName AS VARCHAR(50) = 'CreatedOn' --The name of the column on which you need to put the criteria

DECLARE @Criteria AS VARCHAR(50) = 'CONVERT(DATE,' + @ColumnName + ') >= ''20130225''' -- The Actual criteria/WHERE Clause of the query

--The below will list the TSQL Statements which could be copied & executed in a separate query window.

SELECT 

  'IF EXISTS(SELECT 1 FROM ' + T.name + ' WHERE ' + @Criteria + ') ' +

  'SELECT ''' + T.name + ''' TableName, * FROM ' + T.name + ' WHERE ' + @Criteria 

FROM 

  sys.columns C

INNER JOIN sys.tables T

  ON T.object_id = C.object_id   

WHERE 

  C.name = @ColumnName

The above script will list down the SELECT statements which could be copied and executed in a separate query window connecting to the same Database. On execution, you will get the list of records from each table base on the specified criteria.

Hope, this helps!

SQL Server # Database Backup Report using T-SQL

Today, I am going to share few very useful scripts which will report us on Database Backup from different view points.

To get the List/History/Log of all the Successful Backups

SELECT 

  b.machine_name,

  b.server_name,

  b.database_name as DBName,

  b.backup_start_date,

  b.backup_finish_date,

  CASE 

    WHEN b.[type] = 'D' THEN 'Database'

    WHEN b.[type] = 'I' THEN 'Differential database'

    WHEN b.[type] = 'L' THEN 'Log'

    WHEN b.[type] = 'F' THEN 'File or filegroup'

    WHEN b.[type] = 'G' THEN 'Differential file'

    WHEN b.[type] = 'P' THEN 'Partial'

    WHEN b.[type] = 'Q' THEN 'Differential partial'

    ELSE b.[type]

  END Backup_Type,    

  b.expiration_date,

  b.[user_name],

  DATEDIFF(MINUTE,b.backup_start_date ,b.backup_finish_date) as Total_Time_in_Minute,

  b.recovery_model,

  b.backup_size/(1024 * 1024 * 1024) as Total_Size_GB,

  bf.physical_device_name as Location

FROM 

  msdb.dbo.backupset AS b

INNER JOIN msdb.dbo.backupmediafamily AS bf

  ON b.media_set_id=bf.media_set_id

ORDER BY 

  b.backup_start_date DESC

GO

To get a list of all successful Backups taken till date for a particular Database

DECLARE @DBName AS VARCHAR(100) = 'Your Database Name'

SELECT 

  b.machine_name,

  b.server_name,

  b.database_name as DBName,

  b.backup_start_date,

  b.backup_finish_date,

  CASE 

    WHEN b.[type] = 'D' THEN 'Database'

    WHEN b.[type] = 'I' THEN 'Differential database'

    WHEN b.[type] = 'L' THEN 'Log'

    WHEN b.[type] = 'F' THEN 'File or filegroup'

    WHEN b.[type] = 'G' THEN 'Differential file'

    WHEN b.[type] = 'P' THEN 'Partial'

    WHEN b.[type] = 'Q' THEN 'Differential partial'

    ELSE b.[type]

  END Backup_Type,

  b.expiration_date,

  b.[user_name],

  DATEDIFF(MINUTE,b.backup_start_date ,b.backup_finish_date) as Total_Time_in_Minute,

  b.recovery_model,

  b.backup_size/(1024 * 1024 * 1024) as Total_Size_GB,

  bf.physical_device_name as Location

FROM 

  msdb.dbo.backupset AS b

INNER JOIN msdb.dbo.backupmediafamily AS bf

  ON b.media_set_id=bf.media_set_id

WHERE

  b.database_name = @DBName  

ORDER BY 

  b.backup_start_date DESC

GO

To get the List of all Databases which are not backed up till date

SELECT

  d.name [DB_Name]

FROM

  master.sys.databases d

LEFT JOIN msdb.dbo.backupset b

  ON b.database_name = d.name

WHERE

  d.database_id IS NULL

To get the List of all Databases which are not backed up since last X days

DECLARE @LastXDays AS INT = 1

;WITH LatestBackupSet AS (

SELECT 

  b.database_name as DBName,

  b.backup_start_date LastBackedUpOn,

  b.[user_name],

  ROW_NUMBER() OVER(PARTITION BY b.database_name ORDER BY b.backup_start_date DESC) Rnk

FROM 

  msdb.dbo.backupset AS b

)

SELECT 

  lbs.DBName,

  lbs.LastBackedUpOn,

  lbs.[user_name]

FROM 

  LatestBackupSet AS lbs

WHERE

  DATEDIFF(DAY,lbs.LastBackedUpOn ,CURRENT_TIMESTAMP) = @LastXDays  

  AND lbs.Rnk = 1

ORDER BY 

  lbs.DBName DESC

GO

To get a list of the Latest successful backups of all Databases

;WITH LatestBackupSet AS (

SELECT 

  b.machine_name,

  b.server_name,

  b.database_name as DBName,

  b.backup_start_date,

  b.backup_finish_date,

  CASE 

    WHEN b.[type] = 'D' THEN 'Database'

    WHEN b.[type] = 'I' THEN 'Differential database'

    WHEN b.[type] = 'L' THEN 'Log'

    WHEN b.[type] = 'F' THEN 'File or filegroup'

    WHEN b.[type] = 'G' THEN 'Differential file'

    WHEN b.[type] = 'P' THEN 'Partial'

    WHEN b.[type] = 'Q' THEN 'Differential partial'

    ELSE b.[type]

  END Backup_Type,

  b.expiration_date,

  b.[user_name],

  DATEDIFF(MINUTE,b.backup_start_date ,b.backup_finish_date) as Total_Time_in_Minute,

  b.recovery_model,

  b.backup_size/(1024 * 1024 * 1024) as Total_Size_GB,

  bf.physical_device_name as Location,

  ROW_NUMBER() OVER(PARTITION BY b.database_name ORDER BY b.backup_start_date DESC) Rnk

FROM 

  msdb.dbo.backupset AS b

INNER JOIN msdb.dbo.backupmediafamily AS bf

  ON b.media_set_id=bf.media_set_id

)

SELECT 

  machine_name,

  server_name,

  DBName,

  backup_start_date,

  backup_finish_date,

  Backup_Type,

  expiration_date,

  [user_name],

  Total_Time_in_Minute,

  recovery_model,

  Total_Size_GB,

  Location

FROM 

  LatestBackupSet AS lbs

WHERE

  lbs.Rnk = 1

ORDER BY 

  lbs.DBName DESC

GO

To get the Latest successful backup of a particular Database

DECLARE @DBName AS VARCHAR(100) = 'Your Database Name'

;WITH LatestBackupSet AS (

SELECT 

  b.machine_name,

  b.server_name,

  b.database_name as DBName,

  b.backup_start_date,

  b.backup_finish_date,

  CASE 

    WHEN b.[type] = 'D' THEN 'Database'

    WHEN b.[type] = 'I' THEN 'Differential database'

    WHEN b.[type] = 'L' THEN 'Log'

    WHEN b.[type] = 'F' THEN 'File or filegroup'

    WHEN b.[type] = 'G' THEN 'Differential file'

    WHEN b.[type] = 'P' THEN 'Partial'

    WHEN b.[type] = 'Q' THEN 'Differential partial'

    ELSE b.[type]

  END Backup_Type,

  b.expiration_date,

  b.[user_name],

  DATEDIFF(MINUTE,b.backup_start_date ,b.backup_finish_date) as Total_Time_in_Minute,

  b.recovery_model,

  b.backup_size/(1024 * 1024 * 1024) as Total_Size_GB,

  bf.physical_device_name as Location,

  ROW_NUMBER() OVER(PARTITION BY b.database_name ORDER BY b.backup_start_date DESC) Rnk

FROM 

  msdb.dbo.backupset AS b

INNER JOIN msdb.dbo.backupmediafamily AS bf

  ON b.media_set_id=bf.media_set_id

WHERE

  b.database_name = @DBName  

)

SELECT 

  machine_name,

  server_name,

  DBName,

  backup_start_date,

  backup_finish_date,

  Backup_Type,

  expiration_date,

  [user_name],

  Total_Time_in_Minute,

  recovery_model,

  Total_Size_GB,

  Location

FROM 

  LatestBackupSet AS lbs

WHERE

  lbs.Rnk = 1

ORDER BY 

  lbs.DBName DESC

GO

To get a list of Databases that were backed-up and do not currently exist

SELECT

  DISTINCT b.database_name

FROM

  msdb.dbo.backupset b

WHERE

  DB_ID(b.database_name) IS NULL

Hope, the above given script will be of help to you. Also, I would like to request you to please add any relevant script which you feel would be useful as a comment.