?? access to sql 腳本編寫器.htm
字號:
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 '外鍵索引不進(jìn)行編寫
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
'取得索引列的排序?qū)傩?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
'取得列數(shù)組
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
'取得索引數(shù)組
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
'通用,內(nèi)部屬性取得自動編號，對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)