Metadata structure in Microsoft and Sybase DBMS

Not so long ago, we announced a free SQL Dynamite utility for searching meta information in databases. We want to thank everyone for the good responses, criticism, errors found.
We have released an update for you, fixed the errors found, and added support for SQLite and Informix .

New version is available here .

In the last article, we promised to consider access to the database meta-information. Today let's talk about MS SQL and Sybase.

The process of searching and analyzing the connections of various objects in Microsoft SQL Server, Sybase Adaptive Server Enterprise, Sybase Anywhere and SQL Azure DBMS primarily consists of working with metadata.
')
The problem is that Sybase ASE and MSSQL, initially having the same metadata structure, have come a long way of development independently of each other. Sybase Anywhere was originally Watcom SQL and had a lot of differences from Sybase ASE, and SQL Azure is generally a cloud-based DBMS. Nevertheless, the structure of metadata in them is in many respects similar, in particular, they have the same table names containing key information about the objects (sysobjects, syscomments and syscolumns).

This article discusses the metadata structure of the following versions of DBMS:

MSSQL 2005, Sybase ASE 15.7, Sybase Anywhere 12.0.1 and SQL Azure, the current version.

0) Common to all database tables and stored procedures

Tables:

1. sysobjects

It is used to store information about all objects in the system (ID, object name, object type, and other parameters).
The main table for working with metadata.

2. syscomments

Stores the text of stored procedures, user-defined functions, triggers and views (views).

3. syscolumns

Stores information about the columns of each table (name, type, etc.).

Stored procedures:

4. sp_columns

A stored procedure that displays information about the columns of a table by its name.

1) Tables and stored procedures specific to several DBMS:

Tables / views:

1. sysconstraints

(MSSQL & Sybase ASE) Stores information about primary keys, foreign keys and similar objects (unique keys, checks, defaults).

2. sys.sysindexes (view) or sysindexes (table)

(MSSQL & Sybase ASE & Sybase Anywhere) Stores index information.

3. sys.sysforeignkeys

(MSSQL & Sybase Anywhere) Stores foreign key information.

Stored procedures:

4. sp_helpindex

(MSSQL & SQL Azure & Sybase ASE) The stored procedure returns information about the indexes of the table by its name.

5. sp_helpconstraint

(MSSQL & Sybase ASE) A stored procedure that displays information about the external and primary keys of a table by its name.

Tables and stored procedures specific to a Microsoft DBMS:

2) MSSQL

1. sp_MShelpcolumns

This is a MSSQL-specific (as the name implies) stored procedure that provides detailed information about the columns of a table by its name.

2. sp_MStablekeys

This is a MSSQL specific (as the name implies) stored procedure that provides detailed information about the primary and foreign keys of a table by its name.

3. sp_MStablechecks

This is a MSSQL specific (as the name implies) stored procedure that provides detailed information about the limitations of the table by its name.

3) MSSQL + SQL Azure

SQL Azure, as well as Microsoft SQL Server (since version 2005), supports INFORMATION_SCHEMA (besides these two DBMSs, this standard is also supported by MySQL and PostgreSQL).

1. sys.indexes

Stores information about indexes and primary keys.

2. sys.sql_modules (only for SQL Azure)

Stores information about triggers.

3. sys.triggers

Like the previous table, it stores information about triggers.

4. sys.foreign_key_columns

Stores information about foreign keys.

In addition, MSSQL (since version 2005) supports such a thing as INFORMATION SCHEMA. In short, this is the standard for storing metadata, which means that the DBMS must have a special set of read-only views that provide information on different types of objects. Examples:

1. information_schema.views

(MSSQL & SQL Azure) Stores information about views.

2. information_schema.routines

(MSSQL & SQL Azure) Stores information about stored procedures and functions.

As we can see, despite significant differences in the structure of metadata, similar approaches can be quite applied (with certain limitations) to analyze the contents of these DBMS. The basic logic of working with them (search for occurrences, DDL generation by metadata) is already implemented in the SQL Dynamite program.