List table names

1
votes

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?

3
phpsql-server

3 Answers

6
votes
Select table_name from information_schema.tables

for any given table you can get the metadata with

sp_help tablename
1
votes

You do query with :

SHOW TABLES;

(you need to select DB before this.)

0
votes

you can try this

SELECT * FROM information_schema.tables