[SOLVED] How to append filegroup dynamically while creating an index in SQL Server?

How to append filegroup dynamically while creating an index in SQL Server?

I have to move some tables from primary file group to the default prime group. I have 3 different environments and in each environment name of the default file group is different. Thus, I can fetch the default file group name as:

select name from sys.filegroups where is_default=1;

Now while recreating my indexes I want to use the output of the above query to specify the file group where I want to create index(as default file group has different names in different environments, thus, I want only a single query to be promoted). I tried as:

CREATE UNIQUE CLUSTERED INDEX PK_INDEX
ON slam.MY_TABLE(COL_1)
WITH (DROP_EXISTING=ON) ON [select name from sys.filegroups where is_default=1];

But I am facing below error:

Msg 1921, Level 16, State 1, Line 19 Invalid filegroup 'select name from sys.filegroups where is_default=1' specified.

Any help will be much appreciated.

Solution

You need to use dynamic sql for that. Select the name of your default file group, construct your query and execute it like this:

declare @fg_name nvarchar(250), @sql nvarchar(max);
select @fg_name = name from sys.filegroups where is_default=1;
set @sql = N'CREATE UNIQUE CLUSTERED INDEX PK_INDEX
    ON slam.MY_TABLE(COL_1)
    WITH (DROP_EXISTING=ON) ON [' + @fg_name + N'];';
exec sp_executesql @sql;