Here I wanted to run the same query on multiple databases using a while loop.

1. I created a table with the name '@database' and added the names of the database that I want to traverse through in it
2. used while loop to traverse through databases names to run that particular query in each database

It didn't work.(it's running twice for the database which has been selected on the left dropdown of SQL management studio )

Tables in both databases are the same.

DECLARE @database TABLE (id INT,name varchar(50))

insert into @database values (1, 'Databse_one'), (2, 'Database_two')

DECLARE @cnt INT =2, @int INT=1,@dbname NVARCHAR(MAX)

while(@int <=@cnt)

 begin

select @dbname='use ' name from @database
where id=@int

EXEC sp_sqlexec @dbname
select top 10 db_name() DB_NAME, req_number, record Type,order_date from ORDER_DOCUMENT order by order_date desc

set @int = @int 1

end

CodePudding user response：

It wouldn't work like that even after you corrected the typos you already have. After use dbName, it would require a GO or be that in a different batch on the same connection. Instead you could write that as:

DECLARE @database TABLE(id INT, name VARCHAR(50));
INSERT INTO @database VALUES(1, 'Databse_one'), (2, 'Database_two');
DECLARE @cnt INT=2, @int INT=1, @dbname NVARCHAR(MAX);
WHILE(@int<=@cnt)BEGIN
    SELECT @dbname=name FROM @database WHERE id=@int;
    DECLARE @sql NVARCHAR(MAX);
    SET @sql=N'select top(10) ''' @dbname N''',req_number, record Type,order_date from ' QUOTENAME(@dbname) N'.dbo.ORDER_DOCUMENT order by order_date desc;';
    EXEC sys.sp_executesql @stmt=@sql;
    SET @int=@int 1;
END;

CodePudding user response：

USE doesn't work here, because the dynamic batch is a different scope. You would need to put the whole SELECT into the dynamic SQL.

But you don't need a WHILE at all. Just create one big UNION ALL query and run it.

DECLARE @database TABLE(name sysname);
INSERT INTO @database(name) VALUES(1, );

DECLARE @sql nvarchar(max);

SELECT @sql = 'SELECT *
FROM (
'   STRING_AGG(CAST('
    SELECT TOP (10) 
      '   QUOTENAME(d.name, '''')   ' DB_NAME,
      req_number,
      record Type,
      order_date
    FROM '     QUOTENAME(d.name)   '..ORDER_DOCUMENT
    ORDER BY
      order_date DESC
'  AS nvarchar(max)), '
')   '
);
'

FROM sys.databases d
WHERE d.name IN ('Databse_one', 'Database_two');

EXEC sp_executesql @sql;

Alternatively if you want separate SELECT statements then remove the UNION ALL

DECLARE @database TABLE(name sysname);
INSERT INTO @database(name) VALUES(1, );

DECLARE @sql nvarchar(max);

SELECT @sql = STRING_AGG(CAST('
SELECT TOP (10) 
  '   QUOTENAME(d.name, '''')   ' DB_NAME,
  req_number,
  record Type,
  order_date
FROM '     QUOTENAME(d.name)   '..ORDER_DOCUMENT
ORDER BY
  order_date DESC;
'  AS nvarchar(max)), '
')

FROM sys.databases d
WHERE d.name IN ('Databse_one', 'Database_two');

EXEC sp_executesql @sql;