September 12, 2008 at 3:03 pm
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
September 12, 2008 at 9:25 pm
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.
September 13, 2008 at 11:20 am
There are a variety of ways to do this (cursor, while), etc. Depends on what you need to do, but Sp_msforeachtable works.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 13, 2008 at 12:14 pm
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://qa.sqlservercentral.com/articles/Test+Data/61572/
--Jeff Moden
September 13, 2008 at 12:45 pm
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
September 13, 2008 at 2:35 pm
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
September 13, 2008 at 3:04 pm
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'
September 13, 2008 at 3:12 pm
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]
September 13, 2008 at 4:32 pm
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
September 13, 2008 at 5:31 pm
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.
September 13, 2008 at 6:29 pm
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
September 13, 2008 at 6:36 pm
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.
September 14, 2008 at 11:22 am
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.
September 14, 2008 at 11:41 am
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.
September 14, 2008 at 11:43 am
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.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply