Ierobežojumu dzēšanas un izveides skripta ģenerācija

Ierobežojumi (Constraints) tiek izmantoti datu integritātes nodrošināšanai. Bet ir dažas lietas, ko nevar ar tabulu izdarīt, ja uz to ir reference no citas tabulas ārējās atslēgas (Foreign Keys). Piemēram, man bija vajadzība pārveidot klasterēto indeksu tā, lai tas izmantotu citas kolonas nekā primārās atslēgas ierobežojums. Lai to izdarītu nepieciešams dzēst visas ārējās atslēgas, veikt izmaiņas un izveidot ārējās atslēgas no jauna.

Pavisam ātra meklēšana google aizveda mani līdz Pinal Dave lapai, kurā tiek piedāvāts risinājums- Generate Foreign Key Scripts For Database.

Problēma ar attiecīgo skriptu ir tāda, ka netiek ņemtas vērā shēmas, kas man ir aktuāli un arī rezultāti netiek izvadīti man vēlamā formā, vēlējos arī DROP skriptus. Un man arī interesēja konkrēta tabula, nevis visa datu bāze.

Tādēļ zemāk mazliet mainīts variants (skriptā izmantoju PrintMax procedūru- Print komandas teksta garuma ierobežojums).

Set NoCount ON;

DECLARE @TABLE_NAME NVARCHAR(128) = 'TableName'

CREATE TABLE #Temp
(
    PKTABLE_QUALIFIER NVARCHAR(128),
    PKTABLE_OWNER NVARCHAR(128),
    PKTABLE_NAME NVARCHAR(128),
    PKCOLUMN_NAME NVARCHAR(128),
    FKTABLE_QUALIFIER NVARCHAR(128),
    FKTABLE_OWNER NVARCHAR(128),
    FKTABLE_NAME NVARCHAR(128),
    FKCOLUMN_NAME NVARCHAR(128),
    KEY_SEQ INT,
    UPDATE_RULE INT,
    DELETE_RULE INT,
    FK_NAME NVARCHAR(128),
    PK_NAME NVARCHAR(128),
    DEFERRABILITY INT
)

DECLARE @FKName NVARCHAR(128)
DECLARE @FKColumnName NVARCHAR(128)
DECLARE @PKColumnName NVARCHAR(128)
DECLARE @fTableName NVARCHAR(128)
DECLARE @fUpdateRule INT
DECLARE @fDeleteRule INT
DECLARE @FieldNames NVARCHAR(500)

DECLARE @create_sql NVARCHAR(MAX) = '';
DECLARE @drop_sql NVARCHAR(MAX) = '';

INSERT #Temp
    EXEC dbo.sp_fkeys @TABLE_NAME

SET @FieldNames = ''
SET @fTableName = ''

SELECT DISTINCT FK_NAME AS FKName, FKTABLE_NAME AS FTName,
    @FieldNames AS FTFields, PKTABLE_NAME AS STName,
    @FieldNames AS STFields, @FieldNames AS FKType, FKTABLE_OWNER, PKTABLE_OWNER
INTO #Temp1
FROM #Temp
ORDER BY FK_NAME,FKTABLE_NAME,PKTABLE_NAME

DECLARE FK_CUSROR CURSOR FOR
    SELECT FKName
    FROM #Temp1
   
OPEN FK_CUSROR
FETCH FROM FK_CUSROR INTO @FKName
WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE FK_FIELDS_CUSROR CURSOR FOR
        SELECT FKCOLUMN_NAME,PKCOLUMN_NAME,UPDATE_RULE,DELETE_RULE
        FROM #TEMP
        WHERE FK_NAME = @FKName
        ORDER BY KEY_SEQ
       
    OPEN FK_FIELDS_CUSROR
    FETCH FROM FK_FIELDS_CUSROR INTO @FKColumnName,@PKColumnName,@fUpdateRule,@fDeleteRule
    WHILE @@FETCH_STATUS = 0
    BEGIN
   
        UPDATE #Temp1 SET FTFields = CASE WHEN LEN(FTFields) = 0 THEN '['+@FKColumnName+']'
            ELSE FTFields + ',[' + @FKColumnName + ']' END
        WHERE FKName = @FKName
   
        UPDATE #Temp1 SET STFields = CASE WHEN LEN(STFields) = 0 THEN '['+@PKColumnName+']'
            ELSE STFields+',['+@PKColumnName+']' END
        WHERE FKName = @FKName
        FETCH NEXT FROM FK_FIELDS_CUSROR INTO @FKColumnName,@PKColumnName, @fUpdateRule,@fDeleteRule
    END
   
    UPDATE #Temp1 SET FKType = CASE WHEN @fUpdateRule = 0
        THEN FKType + ' ON UPDATE CASCADE' ELSE FKType END
    WHERE FKName = @FKName
   
    UPDATE #Temp1 SET FKType = CASE WHEN @fDeleteRule = 0
        THEN FKType + ' ON DELETE CASCADE' ELSE FKType END
    WHERE FKName = @FKName
   
    CLOSE FK_FIELDS_CUSROR
    DEALLOCATE FK_FIELDS_CUSROR
    FETCH next FROM FK_CUSROR INTO @FKName
END

CLOSE FK_CUSROR
DEALLOCATE FK_CUSROR

SELECT @drop_sql += 'ALTER TABLE ['+FKTABLE_OWNER+'].['+FTName+'] DROP
    CONSTRAINT ['+FKName+']' + Char(10) + Char(13)
FROM #Temp1

SELECT @create_sql += 'ALTER TABLE ['+FKTABLE_OWNER+'].['+FTName+'] ADD
    CONSTRAINT ['+FKName+'] FOREIGN KEY ('+FTFields+')
    REFERENCES ['+PKTABLE_OWNER+'].['+STName+'] ('+STFields+') '+FKType + Char(10) + Char(13)
FROM #Temp1

Print '/* DROP SCRIPTS */'
EXEC PrintMax @drop_sql
Print '/* CREATE SCRIPTS COMES NEXT*/'
EXEC PrintMax @create_sql

Drop Table #Temp
Drop Table #Temp1