19

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jeff
  • 2,794
  • 8
  • 30
  • 35

3 Answers3

35

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.

Jeremy Smyth
  • 23,270
  • 2
  • 52
  • 65
  • Thank you, this is precisely what I was looking for. – Jeff Aug 24 '09 at 18:32
  • 6
    `SELECT * FROM INFORMATION_SCHEMA.COLUMNS` is very useful. It provides details of the columns along with table name. – KSK Jun 12 '14 at 13:19
6

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

pavium
  • 14,808
  • 4
  • 33
  • 50
  • 3
    They're not :) There are a few other ways, like `SELECT * FROM sys.objects WHERE type='U'` and so on, but no `SHOW TABLES` in MS SQL, sadly. – Jeremy Smyth Aug 23 '09 at 11:31
0

SchemaSpy http://schemaspy.sourceforge.net/ is a great tool for analyzing existing databases. It generates html lists of table and constraints as well as a graphical representation of relationships

JamesP
  • 602
  • 6
  • 12