Click here to Skip to main content
13,454,623 members (56,330 online)
Click here to Skip to main content
Add your own
alternative version


47 bookmarked
Posted 14 Sep 2003

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


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

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


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(); 
txtQuery.Text = q.GetQuery();

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) { 

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.


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_CONSTRAINTSHolds 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_CONSTRAINTSHolds information about constraints in the database
COLUMNSLists 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:



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.


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


About the Author

Software Developer (Senior)
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.

You may also be interested in...


Comments and Discussions

Generallisting the table names in my c# program Pin
nomsnoms27-Apr-06 22:12
membernomsnoms27-Apr-06 22:12 
GeneralRe: listing the table names in my c# program Pin
oleg.vidovic30-Nov-17 5:11
memberoleg.vidovic30-Nov-17 5:11 
GeneralException Pin
tlongman15-Nov-04 20:32
membertlongman15-Nov-04 20:32 
GeneralRe: Exception Pin
oleg.vidovic5-Dec-04 14:22
memberoleg.vidovic5-Dec-04 14:22 
GeneralVisitor directory missing Pin
tlongman15-Nov-04 19:54
membertlongman15-Nov-04 19:54 
GeneralRe: Visitor directory missing Pin
oleg.vidovic5-Dec-04 14:11
memberoleg.vidovic5-Dec-04 14:11 
GeneralAlternative Approach Pin
Phil Bolduc25-Nov-03 11:13
memberPhil Bolduc25-Nov-03 11:13 
Although I like using the INFORMATION_SCHEMA.* views when I am using Query Analyser and writing other SQL Server specific scripts, there is an other way of accomplishing what you are doing.

By using the ADO.NET provided OleDbConnection.GetOleDbSchemaTable method, you can extend this functionality to any OLEDB provider.

See Obtaining Schema Information from a Database from the .NET Framework Developer's Guide

Although determining the resultant schema definition is not straight forward and not documented well in the .NET Framework documentation. Referencing the OLEDB SDK documenation is recommended as well.

Having said that, here is an example of using the INFORMATION_SCHEMA views to retrieve the primary key for a given table. It is not as straight forward as you might have thought.
         ON key_column_usage.COLUMN_NAME = columns.COLUMN_NAME
        AND key_column_usage.TABLE_NAME = columns.TABLE_NAME
        AND key_column_usage.TABLE_SCHEMA='dbo'
 WHERE columns.TABLE_NAME = @TableName
                 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS table_constraints
                WHERE table_constraints.TABLE_NAME = columns.TABLE_NAME 
                  AND table_constraints.TABLE_SCHEMA = key_column_usage.TABLE_SCHEMA 
                  AND table_constraints.CONSTRAINT_TYPE = 'PRIMARY KEY'
                  AND table_constraints.CONSTRAINT_NAME = key_column_usage.CONSTRAINT_NAME)
     ORDER BY key_column_usage.ORDINAL_POSITION

More examples of using INFORMATION_SCHEMA views can be found in the source of LLBLGen, the data-access tier generator

GeneralWeb Data Administrator Pin
Gunmen15-Sep-03 7:09
memberGunmen15-Sep-03 7:09 
GeneralGraphics Pin
Ray Cassick15-Sep-03 2:23
memberRay Cassick15-Sep-03 2:23 

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

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

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01-2016 | 2.8.180321.1 | Last Updated 15 Sep 2003
Article Copyright 2003 by oleg.vidovic
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid