Get SQL Server schema via a SQL query?

Jeff picture Jeff · Aug 23, 2009 · Viewed 81.1k times · Source

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

Jeremy Smyth picture Jeremy Smyth · Aug 23, 2009

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.