2

EDIT2: Found a fix! I used the number of the desired schema instead of the name. Should've thought of that before, really! And i think the error messages could've been a bit better aswell. Thanks for all your time!

How can i get the names of all tables inside a database through sql inside asp classic? The server is running windows 2008, iis7.5 and microsoft jet. I've tried all the querys i could find on the internet (and here) but none have worked.

If i add a ; to the query to run a set of querys at the same time it gives me an error because the statement isn't over at the semicolon.
The master.mdf database cannot be accessed because it's of unknown format.
The sysobjects variable apparently doesn't exist.
I am using mssql 2000 format. (.mdf)
The connection is made through classic asp with the Microsoft.Jet.OLEDB.4.0 provider and ADODB connection/recordset.

How do I get list of all tables in a database using TSQL?
Query to get the names of all tables in SQL Server 2008 Database

EDIT: I've found two folders containing databases. One is in C:\program files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\mssql\binn\templates and contains master.mdf, mastlog.ldf, model.mdf, modellog.ldf, msdbdata.mdf and msdblog.ldf. The other one is also in the \binn\template data directory and contains master.mdf, mastlog.ldf, model.mdf, modellog.ldf, MSDBData.mdf, MSDBLog.ldf, mssqlsystemresource.ldf, mssqlsystemresource.mdf, tempdb.mdf and templog.ldf. Maybe these is of some interest?

How can i tell if i have permission? Does it give a permission denied error?

Please help! No, don't. Read the 2nd edit at the top.

Community
  • 1
  • 1
Filip Haglund
  • 13,919
  • 13
  • 64
  • 113

4 Answers4

2
USE YOUR_DATABASE
GO
SELECT *
FROM sys.Tables
GO
Senad Meškin
  • 13,597
  • 4
  • 37
  • 55
  • Not entirely sure and cannot verify it without a copy of SQL Server 2000, but previously I thought that `sys.*` objects were introduced with SQL Server 2005, and in SQL Server 2000 you would use names like `systables`, `sysobjects` etc. instead. But as I said, I'm not sure. – Andriy M Jun 02 '11 at 19:14
  • 1
    Runs, but cannot find `C:\Windows\SysWOW64\inetsrv\sys.mdb` :/ – Filip Haglund Jun 02 '11 at 22:41
1

Have you tried the example from:

http://www.kamath.com/codelibrary/cl002_listtables.asp

gpresland
  • 1,690
  • 2
  • 20
  • 31
  • I cannot use `DNS=Library` and the provider i usually use doesn't support that and gives me `ADODB.Connection error '800a0cb3'` – Filip Haglund Jun 02 '11 at 22:33
1

I almost always use the INFORMATION_SCHEMA views:

SELECT * FROM INFORMATION_SCHEMA.TABLES

If this isn't working for you, the SQL user your site is running under may not have access to the system objects. This is actually a good thing, as giving your site access to the underlying database schema can leave you vulnerable to SQL injection.

So if you do go this route, proceed with caution.

Jerad Rose
  • 15,235
  • 18
  • 82
  • 153
0

The mdf by itself is useless: you need a database engine (a.k.a. a SQL Server instance) to "run" it. As I understand the question, this is your problem.

Then you can use sysobjects in your database: unless you have added your tables to the master database

There is no practical way to use an mdf directly: if nothing else download MSDE

gbn
  • 422,506
  • 82
  • 585
  • 676