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!