List table names

Question

I'm in the process of trying to migrate my ASPNET site to Django. All my current data is tied up in MS SQL Server on a shared host that (helpfully) doesn't support python... I could use my existing code to export all the data but I've also stopped using Windows. The site is mainly compiled VB.NET so I'd need to install Windows and Visual Studio and then figure out what I'm trying to do... I don't like that plan.

Rather than go through that headache, I'd like to use PHP to export the entire database (or a table at a time) to JSON. SimpleJSON in Python will make it ludicrously easy to import so it seems like a plan.

So far, so good. In PHP I've managed to connect to the SQL Server and perform simple queries, but I need a list of tables so I know what data I need to copy. I want all the tables because there are legacy tables from when I rewrote the site about three years ago, and I'd like to keep that data somewhere...

So first thing: Does anybody know the SQL query for listing all tables?

I've tried mssql_query('sp_tables'); but this returns a strange list:

mydatabasename
mydatabasename
dbo
dbo
syscolumns
syscolumns
SYSTEM TABLE
SYSTEM TABLE

Secondly: In your opinion, would I be better off writing each table dump to its own .json file or should I keep it all together?

Thirdly: Am I going about this the wrong way?

Answer 1

Select table_name from information_schema.tables

for any given table you can get the metadata with

sp_help tablename

Answer 2

you do query with: SHOW TABLES; (you need to select DB before this)