Click here to Skip to main content
Click here to Skip to main content
Go to top

Browsing MS SQL Server Desktop Engine using Information Schema

, 14 Sep 2003
Rate this:
Please Sign up or sign in to vote.
Browse MS SQL Server Information Schema Catalog

Introduction

The NET Framework SDK QuickStarts tutorials illustrate how to manipulate a SQL Server database using SQL and ADO.NET. In order to use these samples you have to install MSDE (Microsoft SQL Server 2000 Desktop Engine ) The engine comes with "OSQL" console command query tool. In demo you will find GUI query tool for MSDE . There is another article that deals with GUI such as MSDE-GUI. My article shows how to browse property of database or SQL server using command query and information schema catalog. Some of user interface for the tool is borrowed from Visual Interdev in a database connection project. The demo shows tree view with node holding a database and their tables/fields, views and stored procedures. The user interface uses DataGrid for query results while the database manipulation is done with SqlConnection, SqlCommand and SqlDataReader.

Hardware & Software Environments

I installed .NET Framework 1.1 and MSDE on Windows Me. I use free IDE from SharpDevelop and I am very glad they made it. That is why you will not see familiar icon for VS.NET in zip. And bit of advice, do not forget to write your own admin password when you install MSDE with the following command:

Setup.exe /qb+ INSTANCENAME=NetSDK
DISABLENETWORKPROTOCOLS=1 SAPWD=<YOUR SA PASSWORD> 

If you do not write your password you will not be able to access your server and you will have to reinstall MSDE.

Design

The design goal is to minimize calls to the database, plus to implement visitor pattern. The database holds list of tables, stored procedures, views, permissions etc. These properties of the database are classes in the class diagram below except permission property. The database class (CMDatabase) opens and holds a connection as well as arrays of tables, views and stored procedures. All database calls are in this class except that tables populate fields into own array. In this way browsing property does not required further access to database. Visitor pattern allows related query methods put in single class as shown below. This diagram is for illustration purpose and does not include all classes, their methods and members.

Classes and Relationship

Visitor Pattern

// MainForm
CView v = (CView) node.Tag;
CQueryBuilderVisitor q = new CQueryBuilderVisitor(); 
v.accept(q);
txtQuery.Text = q.GetQuery();

//CQueryBuilderVisitor
public class CQueryBuilderVisitor : CVisitor
{
  public override void visit(CView v) {
  m_query =v.GetDefinition();
  } 

  public override void visit(CTable t) {
  m_query = " SELECT * FROM [" + t.GetName() + "]";
  } 

// etc...


// CView
public void accept(CVisitor v) { 
v.visit(this);
}

//CVisitor
public abstract class CVisitor
{
public abstract void visit(CField f);
public abstract void visit(CTable t);
public abstract void visit(CView v);
public abstract void visit(CStoredproc sp);
}

Using Demo

GUI image shows in enough details how to use the tool. When the program starts, it adds in text area self explanation connection string where you add your own server name, user name etc. When you finish your connection string, click on connect to connect to server. The program opens connection and browses schema for properties. Then, it listed them into tree view. You can select a node in the tree to create query or open definition for the view or stored procedures. To execute your query click ! button.

Implementation

There are many ways to explore MS SQL. Using information schema property listed below and SqlCommand query you can lists almost everything that you need to know about your databases in SQL server. The list looks like this:

CHECK_CONSTRAINTS Holds information about constraints in the database
COLUMN_DOMAIN_USAGE Identifies which columns in which tables are user-defined datatypes
COLUMN_PRIVILEGES Has one row for each column level permission granted to or by the current user
CHECK_CONSTRAINTS Holds information about constraints in the database
COLUMNS Lists one row for each column in each table or view in the database
CONSTRAINT_COLUMN_USAGE Lists one row for each column that has a constraint defined on it
CONSTRAINT_TABLE_USAGE Lists one row for each table that has a constraint defined on it
DOMAIN_CONSTRAINTS Lists the user-defined datatypes that have rules bound to them
DOMAINS Lists the user-defined datatypes
KEY_COLUMN_USAGE Lists one row for each column that's defined as a key
PARAMETERS Lists one row for each parameter in a stored procedure or user-defined function
REFERENTIAL_CONSTRAINTS Lists one row for each foreign constraint
ROUTINES Lists one row for each stored procedure or user-defined function
ROUTINE_COLUMNS Contains one row for each column returned by any table-valued functions
SCHEMATA Contains one row for each database
TABLE_CONSTRAINTS Lists one row for each constraint defined in the current database
TABLE_PRIVILEGES Has one row for each table level permission granted to or by the current user
TABLES Lists one row for each table or view in the current database
VIEW_COLUMN_USAGE Lists one row for each column in a view including the base table of the column where possible
VIEW_TABLE_USAGE Lists one row for each table used in a view
VIEWS Lists one row for each view

For example: to query views in Northwind database we can use the following command:

SELECT TABLE_NAME,VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS
  WHERE TABLE_CATALOG='Northwind' AND TABLE_NAME NOT IN 
  (SELECT NAME FROM MASTER.DBO.sysobjects)

Disclaimer

The idea for install .NET and free IDE on 98/ME comes from someone who wrote article here "How to install NET SDK on 98". The credit goes to him.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

oleg.vidovic
Web Developer
Canada Canada
I have been in IT industry for 15 years. Prior to move to Regina SK, I had worked in Vancouver for several custom software development companies.

Comments and Discussions

 
Generallisting the table names in my c# program Pinmembernomsnoms27-Apr-06 22:12 
GeneralException Pinmembertlongman15-Nov-04 20:32 
GeneralRe: Exception Pinmemberoleg.vidovic5-Dec-04 14:22 
GeneralVisitor directory missing Pinmembertlongman15-Nov-04 19:54 
GeneralRe: Visitor directory missing Pinmemberoleg.vidovic5-Dec-04 14:11 
GeneralAlternative Approach PinmemberPhil Bolduc25-Nov-03 11:13 
GeneralWeb Data Administrator PinmemberGunmen15-Sep-03 7:09 
GeneralGraphics PinmemberRay Cassick15-Sep-03 2:23 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140916.1 | Last Updated 15 Sep 2003
Article Copyright 2003 by oleg.vidovic
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid