How to list all tables in a database?

You can list all the [user] tables in a db like so:

SELECT name
FROM sysobjects WITH (NOLOCK)
WHERE xtype = 'U'

Btw, unless the db is extremely large, it's much easier just to backup the db, restore it to a different name, then copy the table(s) desired back to the original db.

Assisted Solution

10.20.2005 at 03:46PM PDT, ID: 15129218

Rank: Genius

Lowfatspread:

use the information_schema.tables view in preference to sysobjects...

e.g.
select * from information_schema.tables
where type = 'base Table'

but I agree with scott why aren't you just using a database backup...

also prbably a better solution if you want just a few tables to export/re-import consider generating text files via BCP to unload/reload.

Accepted Solution

10.20.2005 at 04:25PM PDT, ID: 15129359

I'm not using a db backup because when we go to production, the user account will not have rights to create a db on the production server. I do agree it is much better - that's what I use now while debugging.

The idea is that the user wants to beable to preserve a state, then run various scenarios while returning to the preserved state in between each run. The database starts at 2 Mb, but will not flatten out until approx 300 Mb over 5 years.

I don't like the direction I'm going at the moment, as it won't scale very nice...
Backup - Load db into an ADO.NET DataSet, then save to local XML file
Restore - Load XML file to the DataSet (already takes 20 seconds for 10 Mb), then replace all records in db.

This is turning into another question. I'll award points on this one (as it's been answered) and repost the backup question.

Lowfatspread - why is information_schema.tables the prefered object?

10.20.2005 at 04:31PM PDT, ID: 15129383

I just posted the Backup question in Home|All Topics|Programming Languages|.NET|VB.NET
"Create a method for the user to backup a SQL Server db"

10.20.2005 at 06:05PM PDT, ID: 15129745

Rank: Master

information_schema.tables is preferred because that syntax will work with SQL Server 2005 and using systables will break!

Well, I think we SQL people trump your VB people for backup purposes. Now, what you've described the "backup" with the "running various scenarious in a preserved state" which is decidedly not the sort of thing you want to be doing with a backup, it's the sort of thing you want to be doing with "snapshots" of the data. This is something that ORACLE does very well, and SQL-server kinda sucks at.

I have a couple of ideas.

1. Create a job that periodically creates a new database with the date of the "snapshot" and give the user read-only rights to it. It's not going to make your database pretty, and it'll take up great gobs of space, but if that's what the client wants...

2. If you can get the client to tell you exactly what they want to do with these snapshots, an even better solution, though more work, would be to build a set of snapshot tables into which you could insert only the pertinent data and the date of the snapshot. Unfortunately, the customer is always right (as long as they pay you) .. if they insist on you giving them an EBCDIC export, then that's what you get paid for ... not telling them that nobody uses EBCDIC and that they'll regret the decision 20 minutes after you hand them the finished work.

10.21.2005 at 04:05AM PDT, ID: 15131246

Rank: Guru

ill:

-- another way
EXEC sp_MSForEachTable 'select * from ? for xml auto'

10.21.2005 at 06:41AM PDT, ID: 15132216

Rank: Genius

>> information_schema.tables is preferred because that syntax will work with SQL Server 2005 and using systables will break! <<

That's not true! As I understand it, sysobjects will be a valid view in SQL 2005 and should continue working.

Also, requestor wanted to list *ALL* tables. INFORMATION_SCHEMA will only show tables **that user have permissions to**, so won't necessarily list *all* tables.

10.21.2005 at 06:47AM PDT, ID: 15132273

Rank: Master

Of course you don't have to believe me, but you could at least do some research before calling me a liar. Here's what Microsoft has to say about shema objects in Sql-Server 2005:

http://msdn2.microsoft.com/en-us/library/ms190324

Instead of sysobjects, you will have sys.objects. There's clearly a difference that will break code!

10.21.2005 at 07:01AM PDT, ID: 15132376

Rank: Genius

Nothing a global replace can't fix!

Btw, I did not call you a liar. Yes, I said that I thought what you posted was not true, but that could be because you were uninformed or misinformed. Being a liar requires *intent* to deceive, which I did not imply.

I don't have time to review the link now, but it could well be that *I was the one that was misinformed* (I mistakenly relied on someone else's word not my own research). So I apologize.

10.21.2005 at 07:44AM PDT, ID: 15132717

Rank: Master

Thank you for the apology. Accepted.

Yes, a global replace would probably take care of it. My point was that deploying Sql-Server 2000 T-SQL code to 2005 out of the box will break the code for anyone using sysobjects or making any reference to _anything_ in the master db. There's obviously more that will need to go on in development-land to port products from 2000 to 2005, but everything we can do, knowing what we do, to ease the transition will be helpful.

My MS complaint is that the Information_schema.Schemata view exists in 2005, but is completely revised and will break code. I think they should've left that in place as it was and created a new information_schema view. That opinion's probably not even worth 2¢.

Anyway, so that we're not just engaging in a discussion:

combining the info from above:

EXEC sp_MSForEachDb 'select TABLE_CATALOG, TABLE_NAME from [?].INFORMATION_SCHEMA.TABLES'

Assisted Solution

10.21.2005 at 09:41AM PDT, ID: 15133752

You lost me ala_frosty - what's EBCDIC?

10.21.2005 at 09:55AM PDT, ID: 15133867

Ahh - returns systables & views as well - very nice.

10.21.2005 at 09:58AM PDT, ID: 15133901

Just needed the select statement for one database on the server (not all databases) - but thanks anyways - didnt know that existed and I'm sure it will come in handy some day.

10.21.2005 at 10:04AM PDT, ID: 15133943

Rank: Genius

EBCDIC (pronounced "eeb-sah-dick") is a format used on mainframes (and I think AS/400s) to store data. It is different from the ASCII-based format used on PCs, servers, and UNIX.

>> not telling them that nobody uses EBCDIC <<

70% of the world's business data is still stored in mainframes, i.e., more business data is in EBCDIC than any other format. Clearly a lot of people are still using it.

10.21.2005 at 10:59AM PDT, ID: 15134301

Rank: Master