I have a table in SQL Server where one column is placed in Filestream filegroup. Now I need to delete this column and all files on disk.
Just recall the process to implement Filestream in SQL Server database:
- Add Filestream filegroup;
- Add file to a newly created Filestream filegroup;
- Create table where column is marked as FILESTREAM and you pointed FILESTREAM_ON to a Filestream group.
To drop Filestream you need to implement these steps in reverse order, but check Filestream columns, files and filegroups before.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
-- Filestream columns SELECT SCHEMA_NAME(t.schema_id) AS [schema], t.[name] AS [table], c.[name] AS [column], TYPE_NAME(c.user_type_id) AS [column_type] FROM sys.columns c JOIN sys.tables t ON c.object_id = t.object_id WHERE t.filestream_data_space_id IS NOT NULL AND c.is_filestream = 1 ORDER BY 1, 2, 3; -- Filestream files and filegroups SELECT f.[name] AS [file_name], f.physical_name AS [file_path], fg.[name] AS [filegroup_name] FROM sys.database_files f JOIN sys.filegroups fg ON f.data_space_id = fg.data_space_id WHERE f.[type] = 2 ORDER BY 1; |
Then do the following:
1 2 3 4 |
ALTER TABLE [FilestreamTable] DROP COLUMN [FilestreamColumn]; ALTER TABLE [FilestreamTable] SET (FILESTREAM_ON="NULL") ALTER DATABASE [FilestreamDatabase] REMOVE FILE [FilestreamFile]; ALTER DATABASE [FilestreamDatabase] REMOVE FILEGROUP [FilestreamFilegroup]; |