List objects in SQL file

Question

We are trying to migrate a database from SQL 2014 to Azure SQL.

In SSMS, you can right click on the database and then on a task to "Deploy Database to Windows Azure SQL Database..".

This task fails because the database uses a SECONDARY filegroup. I've gone through the process to move all indexes, heaps, etc. out of the SECONDARY file group into the PRIMARY filegroup.

However, I still can't delete the file in the SECONDARY filegroup because there is 43MB of data in it. Every script I've found to list all the objects in a filegroup say there is nothing remaining in the file.

How can I determine what's still in this filegroup so I can delete the file and remove the SECONDARY filegroup?

Answer 1

I found this script which lists LOB objects in each filegroup. Based on the results I was able to find the table still using the SECONDARY filegroup.

SELECT
    au.*,
    ds.name AS [data_space_name],
    ds.type AS [data_space_type],
    p.rows,
    o.name AS [object_name]
FROM sys.allocation_units au
    INNER JOIN sys.data_spaces ds
        ON au.data_space_id = ds.data_space_id
    INNER JOIN sys.partitions p
        ON au.container_id = p.partition_id
    INNER JOIN sys.objects o
        ON p.object_id = o.object_id
WHERE au.type_desc = 'LOB_DATA'

Answer 2

Can you post the result of the following sql statement?

SELECT o.[name], o.[type], o.[type_desc], i.[name], i.[index_id], f.[name] 
FROM sys.indexes i
INNER JOIN sys.filegroups f ON i.data_space_id = f.data_space_id
INNER JOIN sys.all_objects o ON i.[object_id] = o.[object_id] 
WHERE i.data_space_id = f.data_space_id
AND i.data_space_id > 1
GO