CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2008sql-server-2008-r2
hysoftwareeng
hysoftwareeng

Reputation: 497

Looping through a column in SQL table that contains names of other tables

I have fairly new to using SQL, currently I have a table that has a column that contains the names of all the tables I want to use for one query, so what I want to do is to loop through that column and go to every single one of these tables and then search one of their columns for a value (there could be multiple values), so whenever a table contains the value, I will list the name of the table. Could someone give me a hint of how this is done? Is cursor needed for this?

Upvotes: 0

Views: 1344

Answers (2)

ZeRaTuL_jF
ZeRaTuL_jF

Reputation: 592

I don't have enough reputation to comment but is the table with the column that contain the table names all in one column, meaning that all the table names are comma separated or marked with some sort of separator? This would cause the query to be a little more complicated as you would have to take care of that before you start looping through your table. However, this would require a cursor, as well as some dynamic sql. I will give a basic example of how you can go about this.

declare @value varchar(50)
declare @tableName varchar(50)
declare @sqlstring nvarchar(100)
set @value = 'whateveryouwant'
declare @getTableName = cursor for
select tableName from TablewithTableNames
OPEN @getTableName
  fetch NEXT
  from @getTableName into @tableName
  while @@FETCH_STATUS = 0
  BEGIN
      set @sqlstring = 'Select Count(*) from ' + @tableName + 'where ColumnNameYouwant = ' + @value
      exec @sqlstring
      If @@ROWcount > 0
         insert into #temptable values (@tableName)
     fetch next
     from @getTableName into @tableName
  END
  select * from #temptable
  drop table #temptable
  close @getTableName
  deallocate @getTableName

I'm currently not able to test this out as for time constraint reasons, but this is how I would go about doing this.

Upvotes: 1

Richard Hansell
Richard Hansell

Reputation: 5403

You could try something like this:

--Generate dynamic SQL
DECLARE @TablesToSearch TABLE (
    TableName VARCHAR(50));
INSERT INTO @TablesToSearch VALUES ('invoiceTbl');
DECLARE @SQL TABLE (
    RowNum INT,
    SQLText VARCHAR(500));
INSERT INTO
    @SQL
SELECT
    ROW_NUMBER() OVER (ORDER BY ts.TableName) AS RowNum,
    'SELECT * FROM ' + ts.TableName + ' WHERE ' + c.name + ' = 1;'
FROM
    @TablesToSearch ts
    INNER JOIN sys.tables t ON t.name = ts.TableName
    INNER JOIN sys.columns c ON c.object_id = t.object_id;

--Now run the queries
DECLARE @Count INT;
SELECT @Count = COUNT(*) FROM @SQL;
WHILE @Count > 0
BEGIN
    DECLARE @RowNum INT;
    DECLARE @SQLText VARCHAR(500);
    SELECT TOP 1 @RowNum = RowNum, @SQLText = SQLText FROM @SQL;
    EXEC (@SQLText);
    DELETE FROM @SQL WHERE RowNum = @RowNum;
    SELECT @Count = COUNT(*) FROM @SQL;
END;

You would need to change the "1" I am using as an example to the value you are looking for and probably add a CONVERT/ CAST to make sure the column is the right data type?

You actually said that you wanted the name of the table, so you would need to change the SQL to:

'SELECT ''' + ts.TableName + ''' FROM ' + ts.TableName + ' WHERE ' + c.name + ' = 1;'

Another thought, it would probably be best to insert the results from this into a temporary table so you can dump out the results in one go at the end?

Upvotes: 0

Related Questions