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.

Read More

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!

Read More