⭐ 欢迎来到虫虫下载站！ | 📦 资源下载 📁 资源专辑 ℹ️ 关于我们
⭐ 虫虫下载站
📤 上传资源

📄 access to sql 脚本编写器.htm

📁 Access to Sql 脚本编写器
💻 HTM
📖 第 1 页 / 共 5 页
字号:
🔍
12345 
		if n>1 then TmpStr=TmpStr&"," & splitchar1 & vbcrlf
		if autoclumn=cols("Column_name") then
			TmpStr=TmpStr & autoclumnStr 
		else
			TmpStr=TmpStr & "	"&splitchar&"["& cols("Column_name") &"] "& lcase(datatypeStr(cols("DATA_TYPE"),cols("CHARACTER_MAXIMUM_LENGTH")))& defaultStr(cols("DATA_TYPE"),cols("COLUMN_DEFAULT"),exec) & nullStr(cols("IS_NULLABLE")) 
		end if
		cols.movenext
	loop
		TmpStr=TmpStr & splitchar1 & vbcrlf &"	"&splitchar&")"
	cols.close
	if exec=1 then 
		TmpStr=TmpStr&""")"
	end if


'' 编写表脚本
'	autoclumn=GetAutoincrementCoulmnT(tableName)
'	if exec=1 then
'		TmpStr="CONN.execute(""CREATE TABLE ["&tableName&"] (""&_"& vbcrlf
'	else
'		TmpStr="CREATE TABLE ["&tableName&"] ("& vbcrlf
'	end if
'	if autoclumn<>"" then
'		TmpStr=TmpStr & "	"&splitchar&"["& autoclumn &"] integer IDENTITY (1,"&GetIncrement(tableName,autoclumn)&") not null"
'		n=n+1
'	end if
'	
'	cols.filter="Table_name='"&tableName&"' and column_name<>'"&autoclumn&"'"
'	while not cols.eof  
'		if n>0 then TmpStr=TmpStr&"," & splitchar1 & vbcrlf
'		TmpStr=TmpStr & "	"&splitchar&"["& cols("Column_name") &"] "& lcase(datatypeStr(cols("DATA_TYPE"),cols("CHARACTER_MAXIMUM_LENGTH")))& defaultStr(cols("DATA_TYPE"),cols("COLUMN_DEFAULT"),exec) & nullStr(cols("IS_NULLABLE")) 
'		cols.movenext
'		n=n+1
'	wend
'		TmpStr=TmpStr & splitchar1 & vbcrlf &"	"&splitchar&")"
'	cols.close
'	if exec=1 then 
'		TmpStr=TmpStr&""")"
'	end if

