CODEWITHSUNDEEP
sqlsql-server
Sean Long
Sean Long

Reputation: 2301

Iterating through a series of databases looking for a specific value

I'm building a SQL script that can be run on any MS SQL Server, where the following will happen:

A temporary table will be built with an ID #, the dbname, and a processed? column.

The system will then iterate through each row in the temporary table, using the dbname as part of the SQL statement.

This is what I have currently:

DECLARE @ID INT
CREATE TABLE #DBNAME (ID varchar(3), dbname varchar(100), processed varchar(1))
BEGIN
INSERT INTO #DBNAME(ID, dbname)
SELECT database_id, name FROM sys.databases
END
BEGIN
UPDATE #DBNAME SET processed = '0'
WHERE dbname IS NOT NULL
END
BEGIN


END
select * from #DBNAME
WHILE (SELECT COUNT(*) From #DBNAME Where processed = 0) > 0
BEGIN
    SELECT TOP 1 @ID = ID from #DBName Where processed = 0
    select * from [#DBNAME.dbname].installinfo where installed='Success'

Currently, the #DBName.dbname.installinfo isn't working because it's not a valid object, and I'm not sure how to do this. The script is not going to be done there, as if the select statement returns more than zero rows, it will need to collect additional information but I think I can put that in just fine if I can get the [#DBNAME.dbname] part correct so I can start working in the iterated database.

Any ideas?

Upvotes: 0

Views: 88

Answers (2)

Hart CO
Hart CO

Reputation: 34774

You'll need to use dynamic sql to use a value from your table as the table name in another query, ie:

DECLARE @sql VARCHAR(8000),@tblname VARCHAR(255)
SET @tblname = SELECT stuff FROM Table
SET @sql = 'select * from '+@tblname+' where installed=''Success''
           '

EXEC (@sql)

Upvotes: 1

Chris H
Chris H

Reputation: 545

I would suggest writing a dynamic sql statement:

DECLARE @dbname AS NVARCHAR(MAX);
SELECT @dname = name FROM #DBNAME Where ID = @ID;
DECLARE @sql as NVARCHAR(MAX);
SET @sql = 'SELECT * FROM ' + @dbname + '.installinfo where installed = ''Success''';
EXEC sp_executesql @sql;

Upvotes: 1

Related Questions