glennkerr
asked on
How to list all tables in a database?
How do I list all the tables in a database?
I'd like to cycle through all the tables in a database and push the contents into an ado.net dataset for export to an XML file.
This would be for a user backup - later to be re-inserted into the db if they so desire.
I'd like to cycle through all the tables in a database and push the contents into an ado.net dataset for export to an XML file.
This would be for a user backup - later to be re-inserted into the db if they so desire.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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"
"Create a method for the user to backup a SQL Server db"
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.
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.
-- another way
EXEC sp_MSForEachTable 'select * from ? for xml auto'
EXEC sp_MSForEachTable 'select * from ? for xml auto'
>> 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.
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.
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!
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!
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
You lost me ala_frosty - what's EBCDIC?
ASKER
Ahh - returns systables & views as well - very nice.
ASKER
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.
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.
>> 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.
Yes, Scott, you are correct. I should have used a better example.
Very few people in the PC development world are familiar with EBCDIC and those that are, have had a need to interface with an IBM mainframe.
I stand corrected.
Very few people in the PC development world are familiar with EBCDIC and those that are, have had a need to interface with an IBM mainframe.
I stand corrected.
ASKER
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?