April 7, 2010 at 3:48 am
Im trying (and failing) to find all FK's in a database that dont match a convention of
FK_childtable_parenttable
Anyways ... Ive had a stab at it but it seems to have problems .. if a table has a correctly named key and an incorrectly named one it returns both.. I only want a list of keys that break the aforementioned naming convention !! help !
If this is a bad way of doing it let me know .
many thanks
simon
p.s Im ignoring that fact two tables can have have multiple FK's... for now !
DECLARE
@FKname varchar(255),
@tablename varchar(255),
@refname varchar(255),
@ref_table_name varchar(255),
@partvarchar(255),
@part2varchar(255),
@wholevarchar(255),
@ctrl CHAR (2),
@lenint
SET @ctrl = CHAR (13) + CHAR (10)
DECLARE DBCUR CURSOR FOR
select OBJECT_NAME(parent_object_id)as table_name,name as fk_name,object_name(referenced_object_id) as ref_table_name
from sys.foreign_keys
where OBJECT_NAME(parent_object_id) <> 'dtproperties'
order by 1
OPEN DBCUR
FETCH NEXT FROM DBCUR INTO @tablename ,@FKname ,@ref_table_name
WHILE @@FETCH_STATUS = 0
BEGIN
if SUBSTRING (@FKname,1,3) <> 'FK_'
print @FKname+ 'incorrectly named'
else
set @part= ltrim(rtrim(@FKname))
set @part2= ltrim(rtrim(@FKname))
set @part= substring(@part,4,255)
set @len= LEN(@tablename)
set @part= substring(@part,1,@len)
if @tablename <> @part
print 'part of the key incorrectly named '
else
set @len = LEN(@tablename)
set@len= @len+2
set @part2 = REPLACE(@part2,'FK_','')
set @part2 = substring(@part2,@len,255)
if @ref_table_name <> @part2
print 'part of the key incorrectly named '
else
set @whole = 'FK_'+@part+'_'+@part2
if @FKname <> @whole
begin
PRINT@tablename
print@ref_table_name
print@whole
print@FKname
print'---------------------------------------------------------------------'
end
FETCH NEXT FROM DBCUR INTO @tablename ,@FKname ,@ref_table_name
END
CLOSE DBCUR
DEALLOCATE DBCUR
GO
April 7, 2010 at 4:18 am
Something like the following might point you in the right direction...
SELECT [schema_name] = SCHEMA_NAME(SFK.[schema_id]),
current_name = SFK.name,
expected_name =
N'FK' +
N'_' +
OBJECT_NAME(SFK.parent_object_id) +
N'_' +
OBJECT_NAME(SFK.referenced_object_id)
FROM sys.foreign_keys SFK
WHERE SFK.type_desc = N'FOREIGN_KEY_CONSTRAINT'
AND SFK.is_ms_shipped = 0
AND SFK.name <> N'FK' +
N'_' +
OBJECT_NAME(SFK.parent_object_id) +
N'_' +
OBJECT_NAME(SFK.referenced_object_id)
ORDER BY
[schema_name],
current_name;
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 7, 2010 at 4:30 am
many thanks Paul ... god I was making life complicated with my code...
~simon
April 7, 2010 at 4:32 am
Simon_L (4/7/2010)
many thanks Paul ... god I was making life complicated with my code...
No worries, Simon! 😎
Resist the evil lures of the cursor!
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply