Get SQL Server schema via a SQL query?

sql-server asp-classic
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.

Related questions

How to prevent 'query timeout expired'? (SQLNCLI11 error '80040e31')

I have a connection to a MS SQL Server 2012 database in classic ASP (VBScript). This is my connection string: Provider=SQL Server Native Client 11.0;Server=localhost; Database=databank;Uid=myuser;Pwd=mypassword; When I execute this SQL command: UPDATE [info] …

Read More
SQL Server Pre-Login Handshake Acknowledgement Error

We have a production website with a mixture of classic ASP, inline .Net and compiled .Net that talks to a SQL Server instance where both are VMs on the same physical box and everything there works great. In order to …

Read More
"Operation is not allowed when the object is closed" when executing stored procedure

This is my stored procedure, and when I am calling it from my classic ASP code, I am getting the error: Operation is not allowed when the object is closed. when I try to do a record count. Does anyone …

Read More