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:
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 <table_name> GO
Listing All Columns
SELECT name, object_name(id) FROM sysindexes
Determining DB Size
EXEC sp_spaceused [tablename] GO