script out foreign keys sql server

Solutions on MaxInterview for script out foreign keys sql server by the best coders in the world

showing results for - "script out foreign keys sql server"
Isaac
12 Jul 2017
1--- SCRIPT TO GENERATE THE CREATION SCRIPT OF ALL FOREIGN KEY CONSTRAINTS
2declare @ForeignKeyID int
3declare @ForeignKeyName varchar(4000)
4declare @ParentTableName varchar(4000)
5declare @ParentColumn varchar(4000)
6declare @ReferencedTable varchar(4000)
7declare @ReferencedColumn varchar(4000)
8declare @StrParentColumn varchar(max)
9declare @StrReferencedColumn varchar(max)
10declare @ParentTableSchema varchar(4000)
11declare @ReferencedTableSchema varchar(4000)
12declare @TSQLCreationFK varchar(max)
13--Written by Percy Reyes www.percyreyes.com
14declare CursorFK cursor for select object_id--, name, object_name( parent_object_id) 
15from sys.foreign_keys
16open CursorFK
17fetch next from CursorFK into @ForeignKeyID
18while (@@FETCH_STATUS=0)
19begin
20 set @StrParentColumn=''
21 set @StrReferencedColumn=''
22 declare CursorFKDetails cursor for
23  select  fk.name ForeignKeyName, schema_name(t1.schema_id) ParentTableSchema,
24  object_name(fkc.parent_object_id) ParentTable, c1.name ParentColumn,schema_name(t2.schema_id) ReferencedTableSchema,
25   object_name(fkc.referenced_object_id) ReferencedTable,c2.name ReferencedColumn
26  from --sys.tables t inner join 
27  sys.foreign_keys fk 
28  inner join sys.foreign_key_columns fkc on fk.object_id=fkc.constraint_object_id
29  inner join sys.columns c1 on c1.object_id=fkc.parent_object_id and c1.column_id=fkc.parent_column_id 
30  inner join sys.columns c2 on c2.object_id=fkc.referenced_object_id and c2.column_id=fkc.referenced_column_id 
31  inner join sys.tables t1 on t1.object_id=fkc.parent_object_id 
32  inner join sys.tables t2 on t2.object_id=fkc.referenced_object_id 
33  where fk.object_id=@ForeignKeyID
34 open CursorFKDetails
35 fetch next from CursorFKDetails into  @ForeignKeyName, @ParentTableSchema, @ParentTableName, @ParentColumn, @ReferencedTableSchema, @ReferencedTable, @ReferencedColumn
36 while (@@FETCH_STATUS=0)
37 begin    
38  set @StrParentColumn=@StrParentColumn + ', ' + quotename(@ParentColumn)
39  set @StrReferencedColumn=@StrReferencedColumn + ', ' + quotename(@ReferencedColumn)
40  
41     fetch next from CursorFKDetails into  @ForeignKeyName, @ParentTableSchema, @ParentTableName, @ParentColumn, @ReferencedTableSchema, @ReferencedTable, @ReferencedColumn
42 end
43 close CursorFKDetails
44 deallocate CursorFKDetails
45
46 set @StrParentColumn=substring(@StrParentColumn,2,len(@StrParentColumn)-1)
47 set @StrReferencedColumn=substring(@StrReferencedColumn,2,len(@StrReferencedColumn)-1)
48 set @TSQLCreationFK='ALTER TABLE '+quotename(@ParentTableSchema)+'.'+quotename(@ParentTableName)+' WITH CHECK ADD CONSTRAINT '+quotename(@ForeignKeyName)
49 + ' FOREIGN KEY('+ltrim(@StrParentColumn)+') '+ char(13) +'REFERENCES '+quotename(@ReferencedTableSchema)+'.'+quotename(@ReferencedTable)+' ('+ltrim(@StrReferencedColumn)+') ' + char(13)+'GO'
50 
51 print @TSQLCreationFK
52
53fetch next from CursorFK into @ForeignKeyID 
54end
55close CursorFK
56deallocate CursorFK