<!-- #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
%>