<%
main()
Sub main()
Dim ct21dbName, ct21Server,ct21Conn
ct21Server = "localhost"
ct21dbName = "sj3webdev"
ct21Conn = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sj3user;Password=sj3;Initial Catalog=" & ct21dbName & ";Data Source=" & ct21Server
Dim Query, Content, Rs
Dim Query2, Content2
Query = "SELECT name FROM sysobjects WHERE (xtype = 'U') AND (status >= 0) ORDER BY name"
Set db = Server.CreateObject("ADODB.Connection")
db.Open ct21Conn
Dim ii
Set Content = db.Execute(Query)
Dim TableName
Dim FieldName, FieldType, Precision, NumericScale, DefinedSize, Attributes
Dim HtmlAll, HtmlRow, bFalse, HtmlFalse, WriteRow
Dim ScriptFalse1, ScriptFalse2, ScriptFalse3, ScriptFalse4
Dim bTableFalseFlg
bTableFalseFlg = 0
Dim Out
Out = ""
Do While Not(Content.BOF Or Content.EOF)
TableName = Content.Fields("name")
If InStr(TableName, "ATMP_") > 0 Then
Out = Out & "DROP TABLE " & TableName & "
" & vbCrLf
End If
Content.MoveNext
Loop
Content.MoveFirst
Do While Not(Content.BOF Or Content.EOF)
TableName = Content.Fields("name")
Query2 = "SELECT * FROM " & TableName & " WHERE 1 = 0 "
Set Content2 = db.Execute(Query2)
Dim bFlg
bFlg = 0
If TableName = "TA仕入売上書類F" Then bFlg = 1 End If
If TableName = "TA仕入売上書類明細F" Then bFlg = 1 End If
If TableName = "TA手形F" Then bFlg = 1 End If
If TableName = "TB銀行取引申込書類F" Then bFlg = 1 End If
If TableName = "TC運送書類F" Then bFlg = 1 End If
If TableName = "TC運送書類明細F" Then bFlg = 1 End If
If TableName = "TC運送費請求書F" Then bFlg = 1 End If
If TableName = "TC運送費請求書手動F" Then bFlg = 1 End If
If TableName = "TC運送費請求書手動明細F" Then bFlg = 1 End If
If TableName = "TX文書F" Then bFlg = 1 End If
For ii = 0 To Content2.Fields.Count - 1
If Content2.Fields(ii).Name = "送受信区分" Then
bFlg = 0
End If
Next
If bFlg = 1 Then
Out = Out & "if not(exists (SELECT * FROM syscolumns WHERE OBJECT_NAME(id) = '" & TableName & "' AND name = N'送受信区分'))" & "
" & vbCrLf
Out = Out & "begin" & "
" & vbCrLf
Out = Out & " CREATE TABLE [dbo].[ATMP_" & TableName & "] (" & "
" & vbCrLf
For ii = 0 To Content2.Fields.Count - 1
FieldName = Content2.Fields(ii).Name
FieldType = Content2.Fields(ii).Type
Precision = Content2.Fields(ii).Precision
NumericScale = Content2.Fields(ii).NumericScale
DefinedSize = Content2.Fields(ii).DefinedSize
Attributes = Content2.Fields(ii).Attributes
If FieldName = "取引先CD" Then
Out = Out & "[作成会社CD] [varchar] (20)," & "
" & vbCrLf
End If
Out = Out & "[" & FieldName & "] [" & SetType(FieldType) & "]"
If InStr(SetType(FieldType), "int") = 0 And InStr(SetType(FieldType), "datetime") = 0 And InStr(SetType(FieldType), "money") = 0 And InStr(SetType(FieldType), "real") = 0 Then
Out = Out & " (" & DefinedSize & ")"
End If
If ii <> Content2.Fields.Count - 1 Then
Out = Out & ","
End If
Out = Out & "
" & vbCrLf
If FieldName = "会社CD" Then
Out = Out & "[送受信区分] [varchar] (2)," & "
" & vbCrLf
End If
Next
Out = Out & " ) ON [PRIMARY]" & "
" & vbCrLf
Out = Out & "
" & vbCrLf
Out = Out & "INSERT INTO ATMP_" & TableName & "
" & vbCrLf
Out = Out & "(" & vbCrLf
For ii = 0 To Content2.Fields.Count - 1
FieldName = Content2.Fields(ii).Name
If FieldName = "取引先CD" Then Out = Out & "作成会社CD," End If
Out = Out & FieldName
If ii <> Content2.Fields.Count - 1 Then
Out = Out & ","
End If
If FieldName = "会社CD" Then Out = Out & "送受信区分," End If
Next
Out = Out & ") SELECT " & vbCrLf
For ii = 0 To Content2.Fields.Count - 1
FieldName = Content2.Fields(ii).Name
If FieldName = "取引先CD" Then Out = Out & TableName & "." & "会社CD," End If
Out = Out & TableName & "." & FieldName
If ii <> Content2.Fields.Count - 1 Then
Out = Out & ","
End If
If FieldName = "会社CD" Then Out = Out & "'S'," End If
Next
Out = Out & " FROM " & TableName & "
" & vbCrLf
Out = Out & "
" & vbCrLf
Out = Out & "INSERT INTO ATMP_" & TableName & "
" & vbCrLf
Out = Out & "(" & vbCrLf
For ii = 0 To Content2.Fields.Count - 1
FieldName = Content2.Fields(ii).Name
If FieldName = "取引先CD" Then Out = Out & "作成会社CD," End If
Out = Out & FieldName
If ii <> Content2.Fields.Count - 1 Then
Out = Out & ","
End If
If FieldName = "会社CD" Then Out = Out & "送受信区分," End If
Next
Out = Out & ") SELECT " & vbCrLf
For ii = 0 To Content2.Fields.Count - 1
FieldName = Content2.Fields(ii).Name
If FieldName = "取引先CD" Then Out = Out & TableName & "." & "会社CD," End If
If FieldName = "会社CD" Then
If TableName = "TA手形F" Then
Out = Out & "受取人CD,"
Else
Out = Out & Replace(TableName, "明細", "") & "." & "取引先CD,"
End If
Else
Out = Out & TableName & "." & FieldName
If ii <> Content2.Fields.Count - 1 Then
Out = Out & ","
End If
End If
If FieldName = "会社CD" Then Out = Out & "'R'," End If
Next
Out = Out & " FROM " & TableName
If InStr(TableName, "明細") > 0 Then
Out = Out & " INNER JOIN " & Replace(TableName, "明細", "") & " ON "
Out = Out & TableName & ".ユーザーコード = " & Replace(TableName, "明細", "") & ".ユーザーコード AND "
Out = Out & TableName & ".演習CD = " & Replace(TableName, "明細", "") & ".演習CD AND "
Out = Out & TableName & ".会社CD = " & Replace(TableName, "明細", "") & ".会社CD "
End If
Out = Out & "
" & vbCrLf
Out = Out & "
" & vbCrLf
Out = Out & " UPDATE ATMP_" & TableName & " SET ユーザーコード = (SELECT ユーザーコード FROM TSシステムM) WHERE (ユーザーコード IS NULL)" & "
" & vbCrLf
Out = Out & "
" & vbCrLf
Out = Out & " DROP TABLE " & TableName & "
" & vbCrLf
Out = Out & "
" & vbCrLf
Out = Out & " EXEC sp_rename 'ATMP_" & TableName & "', '" & TableName & "'" & "
" & vbCrLf
Out = Out & "end" & "
" & vbCrLf
Out = Out & "
" & vbCrLf
Out = Out & "
" & vbCrLf
End If
Content.MoveNext
Loop
Response.Write(Out)
%>