searching in 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)

searching in tables

Author

Topic

8022421
Starting Member

45 Posts

Posted - 2009-06-11 : 14:05:18

there are about 12 tables that have the same column say 'id' and i have an input 'id' value to be searched in these tables say @id.
the task is to search for atleast 1 match of this @id value in these tables without actually writing seperate queries to search for in each table one after the other..Something like the most efficient way to search if there exists atleast 1 value matching in these tables.

shackclan
Starting Member

8 Posts

Posted - 2009-06-11 : 14:26:10

This is written for pre-2008 but give a try:

DECLARE @SQL VARCHAR(2000)
, @PULL_SQL VARCHAR(2000)
, @WHERE_SQL VARCHAR(2000)
, @ID INT

SET @PULL_SQL = 'SELECT * FROM '
SET @WHERE_SQL = ' WHERE '
SET @ID = ??? -- set the value you are searching for

DECLARE gpCsr cursor for
SELECT DISTINCT so.name AS TabName
, sc.name AS ColName
FROM syscolumns sc
INNER JOIN sysobjects so
ON sc.id = so.id
WHERE sc.name LIKE '%ID%' -- where you set the name of the column you are searching for
AND so.type = 'U'
ORDER BY so.name

DECLARE
@TABLE NVARCHAR(255)
, @COLUMN NVARCHAR(255)

set nocount on
OPEN gpCsr
FETCH NEXT FROM gpCsr INTO @TABLE
, @COLUMN
WHILE (@@fetch_status <> -1)
BEGIN

IF (@@fetch_status <> -2)
BEGIN
-- 1st: PULL DATA
SET @SQL = @PULL_SQL + @TABLE + @WHERE_SQL + @COLUMN + ' = ' + CAST(@ID AS VARCHAR(50))
EXECUTE(@SQL)
END
FETCH NEXT FROM gpCsr INTO @TABLE
, @COLUMN

END
CLOSE gpCsr
DEALLOCATE gpCsr

Thanks,
shackclan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-14 : 02:57:04

quote:
Originally posted by 8022421

there are about 12 tables that have the same column say 'id' and i have an input 'id' value to be searched in these tables say @id.
the task is to search for atleast 1 match of this @id value in these tables without actually writing seperate queries to search for in each table one after the other..Something like the most efficient way to search if there exists atleast 1 value matching in these tables.

you could use the system stored procedure sp_Msforeachtable for this


EXEC sp_Msforeachtable 'If ''?'' IN (Table1,...TableN) SELECT * FROM ? WHERE id=@id'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-14 : 03:04:06

also see
http://www.databasejournal.com/features/mssql/article.php/3441031/SQL-Server-Undocumented-Stored-Procedures-spMSforeachtable-and-spMSforeachdb.htm

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources