<!-- #include file="../include.asp" -->
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=shift_jis">
<meta name="robots" content="all">
<title><% = AppTitle %></title>
</head>
<%
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 & "<br>" & 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'ユーザーコード'))" & "<br>" & vbCrLf
Out = Out & "begin" & "<br>" & vbCrLf
Out = Out & " CREATE TABLE [dbo].[ATMP_" & TableName & "] (" & "<br>" & vbCrLf
Out = Out & "[ユーザーコード] [varchar] (12) NULL ," & "<br>" & 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 & "<br>" & vbCrLf
Next
Out = Out & " ) ON [PRIMARY]" & "<br>" & vbCrLf
Out = Out & "<br>" & vbCrLf
Out = Out & "INSERT INTO ATMP_" & TableName & "<br>" & 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 & "<br>" & vbCrLf
Out = Out & "<br>" & vbCrLf
Out = Out & " UPDATE ATMP_" & TableName & " SET ユーザーコード = (SELECT ユーザーコード FROM TSシステムM) WHERE (ユーザーコード IS NULL)" & "<br>" & vbCrLf
Out = Out & "<br>" & vbCrLf
Out = Out & " DROP TABLE " & TableName & "<br>" & vbCrLf
Out = Out & "<br>" & vbCrLf
Out = Out & " EXEC sp_rename 'ATMP_" & TableName & "', '" & TableName & "'" & "<br>" & vbCrLf
Out = Out & "end" & "<br>" & vbCrLf
Out = Out & "<br>" & vbCrLf
Out = Out & "<br>" & vbCrLf
End If
Content.MoveNext
Loop
Response.Write(Out)
%>
</body>
</html>
<%
End Sub
Function SetType(ii)
Select Case ii
case 0
SetType = "adEmpty"
case 16
SetType = "adTinyInt"
case 2
SetType = "smallint"
case 3
SetType = "int"
case 20
SetType = "adBigInt"
case 17
SetType = "adUnsignedTinyInt"
case 18
SetType = "adUnsignedSmallInt"
case 19
SetType = "adUnsignedInt"
case 21
SetType = "adUnsignedBigInt"
case 4
SetType = "real"
case 5
SetType = "float"
case 6
SetType = "money"
case 14
SetType = "adDecimal"
case 131
SetType = "adNumeric"
case 11
SetType = "adBoolean"
case 10
SetType = "adError"
case 132
SetType = "adUserDefined"
case 12
SetType = "adVariant"
case 9
SetType = "adIDispatch"
case 13
SetType = "adIUnknown"
case 72
SetType = "adGUID"
case 7
SetType = "adDate"
case 133
SetType = "adDBDate"
case 134
SetType = "adDBTime"
case 135
SetType = "datetime"
case 8
SetType = "adBSTR"
case 129
SetType = "adChar"
case 200
SetType = "varchar"
case 201
SetType = "adLongVarChar"
case 130
SetType = "adWChar"
case 202
SetType = "adVarWChar"
case 203
SetType = "adLongVarWChar"
case 128
SetType = "adBinary"
case 204
SetType = "adVarBinary"
case 205
SetType = "adLongVarBinary"
case 136
SetType = "adChapter"
case 64
SetType = "adFileTime"
case 137
SetType = "adDBFileTime"
case 138
SetType = "adPropVariant"
case 139
SetType = "adVarNumeric"
End Select
End Function
%>