Thursday, September 11, 2014

PK, FK Rename and duplicate keys removal.

Well, here is the frustration with SSDT schema compare, it doesn't have options to ignore column order.
say for an instance, you have a table in one DB Tab (col1, col2) & Tab(Col2, Col1) in another DB, while you compare both these DB's Schema compare would find a difference.

also it has given some more pain with user defined data types and PK, FK order.
so I have decided to live with my VS and SSDT.

Written below script to rename primary keys and foreign keys
e.g, Vendor_Details is the table name, so I'll rename primary keys to Vendor_Details_PK and foreign keys to Vendor_Details_FK01, Vendor_Details_FK02.. etc



DECLARE @TBL_NME   AS VARCHAR(max),
        @CONST_NME AS VARCHAR(max),
        @Qry       AS NVARCHAR(max);
        
-- Renaming PK      
        
DECLARE cur_pk CURSOR FOR
  SELECT constraint_name,
         table_name
  FROM   information_schema.table_constraints
  WHERE  constraint_type = 'PRIMARY KEY'
  ORDER  BY constraint_name

OPEN cur_pk

FETCH next FROM cur_pk INTO @CONST_NME, @tbl_nme

WHILE @@FETCH_STATUS = 0
  BEGIN
      SET @Qry = 'SP_RENAME ''' + @CONST_NME + ''' , '''
                 + @TBL_NME + '_PK'''

      EXEC Sp_executesql
        @Qry;

      FETCH next FROM cur_pk INTO @CONST_NME, @tbl_nme
  END

CLOSE cur_pk

DEALLOCATE cur_pk

-- Renaming FK

DECLARE curtab CURSOR FOR
  SELECT table_name
  FROM   information_schema.tables

OPEN curtab

FETCH next FROM curtab INTO @TBL_NME

WHILE @@FETCH_STATUS = 0
  BEGIN
      DECLARE @INC AS INT = 1;
      DECLARE cur_fk CURSOR local fast_forward FOR
        SELECT constraint_name
        FROM   information_schema.table_constraints
        WHERE  table_name = @TBL_NME
               AND constraint_type = 'FOREIGN KEY'
        ORDER  BY constraint_name

      OPEN cur_fk
      FETCH next FROM cur_fk INTO @CONST_NME
           
      WHILE @@FETCH_STATUS = 0
        BEGIN
       
             SET @Qry = 'SP_RENAME ''' + @CONST_NME + ''' , '''
                         + @TBL_NME + '_FK' + RIGHT('0'+CONVERT(VARCHAR(2), @INC),2)
                         + ''';';
              begin distributed tran         
                        EXEC Sp_executesql @Qry;
              commit tran
            
            SET @INC = @INC + 1;
            WAITFOR DELAY '00:00:00:1'

             FETCH next FROM cur_fk INTO @CONST_NME
        END

      CLOSE cur_fk

      DEALLOCATE cur_fk
WAITFOR DELAY '00:00:00:1'
      FETCH next FROM curtab INTO @TBL_NME
  END

CLOSE curtab

DEALLOCATE curtab


/*

--There are some chances that you might have duplicate FK's, you can fetch them and delete with below query, Evaluate the output and run the output manually.

with CTE as (
SELECT RC.CONSTRAINT_NAME FK_Name
, KF.TABLE_NAME FK_Table
, KF.COLUMN_NAME FK_Column
, RC.UNIQUE_CONSTRAINT_NAME PK_Name
, KP.TABLE_NAME PK_Table
, KP.COLUMN_NAME PK_Column
,ROW_NUMBER() over (partition by KF.TABLE_NAME, KF.COLUMN_NAME order by RC.CONSTRAINT_NAME) as RN
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KF ON RC.CONSTRAINT_NAME = KF.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KP ON RC.UNIQUE_CONSTRAINT_NAME = KP.CONSTRAINT_NAME
)
select distinct 'alter table ' +FK_Table+ ' drop constraint '+ FK_Name + ';'from CTE
where RN>1


*/


Once you are happy with  keys, you can continue SSDT schema compare. still you will have some manual work, but minimal.

;-)
happy Querying.