[script utili] dump db access

[deleted_b]

lo ho cercato invano su internet, visto che non ho trovato niente ve lo posto

stampa (ma potete fargli fare quello che volete) la lista delle istruzioni sql per ricreare tabelle, indici e relazioni...

sicuramente mi sono scordato qualche tipo di dato e parole riservate, ma è facile aggiungerne di nuovi... si può adattare anche a sql server, ma ancora non lo ho testato...

dovrebbe andare... se lo usate, avvisatemi in caso di bug ... o per suggerimenti...

<%

	CONNECTION_STRING = "Provider='Microsoft.Jet.OLEDB.4.0';Data Source='" & Server.MapPath("/mdb-database/miodb.mdb") & "'"



	' Create DB object
	Set objDB = Server.CreateObject("ADOX.Catalog")
	
	' Open connection
	objDB.ActiveConnection = CONNECTION_STRING


	
	' Get tables list
	Set objTables = objDB.Tables
	


	Response.Write("<h2>Tables</h2>")

	' 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:Allow Zero Length") = False Then
							strSQL = strSQL & " CHECK<>"""""
						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
				
					Case "203"	' Memo

						strSQL = strSQL & " TEXT"
						
						If objColumns(j).Properties("Jet OLEDB:Allow Zero Length") = False Then
							strSQL = strSQL & " CHECK<>"""""
						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
						
					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("

" & Replace(Replace(Left(strSQL, Len(strSQL) - 2) & "</blockquote>);", " (", " (<blockquote>"), ",", ",
"))
			
		End If
		
	Next
	
	Set objTables = Nothing



	Set objDB.ActiveConnection = Nothing
	Set objDB = Nothing
	
	

	' Create DB connection object
	Set objConn = Server.CreateObject("ADODB.Connection")
	
	' Open connection
	objConn.Open = CONNECTION_STRING


	
	' 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("<h2>Indexes</h2>")

	' Dump 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("

" & strIndex)
			
		End If
		End If
		
		rsIndexes.MoveNext
		
	WEnd

	rsTables.Filter	= ""
	rsKeys.Filter 	= ""
	
	rsIndexes.Close
	Set rsIndexes = Nothing
	


	Response.Write("<h2>Foreign Keys</h2>")

	' Dump 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("

" & strKey)
			
		End If
		
		rsKeys.MoveNext
		
	WEnd
	
	rsKeys.Close
	Set rsKeys = Nothing



	rsTables.Close
	Set rsTables = Nothing

	objConn.Close
	Set objConn = Nothing
	
	
	
	Function checkName (strName)
	
		Dim i
	
	
	
		' Reserved words
		strReservedWords = "|date|"
	
		' 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, "|" & strName & "|") > 0 Then
	
			checkName = "[" & strName & "]"
	
			Exit Function
	
		End If
	
		checkName = strName
	
	End Function

%>