Analyzing A Remote Microsoft SQL Database

Feb 222011

Introduction

Suppose you have access to a remote MS-SQL database, and you need to analyze it. Why? Let’s pretend that you intend to migrate the contents to another server or database, like MySQL. 😉 Anyway, how do you discover various essentials about the database? … These instructions are for Microsoft SQL Server 2000. The following instructions and results will probably vary wildly for other versions of MS-SQL.

Connect with TSQL

First, let’s connect to the remote MS-SQL using the tsql command line client:

tsql -S <sql_server_name> -U <user_name>

You may have another favorite method to gain command line access to the SQL database, but this is my current favorite. 🙂

Listing All The Tables in the Database

In our open SQL CLI, one site posted doing something like:

EXEC sp_tables
GO

Unfortunately, this does not list all the tables for some unknown reason. I could see several more tables listed in MS SQL Enterprise Manager. Most of my “user” tables were not reported by the above method, although some were reported correctly.

Another site indicated listing special system table contents, which I think is actually specific to ORACLE and newer versions of MS-SQL. Maybe it will work for you?

SELECT * FROM sys.tables
GO

The same site also suggested the following, but it also returned partial results for some reason unknown to me.

SELECT * FROM INFORMATION_SCHEMA.TABLES
GO

Others recommend using “sp_help” with no arguments, like so:

sp_help
GO

However, I found that it returned far too much information, much more than just the list of all tables, although that did seem to be included…

Ultimately, I used this:

SELECT * FROM sysobjects WHERE TYPE='s' OR TYPE='u' ORDER BY NAME
GO

This returned a filtered list of system objects that had a type of either “system” or “user” table. Very nice! 😀

Listing Table Structure

The structure of a particular structure can be listed, like so:

sp_help &lt;table_name&gt;
GO

Listing All Columns

Try this:

SELECT name, object_name(id) FROM sysindexes

Determining DB Size