Sql server "show create table" and "show create database" commands
Mysql-style "Show create table" and "show create database" commands for Microsoft sql server. Script is written in Microsoft classic asp language and is quite easy to port to another language or modify to help migrating to other databases.
<%
'''''''''''''''''''''''''''''''''''''''''''''''''''''
' "show create table" and "show create database"
' commands for mssql
'
' Example:
' set pageConnection = server.createObject("ADODB.Connection")
' pageConnection.open "Driver={SQL Server};Server=127.0.0.1;UID=user;PWD=password;Database=dbname;"
' response.write showcreatetable("tablename",1+2+4,"")
' pageConnection.close
'
' Written by Ville Jungman / Varuste.net
' Gnu public licence. Free to use, modify & copy.
'''''''''''''''''''''''''''''''''''''''''''''''''''''
' show create table -command for microsoft sql server
'
'
' purpose:
' returns sql to create table
'
' usage:
' response.write showcreatetable("mytable",1+2+4)
'
' parametres:
' name = table name
' mode = function return value mode (see below)
' if mode bit 1 is on: sql to create table will be returned
' if mode bit 2 is on: sql to create table related stuff will be returned
' if mode bit 4 is on: sql to create foreign keys will be returned
' if mode bit 8 is on: sql to create table rows
' where = Sql where command if only some of the data rows are needed. Like: ' id>3'
function showcreatetable(name,mode,where)
dim query,query2,query3,query4,kentat,kentat2,sql,unique,sql1,sql2,sql3,sql4,primarykey,tyypit,temp,temp2,identity
set query = server.createObject("ADODB.Recordset")
set query2 = server.createObject("ADODB.Recordset")
set query3 = server.createObject("ADODB.Recordset")
set query4 = server.createObject("ADODB.Recordset")
Set tyypit = Server.CreateObject("Scripting.Dictionary")
''''''''''''''''''''''''
' get primary key -field
query.open "select c.name from sysindexes i join sysobjects o ON i.id = o.id join sysobjects pk ON i.name = pk.name AND pk.parent_obj = i.id AND pk.xtype = 'PK' join sysindexkeys ik on i.id = ik.id and i.indid = ik.indid join syscolumns c ON ik.id = c.id AND ik.colid = c.colid where o.name = '" & name & "' order by ik.keyno",pageConnection,0,1
if not query.eof then
primarykey = query("name")
sql2 = sql2 & "alter table " & name & " add primary key(" & primarykey & ");"
end if
query.close
'''''''''''''''''''''''''''''''''
' get sysobjects-id for the table
query.open "select id from sysobjects where xtype='u' and name='" & name & "'",pageConnection,0,1
'''''''''''''''''''''''''
' loop through all fields
query2.open ("select sc.status,sc.name,st.name as xtype,st.length,st.variable,sc.isnullable from syscolumns as sc,systypes as st where sc.xtype=st.xtype and sc.id=" & query("id") & " and left(sc.name,3)<>'zzz' order by sc.name"),pageConnection,0,1
while not query2.EOF
if exists(kentat) then
kentat = kentat & ","
kentat2 = kentat2 & ","
kentat3 = kentat3 & ","
end if
kentat2 = kentat2 & query2("name")
kentat = kentat & query2("name")
kentat = kentat & " " & query2("xtype")
temp = query2("name")
temp2 = query2("xtype")
tyypit.add temp,temp2
if query2("xtype") = "varchar" then
kentat = kentat & "(" & query2("length") & ")"
end if
if query2("isnullable") then
kentat = kentat & " null"
else
kentat = kentat & " not null"
end if
if query2("status") and 128 then
kentat = kentat & " identity"
identity = name
end if
''''''''''''''''''''''
' default column value
query3.open "SELECT column_default FROM INFORMATION_SCHEMA.columns where table_name='" & name & "' and column_name='" & query2("name") & "' and column_default is not null",pageConnection,0,1
if not query3.eof then
kentat = kentat & " default " & query3("column_default")
end if
query3.close
query2.movenext
wend
query2.Close
''''''
' keys
query2.open "select constid,rkeyid,fkeyid,fkey,rkey from sysforeignkeys where fkeyid=" & query("id"),pageConnection,0,1
while not query2.EOF
''''''''''''''
' get key name
dim constraint,fkey,rkey,table,rtable
query3.open "select name from sysobjects where id=" & query2("constid"),pageConnection,0,1
constraint = query3("name")
query3.close
''''''''''''''''''''''''''''
' get table name for the key
query3.open "select name from sysobjects where id=" & query2("rkeyid"),pageConnection,0,1
rtable = query3("name")
query3.close
''''''''''''''''''''''''''''''''
' get the field name for the key
query3.open "select name from syscolumns where colid=" & query2("rkey") & " and id=" & query2("rkeyid"),pageConnection,0,1
rkey = query3("name")
query3.close
'''''''''''''''''''''''''''''''''''''''
' get the field the key references from
query3.open "select name from syscolumns where colid=" & query2("fkey") & " and id=" & query2("fkeyid"),pageConnection,0,1
fkey = query3("name")
query3.close
'''''''''''''''''''''''
' show alter table -sql
if left(fkey,3) <> "zzz" and left(rkey,3) <> "zzz" then
sql3 = sql3 & "alter table " & name & " add foreign key(" & fkey & ") references " & rtable & " (" & rkey & ");"
end if
flushaa
query2.movenext
wend
query2.close
''''''''''''''''''''''
' loop for unique keys
query2.open "select id,name from sysobjects where xtype='UQ' and parent_obj=" & query("id"),pageConnection,0,1
while not query2.EOF
''''''''''''''''''''''''''''''''''''
' loop for fields for the unique key
sql = _
" select c.name" & _
" from sysindexes i,sysobjects pk,sysindexkeys ik,syscolumns c" & _
" where" & _
" i.name = pk.name and" & _
" pk.parent_obj = i.id and" & _
" pk.xtype = 'UQ' and pk.id=" & query2("id") & " and" & _
" i.id = ik.id and " & _
" i.indid = ik.indid and" & _
" ik.id = c.id and" & _
" ik.colid = c.colid"
unique = ""
query3.open sql,pageConnection,0,1
while not query3.EOF
if exists(unique) then
unique = unique & ","
end if
unique = unique & query3("name")
query3.movenext
wend
query3.close
'sql2 = sql2 & "alter table " & name & " add constraint " & query2("name") & " unique(" & unique & ");"
sql2 = sql2 & "alter table " & name & " add unique(" & unique & ");"
query2.movenext
wend
query2.close
''''''''''''''''''
' loop for indexes
query2.open "select name,indid from sysindexes where indid>0 and id=" & query("id") & " and left(name,8) <> '_WA_Sys_'",pageConnection,0,1
while not query2.EOF
''''''''''''''''''''''''''''''''''''''
' get table name the key references to
set query3=server.createObject("ADODB.Recordset")
query3.open "select name from sysobjects where id=" & query("id"),pageConnection,0,1
table = query3("name")
query3.close
'''''''''''''''''''''''''''''''''''''''''
' loop for fields the index references to
dim cols,fail
fail = ""
cols = ""
query3.open "select colid from sysindexkeys where indid=" & query2("indid") & " and id=" & query("id"),pageConnection,0,1
while not query3.eof
''''''''''''''''''''''''''''''''
' get the field name for the key
query4.open "select name from syscolumns where colid=" & query3("colid") & " and id=" & query("id"),pageConnection,0,1
if left(query4("name"),3) = "zzz" then
fail = 1
end if
if exists(cols) then
cols = cols & ","
end if
cols = cols & query4("name")
query4.close
query3.movenext
wend
query3.close
if not exists(fail) then
'sql2 = sql2 & "create index " & query2("name") & " on " & table & "(" & cols & ");"
end if
query2.movenext
wend
query2.Close
query.close
''''''''''''''''''''''
' make createtable-sql
sql1 = "create table " & name & "(" & kentat & ");"
'''''''''''''
' insert data
if cbool(mode and 8) then
dim apu,kentat3,kentat4
sql4 = "delete from " & name & ";"
if exists(identity) then
sql4 = sql4 & "SET IDENTITY_INSERT " & name & " ON;"
end if
sql = "select * from " & name
if exists(where) then
sql = sql & " where " & where
end if
query.open sql,pageConnection,0,1
while not query.eof
kentat3 = ""
kentat4 = ""
for each apu in split(kentat2,",")
if exists(kentat3) then
kentat3 = kentat3 & ","
kentat4 = kentat4 & ","
end if
kentat3 = kentat3 & apu
if tyypit.item(apu) = "bit" then
if query(apu) = "True" then
kentat4 = kentat4 & "1"
else
kentat4 = kentat4 & "0"
end if
else
if tyypit.item(apu) = "varchar" then
temp = query(apu)
if exists(temp) then
temp = replace(temp,";","")
end if
kentat4 = kentat4 & "'" & temp & "'"
else
if exists(query(apu)) then
kentat4 = kentat4 & query(apu)
else
kentat4 = kentat4 & "null"
end if
end if
end if
next
sql4 = sql4 & "insert into " & name & "(" & kentat3 & ") values(" & kentat4 & ");"
query.movenext
wend
query.close
if exists(identity) then
sql4 = sql4 & "SET IDENTITY_INSERT " & name & " off;"
end if
end if
''''''''''''''''''''''''''''''
' return sql according to mode
if cbool(mode and 1) then
showcreatetable = sql1
end if
if cbool(mode and 2) then
showcreatetable = showcreatetable & sql2
end if
if cbool(mode and 4) then
showcreatetable = showcreatetable & sql3
end if
if cbool(mode and 8) then
showcreatetable = showcreatetable & sql4
end if
end function
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' show create database -command for microsoft sql server
'
' purpose:
' returns sql to create database
'
' usage:
' response.write showcreatedatabase(1+2)
'
' parametres:
' mode = function return value mode (see below)
' if mode bit 1 is on: sql to create tables will be returned
' if mode bit 2 is on: table related sql will be returned (foreign keys etc)
function showcreatedatabase(mode)
dim query,query2,sql1,sql2,sql3,sql4
set query = server.createObject("ADODB.Recordset")
set query2 = server.createObject("ADODB.Recordset")
'''''''''''''''''''''
' remove foreign keys
'query2.open "select table_name,constraint_name from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where constraint_catalog = 'varusten2' and constraint_type='FOREIGN KEY'",pageConnection2,0,1
'while not query2.EOF
' sql1 = sql1 & "alter table " & query2("table_name") & " drop constraint " & query2("constraint_name") & ";"
' query2.movenext
'wend
'query2.close
''''''''''''''''''''''''
' loop through all tables
' very bad code here: zzz and dtproperties tables are on my private use. please use following -line instead :)
' query.open "select name,id from sysobjects where xtype='u' order by name",pageConnection,0,1
query.open "select name,id from sysobjects where xtype='u' and left(name,3)<>'zzz' and name<>'dtproperties' order by name",pageConnection,0,1
while not query.EOF
'query2.open "select 1 from sysobjects where xtype='u' and name='" & query("name") & "'",pageConnection2,0,1
'if not query2.eof then
' sql1 = sql1 & "drop table " & query("name") & ";"
'end if
'query2.close
if cbool(mode and 1) then
sql1 = sql1 & showcreatetable(query("name"),1,"")
end if
if cbool(mode and 2) then
sql2 = sql2 & showcreatetable(query("name"),2,"")
end if
if cbool(mode and 4) then
sql3 = sql3 & showcreatetable(query("name"),4,"")
end if
if cbool(mode and 8) then
sql4 = sql4 & showcreatetable(query("name"),8,"")
end if
query.movenext
wend
query.close
showcreatedatabase = sql1 & sql2 & sql4 & sql3
end function
%>
Script donated by Varuste.net