' 编写索引脚本
	dim InxArr,i,kstr,j,tmpStr1
	InxArr=getInxArr(tableName) 
	Set cols=CONN.openSchema(12)
	
	for i=0 to ubound(InxArr)
		cols.filter="Table_name='"&tableName&"' and index_name='"&InxArr(i)&"'"
		kstr=""
		tmpStr1=""
		if Not isForeignIndex(tableName,InxArr(i)) then '外键索引不进行编写
			while not cols.eof
				kstr=kstr&",["&cols("column_name")&"] "&GetInxDesc(TableName,InxArr(i),cols("column_name"))
				cols.movenext
			wend
			tmpStr1=tmpStr1&"CREATE "
			if isUnique(TableName,InxArr(i)) then tmpStr1=tmpStr1&"Unique "
			tmpStr1=tmpStr1&"INDEX ["&InxArr(i)&"] on ["&tableName&"]("&mid(kstr,2)&")"
			if isPrimaryKey(TableName,InxArr(i)) then tmpStr1=tmpStr1&" with Primary"
			if exec=1 then tmpStr1="CONN.execute("""&tmpStr1&""")"
			tmpStr=tmpStr&vbcrlf&tmpStr1
		end if
	next
	cols.close
	cols.filter=0
	CreatTableSql=TmpStr
End function

function CreatForeignSql(exec)
dim cols
dim FKtable,PK_cols,FK_cols,tmpStr,tmpStr1
Set cols=CONN.openSchema(27)
cols.filter="PK_NAME<>Null"
	while not cols.eof
			tmpStr1=""
			tmpStr1="ALTER TABLE ["&cols("FK_TABLE_NAME")&"] "&_  
						"Add CONSTRAINT ["&cols("FK_NAME")&"] "&_  
						"FOREIGN KEY (["&cols("FK_COLUMN_NAME")&"]) REFERENCES "&_
						"["&cols("PK_TABLE_NAME")&"] (["&cols("PK_COLUMN_NAME")&"]) "
			if cols("UPDATE_RULE")="CASCADE" then	tmpStr1=tmpStr1&"ON UPDATE CASCADE "
			if cols("DELETE_RULE")="CASCADE" then	tmpStr1=tmpStr1&"ON DELETE CASCADE "
			if exec=1 then tmpStr1="CONN.execute("""&tmpStr1&""")"
			tmpStr=tmpStr&vbcrlf&tmpStr1
		
		cols.movenext
	wend
	cols.filter=0
	cols.close
	set cols=nothing
	CreatForeignSql=tmpStr
End Function

'判断是否是外键索引
Function isForeignIndex(TableName,indexName)
	dim cols
	Set cols=CONN.openSchema(27)
	cols.filter="FK_TABLE_Name='"&TableName&"' and FK_NAME='"&indexName&"'"
	if Not cols.eof then
		isForeignIndex=true
	else
		isForeignIndex=false
	end if
End Function
'取得索引列的排序属性
function GetInxDesc(TableName,indexName,ColumnName)
	dim cat
	set cat=Server.CreateObject("ADOX.Catalog") 
	cat.ActiveCONNection =CONNstr
	if cat.Tables(""&TableName&"").Indexes(""&indexName&"").Columns(""&ColumnName&"").SortOrder=2 then
		GetInxDesc="Desc"
	else
		GetInxDesc=""
	end if
	set cat=nothing
end function
'取得列数组
function getColumArr(tableName)
	dim cols,arr(),n
	redim arr(-1)
	n=0
	redim arr(n)
	set cols=CONN.openSchema(4)
	cols.filter="Table_Name='"&tableName&"'"
	while not cols.eof
		redim Preserve arr(n)
		arr(n)=cols("column_name")
		cols.movenext
		n=n+1
	wend
	cols.filter=0
	cols.close
	set cols=nothing
	getColumArr=arr
end function
'取得索引数组
function getInxArr(tableName)
	dim cols,arr(),n,tmpCol
	redim arr(-1)
	n=0
	set cols=CONN.openSchema(12)
	cols.filter="Table_Name='"&tableName&"'"
	while not cols.eof
		if cols("index_name")<>tmpCol then
			redim Preserve arr(n)
			arr(n)=cols("index_name")
			n=n+1
		end if
		tmpCol=cols("index_name")
		cols.movenext
	wend
	cols.filter=0
	cols.close
	set cols=nothing
	getInxArr=arr
end function

function isUnique(TableName,IndexName)
	dim cols
	set cols=CONN.openSchema(12)
	cols.filter="Table_Name='"&TableName&"' and Index_Name='"&IndexName&"' and UNIQUE=True"
	if not cols.eof then
		isUnique=true
	else
		isUnique=false
	end if
	cols.filter=0
	cols.close
	set cols=nothing
end function


function isPrimaryKey(TableName,IndexName) 
	dim cols
	set cols=CONN.openSchema(12)
	cols.filter="Table_Name='"&TableName&"' and Index_Name='"&IndexName&"' and PRIMARY_KEY=True"
	if not cols.eof then
		isPrimaryKey=true
	else
		isPrimaryKey=false
	end if
	cols.filter=0
	cols.close
	set cols=nothing
end function

function getPrimaryKey(tableName,columnName)
	dim cols
	Set cols=CONN.openSchema(12)
	cols.filter="Table_Name='"&tableName&"' and Column_Name='"&columnName&"' and PRIMARY_KEY=True"
	if not cols.eof then
		getPrimaryKey=cols("INDEX_NAME")
		'isPrimaryKey=true
	else
		getPrimaryKey=""
		'isPrimaryKey=false
	end if
	cols.filter=0
	cols.close
	set cols=nothing
end function

Function GetIncrement(tableName,columnName)
	dim cat
	set cat=Server.CreateObject("ADOX.Catalog") 
	cat.ActiveCONNection =CONNstr
	GetIncrement=cat.Tables(""&TableName&"").Columns(""&columnName&"").Properties("Increment") 
	set cat=nothing
end function

Function GetSeed(tableName,columnName)
	dim cat
	set cat=Server.CreateObject("ADOX.Catalog") 
	cat.ActiveCONNection =CONNstr
	GetSeed=cat.Tables(""&TableName&"").Columns(""&columnName&"").Properties("Seed") 
	set cat=nothing
end function

'通用,内部属性取得自动编号，对SQLserver Access都可以
Function GetAutoincrementCoulmnT(TableName)
	dim i
	rs.open "select * from ["&TableName&"] where 1=0",CONN,0,1
	for i=0 to rs.fields.count-1
		//if rs(i).Properties("isAutoIncrement")=True then
		if rs(i).Properties("isAutoIncrement")=True then
			GetAutoincrementCoulmnT=rs(i).name
			rs.close
			exit function
		end if	
	next
	rs.close
End function

function datatypeStr(DATA_TYPE,CHARACTER_MAXIMUM_LENGTH)
	select case DATA_TYPE 
	case 130 
	  if CHARACTER_MAXIMUM_LENGTH=0 then
		     datatypeStr="Text"	'LongText
	  else   datatypeStr="char("&CHARACTER_MAXIMUM_LENGTH&")"  'varchar()
	  end if
	case 17  datatypeStr="tinyint"
	case 2   datatypeStr="Smallint"
	case 3   datatypeStr="integer" 
	case 4   datatypeStr="real" 'or  /同意词 float4
	case 5 	 datatypeStr="float" 'or  /同意词 float8 
	case 6	 datatypeStr="money" 'or  /同意词  CURRENCY
	case 7	 datatypeStr="datetime"
	case 11  datatypeStr="bit"
	case 72  datatypeStr="UNIQUEIDENTIFIER"  'or  /同意词  GUID
	case 131 datatypeStr="DECIMAL"  'or  /同意词  DEC
	case 128 datatypeStr="BINARY"  'or  /同意词  DEC
	end select 'AUTOINCREMENT
end function

function defaultStr(DATA_TYPE,COLUMN_DEFAULT,exec)
	if isNull(COLUMN_DEFAULT) then
		defaultStr=""
		exit function
	end if
	dim splitchar
	if exec=1 then 
		splitchar=""""""
	else 
		splitchar=""""
	end if
	select case DATA_TYPE 
	case 130 
			if left(COLUMN_DEFAULT,1)="""" and right(COLUMN_DEFAULT,1)="""" then
				COLUMN_DEFAULT=mid(COLUMN_DEFAULT,2,len(COLUMN_DEFAULT)-2)
			end if
				COLUMN_DEFAULT=replace(COLUMN_DEFAULT,"""",splitchar)
12345

⌨️ 快捷键说明

复制代码 Ctrl + C
搜索代码 Ctrl + F
全屏模式 F11
切换主题 Ctrl + Shift + D
显示快捷键 ?
增大字号 Ctrl + =
减小字号 Ctrl + -