强制输出查询一次显示DB_NAME（）数据一次

发布于 2025-01-31 22:36:39 字数 990 浏览 3 评论 0原文

我有一个查询，可以从表中选择所有索引并生成输出文本以单独删除它们。

我想将db_name（） info放在命令的开头，但此时每个索引下显示数据。

我以前尝试过选择，但没有成功。

有人可以帮我吗？查询和结果如下：

- 查询

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';

- 我得到的结果（示例）

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

- 我想要的结果

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

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

凑诗 2025-02-07 22:36:40

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

但是，为什么要打扰呢？如 @shreepat18所说，您几乎可以在SQL Server Management Studio中脚本脚本！

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!

回复收藏 0 原文

少跟Wǒ拽 2025-02-07 22:36:39

您可以只使用use命令初始化一个变量，然后使用字符串串联添加drop index命令。这在SQL Server 2017（String_agg（））中更容易，但是您没有告诉我们您使用的版本，所以...

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;

示例

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;