Get SQL Server schema via a SQL query?

Question

I've inherited a clunky and horribly un-documented site from a bad developer and am trying to get a look at the database schema. Unfortunately the web host is the worst I've ever dealt with and has no control panel capability for viewing the db schema or even exporting tables.

Is there any way that I can get a look at the schema via a SQL query (this would be with ASP + SQL Server)? My end goal here is to see what tables exist, possibly get a SQL dump of the vital tables, and then recreate the whole thing the right way.

Answer 1

The INFORMATION_SCHEMA schema is a good place to start:

SELECT * FROM INFORMATION_SCHEMA.TABLES
SELECT * FROM INFORMATION_SCHEMA.VIEWS

...and so on.

You might also want to have a look at using SMO, an API to get at the metadata in SQL Server.

Answer 2

I'm not sure if simple queries like

SHOW TABLES;
DESCRIBE table_name;
SHOW TABLE STATUS from table_name;

are valid in MS SQL. They would also be useful