Database Schema

<%@LANGUAGE="JAVASCRIPT" EnableSessionState="FALSE"%> <% var sConn = "Provider=sqloledb;"; sConn += "Network Library=DBMSSOCN;"; sConn += "Data Source=servername;"; //"Data Source" is the name/IP of the SQL Server sConn += "Initial Catalog=somedatabase;"; //Name of the Database on the SQL Server sConn += "User Id=userid;"; sConn += "Password=password;"; var conn = Server.CreateObject("ADODB.Connection"); conn.Open(sConn); if (String(Request.QueryString("table")) != "undefined") { sSQL = " SELECT tab.name table_name, "; sSQL += " col.name column_name, "; sSQL += " col.colid column_id, "; sSQL += " typ.name data_type, "; sSQL += " col.length length, "; sSQL += " col.prec prec, "; sSQL += " col.scale scale, "; sSQL += " com.text default_value, "; sSQL += " obj.name default_cons_name, "; sSQL += " CASE "; sSQL += " WHEN col.isnullable = 1 THEN 'Y' "; sSQL += " ELSE 'N' "; sSQL += " END is_nullable, "; sSQL += " CASE "; sSQL += " WHEN col.status & 0x80 = 0x80 THEN 'Y' "; sSQL += " ELSE 'N' "; sSQL += " END is_identity "; sSQL += " FROM sysobjects tab, "; sSQL += " syscolumns col LEFT OUTER JOIN "; sSQL += " syscomments com INNER JOIN "; sSQL += " sysobjects obj ON com.id = obj.id ON col.cdefault = com.id AND com.colid = 1, "; sSQL += " systypes typ "; sSQL += " WHERE tab.id = col.id "; sSQL += " AND tab.xtype = 'U' "; sSQL += " AND tab.name = '" + Request.QueryString("table") + "' "; sSQL += " AND col.xusertype = typ.xusertype "; sSQL += " ORDER BY 1, 3 "; sOut = ''; sOut += '

Table Schema for "' + Request.QueryString("table") + '"

'; sOut += ''; sOut += ''; rs=Server.CreateObject("ADODB.Recordset"); rs.Open(sSQL, conn); while (!rs.EOF) { sOut += ''; sOut += ''; sOut += ''; sOut += ''; sOut += ''; sOut += ''; defaultValue = String(rs("default_value")); if (defaultValue.length < 20) { sOut += ''; } else { sOut += ''; } sOut += ''; rs.MoveNext(); } sOut += '

Column Name	Data Type	Length	Nulls	Identity	Default Value
' + rs("column_name") + '	' + rs("data_type") + '	' + rs("length") + '	' + rs("is_nullable") + '	' + rs("is_identity") + '	' + defaultValue + '	Text Value...

'; sSQL = " SELECT tab.name table_name, "; sSQL += " ind.name constraint_name, "; sSQL += " INDEX_COL(tab.name, ind.indid, idk.keyno) column_name, "; sSQL += " idk.keyno pos "; sSQL += " FROM sysobjects tab, "; sSQL += " sysindexes ind, "; sSQL += " sysindexkeys idk "; sSQL += " WHERE ind.status & 0x800 = 0x800 "; sSQL += " AND ind.id = tab.id "; sSQL += " AND idk.id = tab.id "; sSQL += " AND idk.indid = ind.indid "; sSQL += " AND tab.name = '" + Request.QueryString("table") + "' "; sSQL += " ORDER BY 1, 2, 4 "; var rs1=Server.CreateObject("ADODB.Recordset"); rs1.Open(sSQL, conn, 1); sOut += '

Primary Key:

' + rs1("column_name") + '
None Defined

'; Response.Write (sOut); Response.End; } %> Database Schema <% var sSQL = " SELECT tab.name table_name "; sSQL += " FROM sysobjects tab "; sSQL += " WHERE tab.xtype = 'U' "; sSQL += " AND tab.name <> 'dtproperties' "; sSQL += " ORDER BY 1 "; var rs=Server.CreateObject("ADODB.Recordset"); rs.Open(sSQL, conn, 1); var thisPage = Request.ServerVariables("SCRIPT_NAME"); var sOut = '

'; sOut += '

Hide Table List

'; sOut += '

' + rs("table_name") + '

'; rs.Close(); sOut += '

'; sOut += '

'; Response.Write (sOut); %>