查询以选择所有数据库，然后在结果上运行另一个查询。

Question

*******编辑*****

我是一位有用的助手，我能够进行文本翻译。以下是需要翻译的内容：

我有多个 SQL 服务器，其中大约有 200 到 300 个数据库，并想在我的服务器上节省空间。

---

我想通过计划任务运行此脚本，在我的 SQL 服务器上对所有数据库运行收缩程序。我已经有了查询语句，但我不知道如何将两者连接起来。要选择所有数据库，我使用以下代码：

select * from sys.sysdatabases
Where name <> 'master' and name <> 'tempdb' and name <> 'model' and name <> 'msdb'

我的心理医生建议我使用这个方法：

USE [single_database_name]
GO
DBCC SHRINKFILE ('single_database_name', 10)
GO

我该如何连接这两个查询，以便从第一个查询中所有数据库名称的列表中获取“single_database_name”？谢谢您的帮助。

- WillNZ

Answer 1

使用带动态SQL的游标。这将在每个数据库中单独缩小每个文件。

Declare @dataFiles Table (databaseName Varchar(256), datafile Varchar(256))
Declare @SQL Nvarchar(Max), @databaseName Varchar(256), @dbfile Varchar(256)

Insert  @dataFiles
select  sd.name, smf.name
from    sys.sysdatabases sd
join    sys.master_files smf
        On  sd.[dbid] = smf.database_id
Where   sd.name not in ('master','tempdb','model','msdb')

Declare cur Cursor For      
Select  databaseName,
        datafile
From    @dataFiles

Open    cur
Fetch   Next 
From    cur
Into    @databaseName,
        @dbfile

While   @@Fetch_Status = 0
Begin
        Set     @SQL = 'USE [' + @databasename + ']
                        DBCC SHRINKFILE (''' + @dbfile + ''', 10) WITH NO_INFOMSGS'

        Exec    sp_executeSQL @SQL

        Fetch   Next 
        From    cur
        Into    @databaseName,
                @dbfile
End
Close   cur
Deallocate cur

select  sd.name As DatabaseName, smf.name DBFileName, (size*8)/1024 SizeMB
from    sys.sysdatabases sd
join    sys.master_files smf
        On  sd.[dbid] = smf.database_id
Where   sd.name not in ('master','tempdb','model','msdb')

- Eric J. Price

---

你是否正在尝试在数据库名称上使用DBCC SHRINKFILE，而不是文件名？ - MarkD

我已更新代码逻辑以实现此功能。如果您键入“ShrinkDatabase”，双击它并按Shift + F1，则应该会弹出有关如何使用它的MS文档。但是，ShrinkDatabase的使用方式不同，具体取决于您想要做什么... 您到底想做什么？除非您真正需要空间，否则缩小文件/数据库是一个坏主意。如果您不断向其中添加内容，那么这样做可能会严重影响数据库性能，因为每次需要空间时都必须进行自动增长。 - Eric J. Price

@Love2Learn 谢谢你的回复。我想要收缩数据库的原因是我有多个 SQL 服务器，每个服务器中大约有 200-300 个数据库，我希望在服务器上节省空间。 - WillNZ

@Love2Learn 你能否编辑脚本，让我在每个结果中看到数据库名称？ - WillNZ

我通过使用另一个系统表来摆脱了第一个游标。我还抑制了输出，只在执行后查询了文件大小。这样行得通吗？ - Eric J. Price

Answer 2

select 
'USE '+ quotename([name]) + '
GO
DBCC SHRINKFILE (''' + [name] + ''', 10)
GO
'
from sys.databases
where name not in ('master', 'tempdb', 'model', 'msdb')

但请不要这样做：缩小是不好的。

- Ben Thul

---

你能解释一下为什么收缩数据库是不好的吗？我有多个 SQL 服务器，每个服务器中大约有 200-300 个数据库，我想在服务器上节省空间。 - WillNZ

Answer 3

通过使用未记录的存储过程sp_MSForEachDB和DBCC SHRINKDATABASE命令，您可以在一行代码中缩小SQL Server上的所有数据库。

EXEC sp_MSForEachDB
  'if ''?'' not in (select name from sys.databases
    where name <> ''master'' and name <> ''tempdb'' and name <> ''model'' and name <> ''msdb'')
      DBCC SHRINKDATABASE([?], 10)'

关于sp_MSForEachDB的更多信息

- Aleksandr Fedorenko

Answer 4

缩小数据库应该只在非常特定的情况下使用，比如删除了大量数据且数据库永远不会再次增长并回收空间。 缩小数据库将使数据碎片化，并可能导致性能问题。

- Drew Leffelman

Answer 5

如果要操作特定的数据库，您至少需要知道数据库的名称，如果是这种情况，请使用以下查询

if (exists(Select name from sys.databases where name like 'single_database_name'))
begin 
DBCC SHRINKFILE ('single_database_name', 10)
end

- hasham

---

他想对第一个查询返回的每个数据库名称执行 SHRINKFILE 操作。 - Francis P