[script utili] dump db access

**deleted_b** · 18-03-2009, 17:10

se vi serve e/o volete testarlo... poi se qualcuno ha la buona volontà di provarlo anche su sqlserver

codice:

<html>

<head>
	<title>Database Dumper</title>
	
	<style type="text/css">
		body { font-size:0.75em }
		h1   { font-size:medium; }
		hr   { height:1px; color:#d0d0d0; }
		div  { width:100%; height:250px; padding:10px; border:1px solid #d0d0d0; overflow:auto; }
	</style>
</head>

<body>

	<form action="" method="post">
		Path <input type="text" name="db" size="50" value="<%= Request.Form("DB") %>" />
		<input type="submit" value="Dump" />
		
		<input type="checkbox" name="tables" 		<% If Request.Form("Tables") 		<> "" Then Response.Write("checked=""checked""") %> /> Tables
		<input type="checkbox" name="indexes"		<% If Request.Form("Indexes") 		<> "" Then Response.Write("checked=""checked""") %> /> Indexes
		<input type="checkbox" name="foreignkeys"	<% If Request.Form("ForeignKeys") 	<> "" Then Response.Write("checked=""checked""") %> /> Foreign keys
		<input type="checkbox" name="data"		<% If Request.Form("Data") 		<> "" Then Response.Write("checked=""checked""") %> /> Data (<input type="checkbox" name="escape" <% If Request.Form("Escape") <> "" Then Response.Write("checked=""checked""") %> /> Escape)
	</form>
	
	<hr />

	<%
	
	If Request.Form("DB") <> "" Then
	
	
	
		CONNECTION_STRING = "Provider='Microsoft.Jet.OLEDB.4.0';Data Source='" & Server.MapPath(Request.Form("DB")) & "'"
	
	
	
		' Create DB object
		Set objDB = Server.CreateObject("ADOX.Catalog")
		
		' Open connection
		objDB.ActiveConnection = CONNECTION_STRING
		
		
	
		' Create DB connection object
		Set objConn = Server.CreateObject("ADODB.Connection")
		
		' Open connection
		objConn.Open CONNECTION_STRING
	
	
	
		If Request.Form("Tables") <> "" Then	
	
			' Get tables list
			Set objTables = objDB.Tables
			
		
		
			Response.Write("<h1>Tables</h1>")
			Response.Write("<div>")
		
			' Dump tables
			For i = 0 To objTables.Count - 1
			
				If objTables(i).Type = "TABLE" Then
				
					' Get table columns
					Set objColumns = objTables(i).Columns
					
					' CREATE TABLE statment
					strSQL = "CREATE TABLE " & checkName(objTables(i).Name) & " ("
					
					' Add columns declaration
					For j = 0 To objColumns.Count - 1
					
						strSQL = strSQL & checkName(objColumns(j).Name)
						
						Select Case objColumns(j).Type
						
							Case "3"	' Long integer
		
								strSQL = strSQL & " INTEGER"
								
								If objColumns(j).Properties("Autoincrement") = True Then
									strSQL = strSQL & " IDENTITY"
								End If
								
								If objColumns(j).Properties("Nullable") = False Then
									strSQL = strSQL & " NOT NULL"
								End If
								
								If objColumns(j).Properties("Autoincrement") = False Then
								If IsNull(objColumns(j).Properties("Default")) = False Then
									strSQL = strSQL & " DEFAULT " & objColumns(j).Properties("Default")
								End If
								End If
						
							Case "5"	' Double precision
		
								strSQL = strSQL & " FLOAT"
								
								If objColumns(j).Properties("Nullable") = False Then
									strSQL = strSQL & " NOT NULL"
								End If
								
								If IsNull(objColumns(j).Properties("Default")) = False Then
									strSQL = strSQL & " DEFAULT " & objColumns(j).Properties("Default")
								End If
								
							Case "7"	' Date
							
								strSQL = strSQL & " DATE"
								
								If objColumns(j).Properties("Nullable") = False Then
									strSQL = strSQL & " NOT NULL"
								End If
								
								If IsNull(objColumns(j).Properties("Default")) = False Then
									strSQL = strSQL & " DEFAULT " & objColumns(j).Properties("Default")
								End If
					
							Case "11"	' Yes / No
								
								strSQL = strSQL & " BIT"
								
								If objColumns(j).Properties("Nullable") = False Then
									strSQL = strSQL & " NOT NULL"
								End If
								
								If IsNull(objColumns(j).Properties("Default")) = False Then
									strSQL = strSQL & " DEFAULT " & objColumns(j).Properties("Default")
								End If
						
							Case "17"	' Byte
		
								strSQL = strSQL & " TINYINT"
								
								If objColumns(j).Properties("Nullable") = False Then
									strSQL = strSQL & " NOT NULL"
								End If
								
								If IsNull(objColumns(j).Properties("Default")) = False Then
									strSQL = strSQL & " DEFAULT " & objColumns(j).Properties("Default")
								End If
								
							Case "72"	' GUID
								
								strSQL = strSQL & " UNIQUEIDENTIFIER"
								
								If objColumns(j).Properties("Nullable") = False Then
									strSQL = strSQL & " NOT NULL"
								End If
								
								If IsNull(objColumns(j).Properties("Default")) = False Then
									strSQL = strSQL & " DEFAULT " & objColumns(j).Properties("Default")
								End If
						
							Case "202"	' Text
		
								If objColumns(j).Properties("Fixed Length") = True Then
									strSQL = strSQL & " CHAR(" & objColumns(j).DefinedSize & ")"
								Else
									strSQL = strSQL & " VARCHAR(" & objColumns(j).DefinedSize & ")"
								End If
								
								If objColumns(j).Properties("Jet OLEDB:Compressed UNICODE Strings") = True Then
									strSQL = strSQL & " WITH COMPRESSION"
								End If
								
								If objColumns(j).Properties("Nullable") = False Then
									strSQL = strSQL & " NOT NULL"
								End If
								
								If IsNull(objColumns(j).Properties("Default")) = False Then
									strSQL = strSQL & " DEFAULT " & objColumns(j).Properties("Default")
								End If
								
								If objColumns(j).Properties("Jet OLEDB:Allow Zero Length") = False Then
									strSQL = strSQL & ",CONSTRAINT " & checkName(objTables(i).Name & "_" & objColumns(j).Name) & " CHECK (" & checkName(objColumns(j).Name) & " <> """")"
								End If
						
							Case "203"	' Memo
		
								strSQL = strSQL & " TEXT"
								
								If objColumns(j).Properties("Jet OLEDB:Compressed UNICODE Strings") = True Then
									strSQL = strSQL & " WITH COMPRESSION"
								End If
								
								If objColumns(j).Properties("Nullable") = False Then
									strSQL = strSQL & " NOT NULL"
								End If
								
								If IsNull(objColumns(j).Properties("Default")) = False Then
									strSQL = strSQL & " DEFAULT " & objColumns(j).Properties("Default")
								End If
								
								If objColumns(j).Properties("Jet OLEDB:Allow Zero Length") = False Then
									strSQL = strSQL & ",CONSTRAINT " & checkName(objTables(i).Name & "_" & objColumns(j).Name) & " CHECK (" & checkName(objColumns(j).Name) & " <> """")"
								End If
								
							Case Else
							
								strSQL = strSQL & " <blockquote><b style=""color:red"">" & objColumns(j).Type & " !!!!!!!!!!!!!!!!!!!!!![/b]
"
								
								For Each objProperty In objColumns(j).Properties
									strSQL = strSQL & objProperty.Name & " = " & objProperty.Value & "
"
								Next
		
								strSQL = strSQL & " </blockquote>"
						
						End Select
						
						If j < objColumns.Count - 1 Then
							strSQL = strSQL &  ","
						End If
					
					Next
			
					strSQL = strSQL & ");"
					
					Set objColumns = Nothing
		
					Response.Write("<nobr>" & strSQL & "</nobr>
")
					
				End If
				
			Next
	
			Response.Write("</div>")
	
	
			
			Set objTables = Nothing
			
		End If


	
		If Request.Form("Indexes") <> "" Then	
	
			' Get tables list
			Set rsTables = objConn.OpenSchema(20)	' adSchemaTables 
		
			' Get indexes list
			Set rsIndexes = objConn.OpenSchema(12)	' adSchemaIndexes
		
			' Get foreign keys list
			Set rsKeys = objConn.OpenSchema(27)	' adSchemaForeignKeys
		
		
		
			Response.Write("<h1>Indexes</h1>")
			Response.Write("<div>")
		
			' Indexes
			While rsIndexes.EOF = False
		
				rsTables.Filter	= "TABLE_NAME LIKE '" & rsIndexes("TABLE_NAME") & "' AND TABLE_TYPE='TABLE'"	' Check for user table
				rsKeys.Filter	= "FK_NAME LIKE '" & rsIndexes("INDEX_NAME") & "'"				' Check for foreign key
				
				If rsTables.EOF = False Then
				If rsKeys.EOF = True Then
				
					If rsIndexes("PRIMARY_KEY") = True Then
						strIndex = "ALTER TABLE " & checkName(rsIndexes("TABLE_NAME")) & " ADD CONSTRAINT " & checkName(rsIndexes("INDEX_NAME")) & " PRIMARY KEY (" & checkName(rsIndexes("COLUMN_NAME")) & ");"
					ElseIf rsIndexes("UNIQUE") = True Then
						strIndex = "CREATE UNIQUE INDEX " & checkName(rsIndexes("INDEX_NAME")) & " ON " & checkName(rsIndexes("TABLE_NAME")) & " (" & checkName(rsIndexes("COLUMN_NAME")) & ");"
					Else
						strIndex = "CREATE INDEX " & checkName(rsIndexes("INDEX_NAME")) & " ON " & checkName(rsIndexes("TABLE_NAME")) & " (" & checkName(rsIndexes("COLUMN_NAME")) & ");"
					End If
						
					Response.Write("<nobr>" & strIndex & "</nobr>
")
					
				End If
				End If
				
				rsIndexes.MoveNext
				
			WEnd

			Response.Write("</div>")
			
			
			
			rsKeys.Close
			Set rsKeys = Nothing
			rsIndexes.Close
			Set rsIndexes = Nothing
			rsTables.Close
			Set rsTables = Nothing
		
		End If


	
		If Request.Form("ForeignKeys") <> "" Then	
	
			' Get tables list
			Set rsTables = objConn.OpenSchema(20)	' adSchemaTables 
		
			' Get indexes list
			Set rsIndexes = objConn.OpenSchema(12)	' adSchemaIndexes
		
			' Get foreign keys list
			Set rsKeys = objConn.OpenSchema(27)	' adSchemaForeignKeys
		
		
		
			Response.Write("<h1>Foreign Keys</h1>")
			Response.Write("<div>")
		
			' Foreign Keys
			While rsKeys.EOF = False
		
				rsTables.Filter	= "TABLE_NAME LIKE '" & rsKeys("PK_TABLE_NAME") & "' AND TABLE_TYPE='TABLE'"	' Check for user table
				
				If rsTables.EOF = False Then
				
					strKey = "ALTER TABLE " & checkName(rsKeys("FK_TABLE_NAME")) & " ADD CONSTRAINT " & checkName(rsKeys("FK_NAME")) & " FOREIGN KEY (" & checkName(rsKeys("FK_COLUMN_NAME")) & ") REFERENCES " & checkName(rsKeys("PK_TABLE_NAME")) & " (" & checkName(rsKeys("PK_COLUMN_NAME")) & ")"
					
					If rsKeys("UPDATE_RULE") = "CASCADE" Then
						strKey = strKey & " ON UPDATE CASCADE"
					End If
					
					If rsKeys("DELETE_RULE") = "CASCADE" Then
						strKey = strKey & " ON DELETE CASCADE"
					End If
					
					strKey = strKey & ";"
						
					Response.Write("<nobr>" & strKey & "</nobr>
")
					
				End If
				
				rsKeys.MoveNext
				
			WEnd

			Response.Write("</div>")
			
			
			
			rsKeys.Close
			Set rsKeys = Nothing
			rsIndexes.Close
			Set rsIndexes = Nothing
			rsTables.Close
			Set rsTables = Nothing
			
		End If


	
		If Request.Form("Data") <> "" Then	
	
			' Get tables list
			Set rsTables = objConn.OpenSchema(20)	' adSchemaTables 
		
		
		
			Response.Write("<h1>Data</h1>")
			Response.Write("<div>")

			' Data
			While rsTables.EOF = False

				If rsTables("TABLE_TYPE") = "TABLE" Then
			      	
			      		' Get table
					Set rsTable = objConn.Execute("SELECT * FROM " & CheckName(rsTables("TABLE_NAME")))
					
					' Build SQL
			      		strSQL1 = "INSERT INTO " & CheckName(rsTables("TABLE_NAME")) & " ("
			      		For j = 0 To rsTable.Fields.Count - 2
			      			strSQL1 = strSQL1 & CheckName(rsTable.Fields(j).Name) & ","
			      		Next
			      		strSQL1 = strSQL1 & CheckName(rsTable.Fields(j).Name)
			      		strSQL1 = strSQL1 & ") VALUES ("
			      		strSQL2 = ")"
					
					' Inserts
					While rsTable.EOF = False
					
						' Values
						strValues = ""
						For j = 0 To rsTable.Fields.Count - 1
							Select Case rsTable.Fields(j).Type
								
								Case adBoolean
									strValues = strValues & CLng(rsTable.Fields(j))
								
								Case Else		
									If Request.Form("Escape") = "" Then
										strValues = strValues & "'" & Replace("" & rsTable.Fields(j), "'", "''") & "'"
									Else
										strValues = strValues & "'" & Escape(Replace("" & rsTable.Fields(j), "'", "''")) & "'"
									End If

							End Select
							
							If j < rsTable.Fields.Count - 1 Then
								strValues = strValues & ","
							End If
						Next
						
						Response.Write("<nobr>" & strSQL1 & strValues & strSQL2 & "</nobr>
")
						
						rsTable.MoveNext
						
					WEnd
			
					rsTable.Close
					Set rsTable = Nothing
			
				End If	
				
				rsTables.MoveNext
			WEnd

			Response.Write("</div>")



			rsTables.Close
			Set rsTables = Nothing
		
		End If
		



		objConn.Close
		Set objConn = Nothing



		Set objDB.ActiveConnection = Nothing
		Set objDB = Nothing
	

	
	End If

**deleted_b** · 18-03-2009, 17:11

codice:

	
	
	
	
	
	Function checkName (ByVal strName)
	
		Dim i
	
	
	
		' Reserved words
		strReservedWords = "|date|language|password|value|guid|image|note|number|text|currency|"
	
		' Invadid chars
		strInvalidChars  = " -"
	
	
	
		For i = 1 To Len(strInvalidChars)
	
			If InStr(strName, Mid(strInvalidChars, i, 1)) > 0 Then
	
				checkName = "[" & strName & "]"
	
				Exit Function
			
			End If
			
		Next
		
		If InStr(strReservedWords, "|" & LCase(strName) & "|") > 0 Then
	
			checkName = "[" & strName & "]"
	
			Exit Function
	
		End If
	
		checkName = strName
	
	End Function

%>

</body>

</html>

Discussione: [script utili] dump db access

Strumenti discussione

Ricerca discussione

Visualizza

[script utili] dump db access

Permessi di invio