Sub 컬럼ID_FK_FK참조()
Dim rngData8 As Range
Dim rngData81 As Range
Dim rngData82 As Range
Dim rng8 As Range
Dim rng81 As Range
Dim rng82 As Range
Dim RowC As Long
'Dim T As String
'Dim T1 As String
'Dim T2 As String
Dim T3 As String
Dim S As Long
Dim S1 As Long
Dim S2 As Long
Dim S3 As Long
Dim S4 As Long
Dim S5 As Long
Dim S6 As Long
Dim S7 As Long
Dim S8 As Long
'Application.ScreenUpdating = False
S8 = 0
ActiveWorkbook.Worksheets("테이블컬럼내역").Activate
If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
S = Sheets("테이블컬럼내역").Range("A1").CurrentRegion.Rows.Count
ActiveWorkbook.Worksheets("테이블컬럼내역").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("테이블컬럼내역").Sort.SortFields.Add Key:=Range("C2:C" & S) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("테이블컬럼내역").Sort.SortFields.Add Key:=Range("E2:E" & S) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("테이블컬럼내역").Sort
.SetRange Range("A1:I" & S)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWorkbook.Worksheets("테이블컬럼내역").Range("A1").AutoFilter Field:=7, Criteria1:="YES"
'ActiveSheet.Range("A1:I" & S).AutoFilter Field:=8, Criteria1:="NO"
' 필터링 후 레코드 수 : S7 = Sheets("테이블컬럼내역").AutoFilter.Range.Columns(8).SpecialCells(xlCellTypeVisible).Cells.Count - 1
'
S1 = Sheets("테이블컬럼내역").Range("A1").CurrentRegion.Rows.Count
Sheets("테이블컬럼내역").Range("A1:I" & S1).Select
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = "FK점검"
Sheets("FK점검").Range("A1").Select
Sheets("FK점검").Paste
S6 = Sheets("FK점검").Range("A1").CurrentRegion.Rows.Count
If S6 = 1 Then
Worksheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = "FK참조"
Sheets("FK참조").Range("A1") = "NO"
Sheets("FK참조").Range("B1") = "테이블명"
Sheets("FK참조").Range("C1") = "테이블ID"
Sheets("FK참조").Range("D1") = "컬럼명"
Sheets("FK참조").Range("E1") = "컬럼ID"
Sheets("FK참조").Range("F1") = "데이터타입"
Sheets("FK참조").Range("G1") = "PK"
Sheets("FK참조").Range("H1") = "FK"
Sheets("FK참조").Range("I1") = "널"
Sheets("FK참조").Range("K1") = "오류구분"
Sheets("FK참조").Range("L1") = "부모테이블/컬럼"
Sheets("FK참조").Range("A1:L1").Select
'Sheets("FK참조").Range(Selection, Selection.End(xlToRight)).Select
Call 선가운데정렬색채우기글씨체크기굵기
GoTo AA
ElseIf S6 = 2 Then
Range("J2").Select
ActiveCell.FormulaR1C1 = "PK"
ElseIf S6 > 2 Then
Range("J2").Select
ActiveCell.FormulaR1C1 = "PK"
Selection.AutoFill Destination:=Range("J2:J" & S6)
Range("J2:J" & S6).Select
ActiveWindow.SmallScroll Down:=6
Range("J2").Select
End If
Sheets("테이블컬럼내역").Select
Selection.AutoFilter
Sheets("FK점검").Activate
S4 = Sheets("FK점검").Range("A1").CurrentRegion.Rows.Count
If S4 = 2 Then
Range("K2").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-8],RC[-6])"
ElseIf S4 > 2 Then
Range("K2").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-8],RC[-6])"
Selection.AutoFill Destination:=Range("K2:K" & S4)
Range("K2:K" & S4).Select
ActiveWindow.SmallScroll Down:=6
Range("K2").Select
End If
Sheets("테이블컬럼내역").Activate
S5 = Sheets("테이블컬럼내역").Range("A1").CurrentRegion.Rows.Count
If S5 = 2 Then
Range("J2").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-7],RC[-5])"
ElseIf S5 > 2 Then
Range("J2").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-7],RC[-5])"
Selection.AutoFill Destination:=Range("J2:J" & S5)
Range("J2:J" & S5).Select
ActiveWindow.SmallScroll Down:=6
Range("J2").Select
End If
Worksheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = "FK참조"
Sheets("FK참조").Range("A1") = "NO"
Sheets("FK참조").Range("B1") = "테이블명"
Sheets("FK참조").Range("C1") = "테이블ID"
Sheets("FK참조").Range("D1") = "컬럼명"
Sheets("FK참조").Range("E1") = "컬럼ID"
Sheets("FK참조").Range("F1") = "데이터타입"
Sheets("FK참조").Range("G1") = "PK"
Sheets("FK참조").Range("H1") = "FK"
Sheets("FK참조").Range("I1") = "널"
Sheets("FK참조").Range("K1") = "오류구분"
Sheets("FK참조").Range("L1") = "부모테이블/컬럼"
Sheets("FK참조").Range("A1:L1").Select
'Sheets("FK참조").Range(Selection, Selection.End(xlToRight)).Select
Call 선가운데정렬색채우기글씨체크기굵기
S4 = Sheets("FK점검").Range("A1").CurrentRegion.Rows.Count
With Sheets("FK점검")
If S4 = 2 Then
Set rngData8 = .Range("K2")
ElseIf S4 > 2 Then
Set rngData8 = .Range(.Range("K2"), .Range("K2").End(xlDown))
End If
End With
For Each rng8 In rngData8
With Sheets("테이블컬럼내역")
Set rngData81 = .Range(.Range("J2"), .Range("J2").End(xlDown))
End With
For Each rng81 In rngData81
If UCase(Trim(rng8.Offset(0, -1))) = "PK" Then
If UCase(Trim(rng8)) <> UCase(Trim(rng81)) Then
If UCase(Trim(rng8.Offset(0, -6))) = UCase(Trim(rng81.Offset(0, -5))) And UCase(Trim(rng81.Offset(0, -2))) = "NO" Then
Sheets("테이블컬럼내역").Activate
Sheets("테이블컬럼내역").Range("A" & rng81.Row).Select
Sheets("테이블컬럼내역").Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("FK참조").Activate
RowC = Sheets("FK참조").Range("A1").CurrentRegion.Rows.Count
Sheets("FK참조").Activate
Sheets("FK참조").Range("A" & RowC + 1).Select
Sheets("FK참조").Paste
Sheets("FK참조").Range("K" & RowC + 1) = "FK 미정의"
Sheets("FK참조").Range("L" & RowC + 1) = rng8.Offset(0, -8).Rows.Value & "/" & rng8.Offset(0, -6).Rows.Value
End If
End If
End If
Next
Next
Sheets("FK참조").Range("A2").Select
Sheets("FK참조").Range(Selection, Selection.End(xlDown)).Select
Sheets("FK참조").Range(Selection, Selection.End(xlToRight)).Select
Call 글씨체크기줄바꿈
Call 글씨체가늘게
Call 바깥쪽테두리
AA:
ActiveWorkbook.Worksheets("테이블컬럼내역").Activate
ActiveWorkbook.Worksheets("테이블컬럼내역").Range("A1:I1").Select
Selection.AutoFilter
ActiveSheet.Range("A1:I" & S).AutoFilter Field:=8, Criteria1:="YES"
S2 = Sheets("테이블컬럼내역").Range("A1").CurrentRegion.Rows.Count
Range("A1:I" & S2).Select
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = "PK점검2"
Sheets("PK점검2").Range("A1").Select
Sheets("PK점검2").Paste
S3 = Sheets("PK점검2").Range("A1").CurrentRegion.Rows.Count
If S3 = 1 Then
GoTo BB
ElseIf S3 = 2 Then
Range("J2").Select
ActiveCell.FormulaR1C1 = "FK"
ElseIf S3 > 2 Then
Range("J2").Select
ActiveCell.FormulaR1C1 = "FK"
Selection.AutoFill Destination:=Range("J2:J" & S3)
Range("J2:J" & S3).Select
ActiveWindow.SmallScroll Down:=6
Range("J2").Select
End If
Sheets("테이블컬럼내역").Select
Selection.AutoFilter
Sheets("PK점검2").Activate
S4 = Sheets("PK점검2").Range("A1").CurrentRegion.Rows.Count
If S4 = 2 Then
Range("K2").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-8],RC[-6])"
ElseIf S4 > 2 Then
Range("K2").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-8],RC[-6])"
Selection.AutoFill Destination:=Range("K2:K" & S4)
Range("K2:K" & S4).Select
ActiveWindow.SmallScroll Down:=6
Range("K2").Select
End If
With Sheets("FK점검")
If S4 = 2 Then
Set rngData8 = .Range("K2")
ElseIf S4 > 2 Then
Set rngData8 = .Range(.Range("K2"), .Range("K2").End(xlDown))
End If
End With
With Sheets("PK점검2")
If S4 = 2 Then
Set rngData82 = .Range("K2")
ElseIf S4 > 2 Then
Set rngData82 = .Range(.Range("K2"), .Range("K2").End(xlDown))
End If
End With
For Each rng82 In rngData82
For Each rng8 In rngData8
If UCase(Trim(rng82)) = UCase(Trim(rng8)) Then
T3 = "FK참조PK미정의"
Else
If UCase(Trim(rng82.Offset(0, -6))) = UCase(Trim(rng8.Offset(0, -6))) Then
T3 = "FK참조PK정의"
Else
T3 = "FK참조PK미정의"
End If
End If
If T3 = "FK참조PK정의" Then Exit For
Next
If T3 = "FK참조PK미정의" Then
Sheets("PK점검2").Activate
Sheets("PK점검2").Range("A" & rng82.Row).Select
Sheets("PK점검2").Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("FK참조").Activate
RowC = Sheets("FK참조").Range("A1").CurrentRegion.Rows.Count
Sheets("FK참조").Activate
Sheets("FK참조").Range("A" & RowC + 1).Select
Sheets("FK참조").Paste
If S8 = 0 Then
S8 = RowC + 1
End If
Sheets("FK참조").Range("K" & RowC + 1) = "FK참조PK미정의"
Sheets("FK참조").Range("L" & RowC + 1) = "없음"
End If
Next
BB:
ActiveWorkbook.Worksheets("FK참조").Range("J:J").Delete
Sheets("FK참조").Activate
Range("A1").Select
Application.DisplayAlerts = False
Sheets("PK점검2").Select
ActiveWindow.SelectedSheets.Delete
Sheets("FK점검").Select
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
ActiveWorkbook.Worksheets("테이블컬럼내역").Range("J:J").Delete
If S8 <> 0 Then
Sheets("FK참조").Range("A" & S8).Select
Sheets("FK참조").Range(Selection, Selection.End(xlDown)).Select
Sheets("FK참조").Range(Selection, Selection.End(xlToRight)).Select
Call 글씨체크기줄바꿈
Call 글씨체가늘게
Call 바깥쪽테두리
End If
'Application.ScreenUpdating = True
End Sub