Archive

Archive for November, 2014

Drop / Recreate FKs

November 7, 2014 Leave a comment

Original at: http://www.codeproject.com/Tips/740561/SQL-Script-to-Drop-and-Re-create-All-Foreign-Keys

;WITH RefColumns AS

    (
SELECT
C.referenced_object_id AS [object_id],
C.parent_object_id,
STUFF((
SELECT ', ' + QUOTENAME(B.name)
FROM sys.foreign_key_columns A
JOIN sys.columns B ON B.[object_id] = A.referenced_object_id
AND B.column_id = A.referenced_column_id
WHERE C.parent_object_id = A.parent_object_id
AND C.referenced_object_id = A.referenced_object_id
FOR XML PATH('')), 1, 2, '') AS ColumnNames
FROM sys.foreign_key_columns C
GROUP BY C.referenced_object_id, C.parent_object_id
)
,
ParentColumns AS
(
SELECT
C.parent_object_id AS [object_id],
C.referenced_object_id,
STUFF((
SELECT ', ' + QUOTENAME(B.name)
FROM sys.foreign_key_columns A
JOIN sys.columns B ON B.[object_id] = A.parent_object_id
AND B.column_id = A.parent_column_id
WHERE C.parent_object_id = A.parent_object_id
AND C.referenced_object_id = A.referenced_object_id
FOR XML PATH('')), 1, 2, '') AS ColumnNames
FROM sys.foreign_key_columns C
GROUP BY C.parent_object_id, C.referenced_object_id
)
SELECT
'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(PT.[schema_id])) + '.' +
QUOTENAME(PT.name) + ' DROP  CONSTRAINT' + ' ' + QUOTENAME(FK.name)                                                        AS [DropFKScript],
'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(PT.[schema_id])) + '.' +
QUOTENAME(PT.name) + ' WITH CHECK ADD  CONSTRAINT '+ QUOTENAME(FK.name) + CHAR(13) + CHAR(10) +
'FOREIGN KEY(' + PC.ColumnNames + ')' + CHAR(13) + CHAR(10) +
'REFERENCES ' + QUOTENAME(SCHEMA_NAME(RT.[schema_id])) + '.' +
QUOTENAME(RT.name) + ' (' + RC.ColumnNames + ')' + CHAR(13) +
CHAR(10) + 'GO' + CHAR(13) + CHAR(10) +
'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(PT.[schema_id])) + '.' +
QUOTENAME(PT.name) + ' CHECK CONSTRAINT ' + QUOTENAME(FK.name) +
CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10)                                                                            AS [CreateFKScript]
FROM sys.foreign_keys FK
JOIN sys.tables PT ON PT.[object_id] = FK.parent_object_id
JOIN ParentColumns AS PC ON PC.[object_id] = FK.parent_object_id
AND PC.referenced_object_id = FK.referenced_object_id
JOIN sys.tables RT ON RT.[object_id] = FK.referenced_object_id
JOIN RefColumns AS RC ON RC.[object_id] = FK.referenced_object_id
AND RC.parent_object_id = FK.parent_object_id
WHERE PT.name NOT IN ('dtproperties', 'sysdiagrams', '__RefactorLog')
AND
RT.name NOT IN ('dtproperties', 'sysdiagrams', '__RefactorLog')
--and (RT.name like 'Award%' or RT.name like 'MasterBreeder%' or RT.name = 'AverageRegistrationGroup')
AND RT.name LIKE 'Customer%'
ORDER BY PT.name

Damm Algorithm T-SQL Implementation

November 7, 2014 Leave a comment

Recently I ended up having to actually write my own function returning the Damm algorithm check digit in T-SQL. I was sure I would find some Damm algorithm T-SQL code snippet on the net, but no, – algo implementation code examples can be found in literally any main stream languages but T-SQL. Here comes its T-SQL version now.

Note 1:

I used input/output Old/New Number as a string to make it work with alpha-numerics of a certain format, – hence I have some padding and prefixing and postfixing of the New Number as well as fixed Old Number format, which is, again, a varchar. This can easily be changed to numerics only, with no prefixes, postfixes or any of that stuff, if needed – simply take it off and change the input/output data types

Note 2:

I used this wiki for the reference of how this algo works: http://en.wikipedia.org/wiki/Damm_algorithm

