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

May 25, 2022

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

>Solution :

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;