"For each table in database......"

Question

Post reply

"For each table in database......"

garethmann101

SSCommitted

Points: 1980
More actions
September 12, 2008 at 3:03 pm

#96534

Is there a way to loop through all tables in an SQL database using ADO?
Or simply the names of each table in the database so that you can connect to each one?
i.e.
Connect to database
For each table in database
Do something here (i.e. connect to the table, etc.)
Loop

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Yes there is, can you tell us the do something so we can figure it all out?

In the mean time you can check out sp_msforeachtable and sp_msforeachdb

Very quick example :

EXEC sp_msforeachtable 'PRINT ''?'''

The question mark is a placeholder for the name of the table or the name of the DB.

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 2

There are a variety of ways to do this (cursor, while), etc. Depends on what you need to do, but Sp_msforeachtable works.

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 3

Heh... ACK!... cursors... while loops... sp_MSForEachTable which contains one of the most ineffecient general purpose cursors there will ever be...

Yeaup... they all work. But, in SQL Server 2005, there is another option... since I don't know what you actually want to do for each table in a database, let's just do a simple count of rows for each table... there are much better ways to do that but it'll suffice as an example... 😛

--===== Build commands to execute for all tables...

DECLARE @sql VARCHAR(MAX)

SELECT @sql = COALESCE(@SQL + CHAR(10),'') + 'SELECT COUNT(*) FROM ' + Name

FROM sys.Tables WITH (NOLOCK)

--===== Optional... show the commands created

PRINT @sql

--===== Now, execute all those commands

EXEC (@SQL)

There's also a concatenate trick you can do with XML at the end of the following article which contains information about how this all works as well as some major pitfalls that some folks fall into when concatentating commands. Heh... rumor has it that the author is a pretty decent fellow... 😀

http://www.sqlservercentral.com/articles/Test+Data/61572/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ron McCullough SSC Guru Points: 63877 More actions · Answer 4

If you wish to do this programmatically then use ADOX. This Microsoft site presents the ADOX object model, with links to just about everything else concerning ADOX. By the way I have used ADOX in code to list every table in a DB and every column in each table. It requires surprisingly little code to do so and you do not have to know much if anything about T-SQL or system tables, etc., etc.

http://msdn.microsoft.com/en-gb/library/ms675541.aspx

The above page has this link to code examples in VB and C++

http://msdn.microsoft.com/en-gb/library/ms675844(VS.85).aspx

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 5

True enough... my question is why you'd want to do anything to all tables from outside the database when the database does it so well?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ron McCullough SSC Guru Points: 63877 More actions · Answer 6

True enough... my question is why you'd want to do anything to all tables from outside the database when the database does it so well?

Jeff: I am taking it that the individual who posted the question is not that cognizant of what T-SQL can do from this portion of the question

Or simply the names of each table in the database so that you can connect to each one?

garethmann101 if you want to use ADO (not ADOX), use this T-SQL statement to return all the table names. In code similiar to:

Ado_Cmd.CommandType = adCmdText

Ado_Cmd.CommandText " insert between quotes one of the following T-SQL statements"

The use the ADO_Cmd to open a recordset

SELECT name AS 'Table name', type_desc FROM sys.objects WHERE Type_desc = 'SYSTEM_TABLE' OR Type_desc = 'USER_TABLE'

If all you want is user created tables then use:

SELECT name AS 'Table name', type_desc FROM sys.objects WHERE Type_desc = 'USER_TABLE' AND name <> 'dtproperties'

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

garethmann101 (9/12/2008)
Or simply the names of each table in the database so that you can connect to each one?

SELECT * From INFORMATION_SCHEMA.TABLES

Try it!

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 8

bitbucket (9/13/2008)
True enough... my question is why you'd want to do anything to all tables from outside the database when the database does it so well?
Jeff: I am taking it that the individual who posted the question is not that cognizant of what T-SQL can do from this portion of the question

Heh... understood. But, now that you've said that, it makes you also wonder if they're cognizant of the damage they could cause. If they don't know how to do this simple task in T-SQL, do they understand the Database Engine well enough to keep from killing the server, the data, or the performance using ADOX?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

garethmann101 SSCommitted Points: 1980 More actions · Answer 9

Heh... understood. But, now that you've said that, it makes you also wonder if they're cognizant of the damage they could cause. If they don't know how to do this simple task in T-SQL, do they understand the Database Engine well enough to keep from killing the server, the data, or the performance using ADOX?

[/Quote]

All I am doing is looping through about 100 tables andf creating a stored procedure for each one.

But thank you rbarryyoung, that select from INFORMATION_SCHEMA.TABLES was exactly what I needed.

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 10

garethmann101 (9/13/2008)
Heh... understood. But, now that you've said that, it makes you also wonder if they're cognizant of the damage they could cause. If they don't know how to do this simple task in T-SQL, do they understand the Database Engine well enough to keep from killing the server, the data, or the performance using ADOX?
[/Quote]
All I am doing is looping through about 100 tables andf creating a stored procedure for each one.

Exactly my point.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

What Jeff is trying to say here, is that we may help you there as well. We earn a living at doing that kind of stuff and we may be able to save you a lot of troubles and headaches down the road.

Maybe we could do the work for you if you only told us more about what you need to do exactly.

garethmann101 SSCommitted Points: 1980 More actions · Answer 12

Well thank for the offer, it is appreciated, obviosly I would not want to reveal excatly what I am trying to do before my site is launched, that would be just crazy.

But as an example, the reason I "might" need to loop through 100 tables, is, lets say I have a tables of cities, one for each state. So lets say 50 tables, each with about 10,000 records of each city. When a user selects a city, they will first select the state, this will trigger a stored procedure to select the relevant table to search for cities, then a second drop-down box would be populated with all of the cities in the table, then from there, when the user selects their city, a second stored procedure would be triggered to select the city they need for the relevant table (remember there is one table per state). That is why I need so many stored procedures for each table.

I decided to design it this way instead of having a single huge table of all the cities in the country to save on search times and make it easier for me to deal with the data.

The "cities" is not actually what I am doing, it is an example, that is why I have about 100 tables instead of 50.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 13

With propper indexing 500 000 records is nothing to sweat at.

I have a report that pulls that from 400 000 rows and the 400 pages report is made in less than 1 second.

Unless you have lots more data, that's not the way to do this.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 14

P.S.

I wouldn't want to be stuck either at picking a town from a list of 10 000 names in a combo or listbox. I think you'd be better of using a search GUI to ease off the process. (pick state from combo, then type search query for the town, then pick from the results of that search).

With proper indexes, that will always run in sub seconds on any server.