ASPX Inline Code: |
<%@ Page language="C#" Trace="false" debug="true" Inherits="exampleViews" Src="default.aspx.cs" strict="false" %>
<%@ Register TagPrefix="Dlp" TagName="CodeViewer" Src="~/templates/CodeViewer/codeViewer.ascx" %>
<html>
<head>
<title>INFORMATION_SCHEMA View Examples</title>
<link rel="stylesheet" href="/Templates/style.css" type="text/css" />
</head>
<body>
<h2>INFORMATION_SCHEMA View Examples</h2>
<a href="/testsite/">Back to Test Home</a> | <a href="">Back to View Home</a>
<br />
<form runat="server">
Change the dropdown to *view* the information: <asp:DropDownList id="ISView" DataTextField="ViewNames" AutoPostBack="true" OnSelectedIndexChanged="ISView_SelectedIndexChanged" runat="server" />
<%--
Want to change this to pull from files/db or something else
--%>
<asp:panel id="mainPanel" runat="server">
<div class="expandable" align="left" onclick="OnToggle()" level2ID="ViewLayoutDiv">Show View Layout:</div>
<div id="ViewLayoutDiv" style="display: none;">
<asp:DataGrid id="ViewLayout" runat="server" BackColor="#eeeeee"
HorizontalAlign="Center"
Font-Name="Verdana" CellPadding="3"
Font-Size="8pt"
Headerstyle-BackColor="lightgray"
Headerstyle-Font-Size="10pt"
Headerstyle-Font-Style="bold" />
</div>
<div class="expandable" align="left" onclick="OnToggle()" id="detailDiv" EnableViewState="false" runat="server">Show Detail:</div>
<asp:panel id="pnlTables" visible="false" EnableViewState="false" runat="server" style="display: none;">
<br />Shows the tables and views that the current user has permmisions to see. To fully filter any extra system objects out of it, use the OBJECTPROPERTY function to deal with it:
<pre>
SELECT
TABLE_CATALOG
, TABLE_SCHEMA
, TABLE_NAME
, TABLE_TYPE
FROM
INFORMATION_SCHEMA.TABLES
WHERE
OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
)
, 'IsMSShipped') = 0
</pre>
<asp:DataGrid id="dgTables" runat="server" BackColor="#eeeeee"
HorizontalAlign="Center"
Font-Name="Verdana" CellPadding="3"
Font-Size="8pt"
Headerstyle-BackColor="lightgray"
Headerstyle-Font-Size="10pt"
Headerstyle-Font-Style="bold" />
</asp:panel>
<asp:panel id="pnlColumns" visible="false" EnableViewState="false" runat="server" style="display: none;">
<br />
<pre>
SELECT
TABLE_CATALOG -- current database
, TABLE_SCHEMA -- current owner
, TABLE_NAME -- current table
, COLUMN_NAME -- column
, ORDINAL_POSITION -- position in the table
, COLUMN_DEFAULT -- is there a default and what is it
, IS_NULLABLE -- does this column allow nulls
, DATA_TYPE -- the datatype
, CHARACTER_MAXIMUM_LENGTH
, CHARACTER_OCTET_LENGTH
, NUMERIC_PRECISION -- precion of the numeric or decimal value
, NUMERIC_PRECISION_RADIX
, NUMERIC_SCALE -- scale of the numeric or decimal value
, DATETIME_PRECISION
, CHARACTER_SET_CATALOG
, CHARACTER_SET_SCHEMA
, CHARACTER_SET_NAME
, COLLATION_CATALOG
, COLLATION_SCHEMA
, COLLATION_NAME -- what collation is this column
, DOMAIN_CATALOG
, DOMAIN_SCHEMA
, DOMAIN_NAME
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME = 'authors'
</pre>
<asp:DataGrid id="dgColumns" runat="server" BackColor="#eeeeee"
HorizontalAlign="Center"
Font-Name="Verdana" CellPadding="3"
Font-Size="8pt"
Headerstyle-BackColor="lightgray"
Headerstyle-Font-Size="10pt"
Headerstyle-Font-Style="bold" />
<pre>
SELECT
TABLE_NAME
, COLUMN_NAME
, ORDINAL_POSITION
, DATA_TYPE
, IDENT_SEED(TABLE_NAME) [SEED]
, IDENT_INCR(TABLE_NAME) [INCREMENT]
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
AND COLUMNPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
)
, COLUMN_NAME
, 'IsIdentity'
) = 1
<asp:DataGrid id="dgColumns1" runat="server" BackColor="#eeeeee"
HorizontalAlign="Center"
Font-Name="Verdana" CellPadding="3"
Font-Size="8pt"
Headerstyle-BackColor="lightgray"
Headerstyle-Font-Size="10pt"
Headerstyle-Font-Style="bold" />
</pre>
</asp:panel>
<asp:panel id="pnlRoutines" visible="false" EnableViewState="false" runat="server" style="display: none;">
<br />Shows all user created (marked) routines (stored procedures, functions) that I have in my test database
<pre>
SELECT
ROUTINE_CATALOG
, ROUTINE_SCHEMA
, ROUTINE_NAME
, ROUTINE_TYPE
FROM
INFORMATION_SCHEMA.ROUTINES
WHERE
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) + '.' + QUOTENAME(ROUTINE_NAME)), 'IsMSShipped') = 0
</pre>
<asp:DataGrid id="dgRoutines" runat="server" BackColor="#eeeeee"
HorizontalAlign="Center"
Font-Name="Verdana" CellPadding="3"
Font-Size="8pt"
Headerstyle-BackColor="lightgray"
Headerstyle-Font-Size="10pt"
Headerstyle-Font-Style="bold" />
</asp:panel>
<asp:panel id="pnlRoutineParams" visible="false" EnableViewState="false" runat="server" style="display: none;">
</asp:panel>
<asp:panel id="pnlKeyColumnUsage" visible="false" EnableViewState="false" runat="server" style="display: none;">
<br />
<pre>
SELECT
CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
ORDER BY
TABLE_NAME, ORDINAL_POSITION
</pre>
<asp:DataGrid id="dgKeyColumnUsage" runat="server" BackColor="#eeeeee"
HorizontalAlign="Center"
Font-Name="Verdana" CellPadding="3"
Font-Size="8pt"
Headerstyle-BackColor="lightgray"
Headerstyle-Font-Size="10pt"
Headerstyle-Font-Style="bold" />
</asp:panel>
</asp:Panel>
</form>
<br />
<Dlp:CodeViewer CodeFileName="default.aspx" CodeFileType="Inline" runat="server" />
<Dlp:CodeViewer CodeFileName="default.aspx.cs" CodeFileType="CodeBehind" runat="server" />
</body>
</html>
ASPX CodeBehind: |
using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Configuration;
public class exampleViews : System.Web.UI.Page
{
protected DropDownList ISView;
protected DataGrid ViewLayout;
protected DataGrid dgTables;
protected DataGrid dgColumns;
protected DataGrid dgColumns1;
protected DataGrid dgRoutines;
protected DataGrid dgKeyColumnUsage;
protected Panel mainPanel;
protected Panel pnlTables;
protected Panel pnlColumns;
protected Panel pnlRoutines;
protected Panel pnlRoutineParams;
protected Panel pnlKeyColumnUsage;
protected Button ShowSelfJoin;
protected HtmlGenericControl detailDiv;
private System.String connectionString = ConfigurationSettings.AppSettings["connectionString"];
protected void Page_Load(System.Object Src, System.EventArgs E)
{
if (!Page.IsPostBack)
{
this.BindDropDownList();
this.mainPanel.Visible = false;
}
else
{
this.mainPanel.Visible = true;
}
}
private void BindDropDownList()
{
this.BindDataGrid("SELECT '' [ViewNames] UNION ALL SELECT QUOTENAME(user_name(schema_id))+'.'+QUOTENAME(name) [ViewNames] FROM sys.all_views WHERE user_name(schema_id) = N'INFORMATION_SCHEMA'", ISView);
}
private void BindDataGrid(System.String sql, DropDownList ddl)
{
SqlConnection conn = new SqlConnection(this.connectionString);
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
ddl.DataSource = cmd.ExecuteReader(CommandBehavior.CloseConnection);
ddl.DataBind();
}
private void BindDataGrid(System.String sql, DataGrid dg)
{
SqlConnection conn = new SqlConnection(this.connectionString);
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
dg.DataSource = cmd.ExecuteReader(CommandBehavior.CloseConnection);
dg.DataBind();
}
protected void ISView_SelectedIndexChanged(System.Object Sender, System.EventArgs e)
{
System.String viewName = this.ISView.SelectedItem.Value;
if(viewName == null || viewName.Length == 0)
{
this.mainPanel.Visible = false;
return;
}
this.PopulateViewInformation(viewName);
/*
The following should also come from a db instead of here. Actually, from stored procedures
*/
switch(viewName)
{
case "[INFORMATION_SCHEMA].[TABLES]" :
detailDiv.Attributes.Add("level2ID", "pnlTables");
this.BindDataGrid("SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES WHERE OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') IS NULL", dgTables);
this.pnlTables.Visible = true;
break;
case "[INFORMATION_SCHEMA].[COLUMNS]" :
detailDiv.Attributes.Add("level2ID", "pnlColumns");
this.BindDataGrid("SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE, DATETIME_PRECISION, CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, CHARACTER_SET_NAME, COLLATION_CATALOG, COLLATION_SCHEMA, COLLATION_NAME, DOMAIN_CATALOG, DOMAIN_SCHEMA, DOMAIN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'authors'", dgColumns);
this.BindDataGrid("SELECT TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE, IDENT_SEED(TABLE_NAME) [SEED], IDENT_INCR(TABLE_NAME) [INCREMENT] FROM INFORMATION_SCHEMA.COLUMNS WHERE OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0 AND COLUMNPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), COLUMN_NAME, 'IsIdentity') = 1", dgColumns1);
this.pnlColumns.Visible = true;
break;
case "[INFORMATION_SCHEMA].[ROUTINES]" :
detailDiv.Attributes.Add("level2ID", "pnlRoutines");
this.BindDataGrid("SELECT ROUTINE_CATALOG, ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE FROM INFORMATION_SCHEMA.ROUTINES WHERE OBJECTPROPERTY(OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) + '.' + QUOTENAME(ROUTINE_NAME)), 'IsMSShipped') = 0", dgRoutines);
this.pnlRoutines.Visible = true;
break;
case "[INFORMATION_SCHEMA].[KEY_COLUMN_USAGE]" :
detailDiv.Attributes.Add("level2ID", "pnlKeyColumnUsage");
this.BindDataGrid("SELECT CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0 ORDER BY TABLE_NAME, ORDINAL_POSITION", dgKeyColumnUsage);
this.pnlKeyColumnUsage.Visible = true;
break;
case "[INFORMATION_SCHEMA].[ROUTINE_COLUMNS]" :
detailDiv.Attributes.Add("level2ID", "pnlRoutineParams");
this.pnlRoutineParams.Visible = true;
break;
default :
break;
}
}
private void PopulateViewInformation(System.String viewName)
{
SqlConnection conn = new SqlConnection(this.connectionString);
SqlCommand cmd = new SqlCommand("dbo.GetInformationSchemaDescription", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("RETURN", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add("@tblName", SqlDbType.NVarChar, 384).Value = viewName;
SqlDataAdapter da = new SqlDataAdapter(cmd);
conn.Open();
DataSet ds = new DataSet();
da.Fill(ds, "ViewLayout");
conn.Close();
conn = null;
this.ViewLayout.DataSource = ds.Tables["ViewLayout"];
this.ViewLayout.DataBind();
}
} //exampleViews