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
Once you are happy with keys, you can continue SSDT schema compare. still you will have some manual work, but minimal.
;-)
happy Querying.
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
*/
Good one
ReplyDelete