Force the output query to shows the DB_NAME() data once-CodePudding

I've got a query that selects all indexes from a table and generate an output text to drop them individually.

I want to put the DB_NAME() info in the beginning of command for once, but at this moment the data is showed at every index drop.

I've tried to put a select before, but without success.

Could someone help me? Query and results are below:

- QUERY

SELECT
  'USE '   QUOTENAME(db_name())   ';'   CHAR(13)  
  'GO '   CHAR(13)  
  'DROP INDEX '   QUOTENAME(i.name)   ' ON '   QUOTENAME(s.name)   '.'   QUOTENAME(t.Name)   ';'   CHAR(13)  
  'GO'
FROM
  sys.indexes i
  JOIN sys.tables t ON t.object_id = i.object_id
  JOIN sys.schemas s ON s.schema_id = t.schema_id
WHERE
  t.Name = 'Queue';

- Results that I get (example)

USE [master];
GO 
DROP INDEX [PK_Queue] ON [dbo].[Queue];
GO
USE [master];
GO 
DROP INDEX [I_001] ON [dbo].[Queue];
GO
USE [master];
GO 
DROP INDEX [I_002] ON [dbo].[Queue];
GO

- Results that I want

USE [master];
GO 
DROP INDEX [PK_Queue] ON [dbo].[Queue];
GO
DROP INDEX [I_001] ON [dbo].[Queue];
GO
DROP INDEX [I_002] ON [dbo].[Queue];
GO

CodePudding user response：

You can just initialize a variable with the USE command, and then append the DROP INDEX commands using string concatenation. This is easier in SQL Server 2017 (STRING_AGG()), but you didn't tell us what version you use, so...

DECLARE @sql nvarchar(max) = N'USE '   QUOTENAME(db_name())   ';';

SELECT @sql  = char(13)   N'GO'   char(13)   N'DROP INDEX ' 
    QUOTENAME(i.name)   ' ON '   QUOTENAME(s.name) 
    '.'   QUOTENAME(t.name)   ';'   CHAR(13)
FROM
  sys.indexes i
  JOIN sys.tables t ON t.object_id = i.object_id
  JOIN sys.schemas s ON s.schema_id = t.schema_id
WHERE
  t.Name = 'Queue' AND i.name IS NOT NULL;
  
 PRINT @sql;

Example db<>fiddle

CodePudding user response：

SELECT Txt
from
(
    SELECT
      1 as Seq,
      'USE '   QUOTENAME(db_name())   ';'   CHAR(13)  
      'GO '   CHAR(13) as Txt
    union all
    SELECT
       2 as Seq,
      'DROP INDEX '   QUOTENAME(i.name)   ' ON '   QUOTENAME(s.name)   '.'   QUOTENAME(t.Name)   ';'   CHAR(13)  
      'GO'
    FROM
      sys.indexes i
      JOIN sys.tables t ON t.object_id = i.object_id
      JOIN sys.schemas s ON s.schema_id = t.schema_id
    WHERE
      t.Name = 'Queue'
) S1
order by Seq

But, why bother? You can script almost everything in SQL Server Management Studio as @shreepat18 said!