Need nudge with logic to use in dynamic SQL statement to delete duplicate FKs

Question

Post reply

Need nudge with logic to use in dynamic SQL statement to delete duplicate FKs

KoldCoffee

SSCoach

Points: 18971
More actions
November 8, 2011 at 11:31 am

#247486

Can someone give me a nudge with the logic to use to identify only the FKs with trailing numbers? I think I need an expression that will identify a string with a number on the end of it...but I can't find such an expression.
I am trying to delete only *duplicate* FKs in my database. Thanks to Michael Sondergaards query I have a way to identify and label them with a trailing number.
EG.
FK_Schema.Table.Column_Schema.Table_Column
FK_Schema.Table.Column_Schema.Table_Column2
FK_Schema.Table.Column_Schema.Table_Column3
(I want to delete only FKs 2 and 3). Below is dynamic sql which deletes ALL FKs, so I need to refine it.
/* SUMMARY
(1)this dynamic SQL deletes all FKs in the database!
*/
---retrieves all FKs and puts in temporary table called @FK
USE MGONextGen
GO
DECLARE @FK varchar(max)
DECLARE getFKName CURSOR FOR
SELECT distinct
RC.Constraint_Name
FROM
information_schema.referential_constraints RC JOIN
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU ON
RC.CONSTRAINT_NAME = CCU.CONSTRAINT_NAME JOIN
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU2 ON
RC.UNIQUE_CONSTRAINT_NAME = CCU2.CONSTRAINT_NAME
OPEN getFKName
FETCH NEXT
FROM getFKName INTO @FK
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
BEGIN TRANSACTION
declare @fk_name varchar (max)
declare @Table_Name varchar (max)
set @fk_name = @FK
SELECT distinct @Table_Name = RC.Constraint_Schema + '.' + CCU.Table_Name
FROM
information_schema.referential_constraints RC JOIN
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU ON
RC.CONSTRAINT_NAME = CCU.CONSTRAINT_NAME JOIN
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU2 ON
RC.UNIQUE_CONSTRAINT_NAME = CCU2.CONSTRAINT_NAME
WHERE RC.Constraint_Name = @FK
print @Table_Name
print @fk_name
---- deletes FKs fetched by cursor
DECLARE @sql nvarchar(4000)
SET @sql = 'ALTER TABLE ' + @Table_Name + ' DROP CONSTRAINT ' + @FK
EXEC(@sql)
COMMIT TRAN
END TRY
BEGIN CATCH
print 'Catch'
IF ( XACT_STATE() !=0)
ROLLBACK TRAN;
SELECT @FK + ERROR_MESSAGE() AS [drop failed];
END CATCH
FETCH NEXT
FROM getFKName INTO @FK
END
CLOSE getFKName
DEALLOCATE getFKName

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

KoldCoffee SSCoach Points: 18971 More actions · Answer 1

I think this will work

where RC.Constraint_Name LIKE '%[0-9]%'

Jeff Moden SSC Guru Points: 1004367 More actions · Answer 2

That's a start. Are you sure you have no FKs with intermediate digits? Also, have you verified by definiton that the FKs are actually duplicates?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

KoldCoffee SSCoach Points: 18971 More actions · Answer 3

As far as having identified the duplicates, I think Michael Sondergaards sproc works really well for that and I have examined the results. Works good.

But, I agree with the reservations. I made no accommodation for FKs comprised of column names that may end with a number. I would like my expression to be more refined so that it recognizes a defacto duplicate, which is going to be two FK names that are exactly the same, and different by only a trailing number. If there are more than 9 duplicates than I need to accommodate a trailing two digit number. This sounds like overkill to some, but Murphey's law....

Any ideas?

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

Why not use ROW_NUMBER() instead of appending a number to the end of the name? Then you delete where row_number > 1

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jeff Moden SSC Guru Points: 1004367 More actions · Answer 5

hxkresl (11/9/2011)
As far as having identified the duplicates, I think Michael Sondergaards sproc works really well for that and I have examined the results. Works good.

You sure about that? I could be wrong but from where I sit, it would appear that the only thing Michael's code looks for is duplicate names. What about duplicate FK's that have different names?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lowell SSC Guru Points: 323518 More actions · Answer 6

here's what i use, that finds FK duplicates regardless of any naming conventions...it uses row_number as suggested:

it also assumes only simple, single column FK, and not multi column FK's

SELECT

'ALTER TABLE ' + FKTABLE + ' DROP CONSTRAINT '+ CONSTRAINTNAME AS CMD,*

FROM (

SELECT

row_number() over (partition by FKTABLE,FKCOLUMN order by FKTABLE) as RW,*

FROM (

SELECT

OBJECT_NAME(constid) AS CONSTRAINTNAME,

OBJECT_NAME(rkeyid) AS REFTABLE,

COL_NAME(rkeyid,rkey) AS REFCOLUMN,

OBJECT_NAME(fkeyid) AS FKTABLE,

COL_NAME(fkeyid,fkey) AS FKCOLUMN,

' ALTER TABLE ' + OBJECT_NAME(fkeyid)

+ ' ADD CONSTRAINT ' + OBJECT_NAME(constid)

+ ' FOREIGN KEY (' + COL_NAME(fkeyid,fkey)

+ ') REFERENCES ' + OBJECT_NAME(rkeyid)

+'(' + COL_NAME(rkeyid,rkey) + ')' AS FKSQL

from sysforeignkeys

) x

) Y

WHERE RW > 1

ORDER BY FKTABLE,RW

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

KoldCoffee SSCoach Points: 18971 More actions · Answer 7

Hi Jeff. Yep, his sproc identifies duplicate keys, not duplicate names. You can look at it as I've provided the link.

KoldCoffee SSCoach Points: 18971 More actions · Answer 8

Thanks Jack Corbett, I'll look at this row number solution. Appreciate it.

KoldCoffee SSCoach Points: 18971 More actions · Answer 9

Thank Lowell! Thanks for the demo on how to use rownumber with rank as more sophisticated alternative to my expression. Appreciate it.

Michael Sondergaards sproc is really smart. It identifies multicolumn FKs as well as single column. It is really sweet.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 10

hxkresl (11/9/2011)
Hi Jeff. Yep, his sproc identifies duplicate keys, not duplicate names. You can look at it as I've provided the link.

I don't see a link anywhere..

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jeff Moden SSC Guru Points: 1004367 More actions · Answer 11

hxkresl (11/9/2011)
Hi Jeff. Yep, his sproc identifies duplicate keys, not duplicate names. You can look at it as I've provided the link.

I agree with Jack Corbett. I might simply be missing it but I don't see a link anywhere.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

KoldCoffee SSCoach Points: 18971 More actions · Answer 12

Hi there. Sorry, here it is

http://www.sqlservercentral.com/scripts/constraints/71340/ .

Jeff Moden SSC Guru Points: 1004367 More actions · Answer 13

hxkresl (11/10/2011)
Hi there. Sorry, here it is
http://www.sqlservercentral.com/scripts/constraints/71340/ .

Ah... got it. Thanks. My previous comments were based on the code you posted.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)