Removing filestream from a database

I am trying to remove filestream from my database, so I have to do the following

I would like to know how to remove the following:

ALTER TABLE AMGR_Documents_Tbl ADD UId uniqueidentifier ROWGUIDCOL NOT NULL DEFAULT( NEWID() );
ALTER TABLE AMGR_Documents_Tbl ADD CONSTRAINT AMGR_Documents_Tbl_UId_Unique UNIQUE( UId );`

Then I need to Add a column with no filestream which probably is the following code:

ALTER TABLE AMGR_Letters_Tbl ADD TextCol1 varbinary( max );
Update [AMGR_Letters_Tbl]
SET TextCol1 = TextCol;
ALTER TABLE [AMGR_Letters_Tbl] DROP COLUMN TextCol;
/*Now i just rename the column*/

I also need to fix the FTS index (Which i do not understand) after which is the following:

```
IF 0 != INDEXPROPERTY( OBJECT_ID( 'AMGR_Letters_Tbl' ), 'Letters_Record_Id', 'IsFulltextKey' ) BEGIN
EXEC sp_fulltext_table AMGR_Letters_Tbl, 'drop'
END

/ I need to fix filestream on both tables: /
/ This is what the whole thing looks like (for 2 tables): /
IF @@VERSION NOT LIKE 'Microsoft SQL Server 2005%' AND ISNULL(SERVERPROPERTY ('FilestreamEffectiveLevel'),0) > 0 BEGIN
if not exists ( select name from syscolumns where id IN
( select Id from sysobjects where id = object_id(N'[dbo].[AMGR_Letters_Tbl]') and OBJECTPROPERTY(id, N'IsUserTable') = 1
) AND name = 'UId' )
BEGIN
-- add unique ROWGUIDCOL column
ALTER TABLE AMGR_Letters_Tbl ADD UId uniqueidentifier ROWGUIDCOL NOT NULL DEFAULT( NEWID() );
ALTER TABLE AMGR_Letters_Tbl ADD CONSTRAINT AMGR_Letters_Tbl_UId_Unique UNIQUE( UId );
EXEC( 'ALTER TABLE AMGR_Letters_Tbl ADD TextCol1 varbinary( max ) FILESTREAM NULL' );
-- will need to re-create FTS index after
IF 0 != INDEXPROPERTY( OBJECT_ID( 'AMGR_Letters_Tbl' ), 'Letters_Record_Id', 'IsFulltextKey' ) BEGIN
EXEC sp_fulltext_table AMGR_Letters_Tbl, 'drop'
END
EXEC( 'UPDATE AMGR_Letters_Tbl SET

To completely remove FILESTREAM features from a database, you need to perform the following steps.

• Delete all FILESTREAM columns from all tables

• Disassociate tables from FILESTREAM filegroups

• Remove all FILESTREAM Data Containers (filegroup files – you might have more than one of them)

• Remove all FILESTREAM filegroup (there may be more than one of them)

Walkthrough Click Here and follow his steps

Another Note: if you have Data in your FileStream file you will not able able to delete it. sp_filestream_force_garbage_collection unfortunately this only works >= SQL Server 2012

For that fix you can See the Whole Thing Here

If that doesn't work. try to make a backup for it.
That is how I cleared my FileStream file.

When it is Cleared then you can Delete it FileStream File and FILESTREAM filegroup.

If your database in heavily indexed, you should check you Functions and View, some might go missing. (Have a Back up of that)

StackExchange Database Administrators Q#111276, answer score: 2