如何找出哪些表在 SQL Server 的文件中包含数据? [英] How do I find out what tables have data in a file in SQL Server?

问题描述

我想在 SQL Server (2005) 中删除一个现在应该是冗余的文件，但是当我尝试删除它时，我被告知该文件不是空的.有谁知道有什么方法可以找出此文件中仍然存在的数据，以便我可以进行任何需要的更改以允许我删除它?

I want to drop a now supposedly redundant file in SQL Server (2005), but when I try to drop it I am told that the file is not empty. Does anyone know of a way to find out what data is still in this file so I can make whatever changes I need to allow me to drop it?

推荐答案

假设你移动了桌子等，你可能需要运行:

Assuming you're moved the table etc, you'll probably need to run:

DBCC SHRINKFILE (MyLogicalFile, EMPTYFILE) --EMPTYFILE is the important bit!!

参见 DBCC SHRINKFILE

要检查(这是我使用的使用脚本的剪切粘贴):

To check (this is a cut'n'paste of a usage script I use):

SELECT
    ds.[name] AS LogicalFileName,
    OBJECT_NAME(p.object_id) AS Thing,
    SUM(au.total_pages) / 128.0 AS UsedMB,
    df.size / 128 AS FileSizeMB,
    100.0 * SUM(au.total_pages) / df.size AS PercentUsed
FROM
    sys.database_files df
    JOIN
    sys.data_spaces ds ON df.data_space_id = ds.data_space_id 
    JOIN
    sys.allocation_units au ON ds.data_space_id = au.data_space_id 
    JOIN 
    sys.partitions p ON au.container_id = p.hobt_id
WHERE
    OBJECTPROPERTYEX(p.object_id, 'IsMSShipped') = 0
GROUP BY
    ds.[name], OBJECT_NAME(p.object_id), df.size
ORDER BY
    ds.[name]

这篇关于如何找出哪些表在 SQL Server 的文件中包含数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！