CREATE FUNCTION dbo.CheckDigitDammAlgo (@vOldNumber NVARCHAR(6))
RETURNS NVARCHAR(15)
AS
BEGIN
DECLARE
@cPaddingLength tinyint = 6,    --constant padding length
@cPadding NCHAR(6) = '000000',    --padding constrant
@cPrefix  NCHAR(3) = 'ZX_',        --prefix constrant
@cPostfix NCHAR(4) = '_712',        --postfix constrant
@vNewNumber NVARCHAR(20);        --New Number (output)
DECLARE @vDigit tinyint;                --each of six Old Number digits
DECLARE @vDigitIndex tinyint = 1;        --Old Number digit position (1 through 6)
DECLARE @vFlagDone bit = 0;                --the outer while-loop ending flag
DECLARE  @vOldInterim tinyint
,@vNewInterim tinyint
,@vColumnIndex NVARCHAR(10)
,
@vRowIndex tinyint
,@vTableEntry tinyint;
DECLARE @vArray TABLE (
interim tinyint
,zero tinyint
,one tinyint
,two tinyint
,three tinyint
,four tinyint
,five tinyint
,six tinyint
,seven tinyint
,eight tinyint
,nine tinyint
);
INSERT INTO @vArray (interim, zero, one, two, three, four, five, six, seven, eight, nine)
VALUES (0, 0, 3, 1, 7, 5, 9, 8, 6, 4, 2);
INSERT INTO @vArray (interim, zero, one, two, three, four, five, six, seven, eight, nine)
VALUES (1, 7, 0, 9, 2, 1, 5, 4, 8, 6, 3);
INSERT INTO @vArray (interim, zero, one, two, three, four, five, six, seven, eight, nine)
VALUES (2, 4, 2, 0, 6, 8, 7, 1, 3, 5, 9);
INSERT INTO @vArray (interim, zero, one, two, three, four, five, six, seven, eight, nine)
VALUES (3, 1, 7, 5, 0, 9, 8, 3, 4, 2, 6);
INSERT INTO @vArray (interim, zero, one, two, three, four, five, six, seven, eight, nine)
VALUES (4, 6, 1, 2, 3, 0, 4, 5, 9, 7, 8);
INSERT INTO @vArray (interim, zero, one, two, three, four, five, six, seven, eight, nine)
VALUES (5, 3, 6, 7, 4, 2, 0, 9, 5, 8, 1);
INSERT INTO @vArray (interim, zero, one, two, three, four, five, six, seven, eight, nine)
VALUES (6, 5, 8, 6, 9, 7, 2, 0, 1, 3, 4);
INSERT INTO @vArray (interim, zero, one, two, three, four, five, six, seven, eight, nine)
VALUES (7, 8, 9, 4, 5, 3, 6, 2, 0, 1, 7);
INSERT INTO @vArray (interim, zero, one, two, three, four, five, six, seven, eight, nine)
VALUES (8, 9, 4, 3, 8, 6, 1, 7, 2, 0, 5);
INSERT INTO @vArray (interim, zero, one, two, three, four, five, six, seven, eight, nine)
VALUES (9, 2, 5, 8, 1, 4, 3, 6, 7, 9, 0);
IF ISNUMERIC(@vOldNumber) = 0
BEGIN
RETURN
NULL;
END

--pad @vOldNumber with zeroes to six characters
SET @vOldNumber = RIGHT(@cPadding + RTRIM(@vOldNumber), @cPaddingLength)
WHILE @vFlagDone = 0
BEGIN
SET
@vOldInterim = 0 --first OldInterim is always 0 for any digit
WHILE @vDigitIndex <= @cPaddingLength
BEGIN
SET
@vDigit = CONVERT(tinyint, SUBSTRING(@vOldNumber, @vDigitIndex, 1));
SET @vColumnIndex = CASE @vDigit
WHEN 0 THEN 'zero'
WHEN 1 THEN 'one'
WHEN 2 THEN 'two'
WHEN 3 THEN 'three'
WHEN 4 THEN 'four'
WHEN 5 THEN 'five'
WHEN 6 THEN 'six'
WHEN 7 THEN 'seven'
WHEN 8 THEN 'eight'
WHEN 9 THEN 'nine'
END;
SET @vRowIndex = @vOldInterim;
SELECT  @vTableEntry = CASE @vColumnIndex
WHEN 'zero' THEN zero
WHEN 'one' THEN one
WHEN 'two' THEN two
WHEN 'three' THEN three
WHEN 'four' THEN four
WHEN 'five' THEN five
WHEN 'six' THEN six
WHEN 'seven' THEN seven
WHEN 'eight' THEN eight
WHEN 'nine' THEN nine
END
FROM    
@vArray
WHERE   interim = @vRowIndex;
SET @vOldInterim = @vTableEntry;
SET @vDigitIndex = @vDigitIndex + 1
END
SET
@vFlagDone = 1
END
SET
@vNewNumber = @cPrefix + @vOldNumber + CONVERT(VARCHAR, @vTableEntry) + @cPostfix;
RETURN @vNewNumber
END

SELECT dbo.CheckDigitDammAlgo ('1')
SELECT dbo.CheckDigitDammAlgo ('2')
SELECT dbo.CheckDigitDammAlgo ('3')
SELECT dbo.CheckDigitDammAlgo ('4')
SELECT dbo.CheckDigitDammAlgo ('5')
SELECT dbo.CheckDigitDammAlgo ('6')
SELECT dbo.CheckDigitDammAlgo ('7')
SELECT dbo.CheckDigitDammAlgo ('8')
SELECT dbo.CheckDigitDammAlgo ('9')
SELECT dbo.CheckDigitDammAlgo ('10')