Loop through table of titles - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Loop through table of titles

Author

Topic

joseph.nowak
Starting Member

5 Posts

Posted - 2006-12-27 : 11:13:36

I have a long list of tables that I am running a working block of code on. As of now, I have to copy the code block and insert the the table's title; meaning I have several hundred blocks of identical code with the only change being which table the query is applied to. Is there a way to loop through a table containing all relavent tables and apply the code block to each? I know how to do this in several other OO languages like Java/C++, but I'm unsure about Microsoft SQL Server.

Thanks,

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-12-27 : 11:59:08

You can get the list of tables by querying INFORMATION_SCHEMA.Tables

You can use dynamic SQL to execute your code, for example

declare @sql nvarchar(4000), @MyTable nvarchar(100)
set @MyTable = N'pubs.dbo.authors'
set @sql = N'SELECT * FROM ' + @MyTable
exec sp_executesql @sql

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-12-27 : 12:03:44

You can declare a cursor on the table that holds the table names, FETCH individual rows from the cursor in a WHILE loop, and execute the needed code for each row in the cursor.

The DECLARE CURSOR subject in SQL Server Books Online shows an example of how to do this.

CODO ERGO SUM

joseph.nowak
Starting Member

5 Posts

Posted - 2006-12-28 : 10:29:54

quote:
Originally posted by snSQL

You can get the list of tables by querying INFORMATION_SCHEMA.Tables

You can use dynamic SQL to execute your code, for example
declare @sql nvarchar(4000), @MyTable nvarchar(100)
set @MyTable = N'pubs.dbo.authors'
set @sql = N'SELECT * FROM ' + @MyTable
exec sp_executesql @sql

Thanks, that seems to be working. Out of curiosity, what does the "N" do?

Thanks,

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-28 : 10:33:13

"N" instructs SQL Engine to treat text after that as unicode string.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2006-12-28 : 18:26:47

If the code is identical except for the table name, you might want to rethink your design a bit... why can't all of this information be in a single table?

--Jeff Moden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-01-01 : 02:56:18

One of the followings

1 Normalization - http://www.datamodel.org/NormalizationRules.html
2 Dynamic SQL - http://www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

joseph.nowak
Starting Member

5 Posts

Posted - 2007-01-05 : 09:37:59

Unfortunately I wasn't involved in the database design; but as someone made me realize there are a few key differences in many of the tables that make it impossible to use one identical query on every table. Also, I came across this article in my research that suggests my method can be done but is a bad idea in Dynamic SQL or in practice for any query.

[url]http://www.sommarskog.se/dynamic_sql.html[/url]

Thanks for everyone's help: if nothing else I've learned quite a bit about SQL in this process!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources