Loop through tables - 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 2005 Forums

Transact-SQL (2005)

Loop through tables

Author

Topic

Clummzie
Starting Member

2 Posts

Posted - 2010-02-09 : 05:13:14

Hi,

Pretty sure I'm making a right meal out what's probably quite a simple problem so here goes!

I have a list of table names (simple select) and I need to check each table (they're identical in structure) to see if the email column contains a particular address and if so return that table name.

Without actually posting the entire stored procedure (should I?) I've set up a while loop based on the table names and then execute sp_executesql (cos the table name is a variable) to check if the passed email address exists.

Trouble is, this doesn't tell me the table name or I can't return it.

Does the above make any sense?
Am I going about this completely the wrong way?

Any help gratefully received

Edited to show:

DECLARE @Email varchar(100)
DECLARE @DbTable varchar(30)
DECLARE @recordCount int
DECLARE @RowNum int
DECLARE @TableName varchar(30)
DECLARE @sql nvarchar(200)

SET @Email = 'myaddress@domain.com'
SET @RecordCount = (SELECT COUNT(*) FROM TABLE_LIST)
SET @RowNum = 0

--set up loop
SELECT TOP 1 @DbTable=DbTable FROM TABLE_LIST

WHILE @RowNum < @recordCount
BEGIN
SET @RowNum = @RowNum + 1
SET @TableName = @DbTable

SET @sql = 'SELECT EMAIL FROM ' + quotename(@TableName) + ' WHERE EMAIL = @Email'
EXEC sp_executesql @sql , N'@Email varchar(100)', @Email = @Email

--next row
SELECT TOP 1 @DbTable=DbTable FROM TABLE_LIST
WHERE DbTable > @DbTable
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-09 : 05:15:13

see

http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-02-09 : 06:42:53

Sounds like a pretty bad design decision. Do you really have dynamic table names?

Anyway -- here's a script hacked from a cross db script that should work. Will at least give you the info in one place rather than an unknown number of select statements.



DECLARE @sql NVARCHAR(MAX)
DECLARE @tableName VARCHAR(255)
DECLARE @email VARCHAR(255)

SET @email = 'THEADDRESS@DOMAIN'

IF OBJECT_ID('tempDb..#results') IS NOT NULL DROP TABLE #results
CREATE TABLE #results (
	[tableName] VARCHAR(255)
	, [email] VARCHAR(255)
	)

SET @sql = N'
INSERT #results ([tableName], [email])
	'

SELECT @sql = @sql + N'SELECT
		' + QUOTENAME([DbTable], '''') + ', [EMAIL]
	FROM
		' + QUOTENAME([dbTable]) + '
	WHERE
		EMAIL = @email

	UNION ALL '
FROM
	TABLE_LIST
ORDER BY
	[dbTable]

SET @Sql = LEFT(@sql, LEN(@sql) - 11)
	
--PRINT @sql
EXEC sp_ExecuteSql
	@sql
	, N'@email VARCHAR(255)'
	, @email

SELECT * FROM #results

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Clummzie
Starting Member

2 Posts

Posted - 2010-02-09 : 08:20:49

Thanks very much for the replies.

Charlie - the tables names are generated by some 3d party software, 1 for each mailing list so nothing to do with me!

Will try your script later.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources