<%
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 = 1
For ii = 0 To Content2.Fields.Count - 1
If Content2.Fields(ii).Name = "ユーザーコード" Then
bFlg = 0
End If
Next
If TableName = "TSシステムM" Or InStr(TableName, "ATMP_") > 0 Then
bFlg = 0
End If
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
Out = Out & "[ユーザーコード] [varchar] (12) NULL ," & "
" & 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
Out = Out & "[" & FieldName & "] [" & SetType(FieldType) & "]"
If InStr(SetType(FieldType), "int") = 0 And InStr(SetType(FieldType), "datetime") = 0 And InStr(SetType(FieldType), "money") = 0 Then
Out = Out & " (" & DefinedSize & ")"
End If
If ii <> Content2.Fields.Count - 1 Then
Out = Out & ","
End If
Out = Out & "
" & vbCrLf
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
Out = Out & FieldName
If ii <> Content2.Fields.Count - 1 Then
Out = Out & ","
End If
Next
Out = Out & ") SELECT " & vbCrLf
For ii = 0 To Content2.Fields.Count - 1
FieldName = Content2.Fields(ii).Name
Out = Out & FieldName
If ii <> Content2.Fields.Count - 1 Then
Out = Out & ","
End If
Next
Out = Out & " FROM " & TableName & "
" & 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)
%>