용어8

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