How to: Use a variable in ALTER/CREATE PARTITION SCHEME command for filegroup?

• mzarlenga

  SSC Enthusiast

  Points: 170

  More actions

  May 9, 2010 at 2:46 pm

  #221310

  Is it possible to use a variable to specify the filegroup in the ALTER/

  CREATE PARTITION SCHEME command?

  I want the partition scheme to use the default filegroup for ALTER and

  CREATE PARTITION SCHEME. At the time the script is created, I don't

  know the default filegroup in the database.

  My code:

  declare @fileGroupName VARCHAR(50) = (select top 1 name from

  sys.filegroups where is_default = 1)

  ALTER PARTITION SCHEME MyScheme NEXT USED @fileGroupName

  Is failing:

  Incorrect syntax near '@fileGroupName'.

  Q: Is it possible to use a variable for the filegroup in the ALTER/

  CREATE commands? Is so, what is the correct syntax?

  Q: If using a variable is not possible, is there another way to

  specify the default filegroup?

• mzarlenga

  SSC Enthusiast

  Points: 170

  More actions

  May 10, 2010 at 7:40 pm

  #1165294

  I'll answer my own question:

  Dynamic SQL.

  Instead of:

  declare @fileGroupName VARCHAR(50) = (select top 1 name from sys.filegroups where is_default = 1)

  ALTER PARTITION SCHEME MyScheme NEXT USED @fileGroupName

  Use:

  declare @fileGroupName VARCHAR(50) = (select top 1 name from sys.filegroups where is_default = 1)

  declare @sql VARCHAR(256) = 'ALTER PARTITION SCHEME MyScheme NEXT USED ' + @fileGroupName

  EXEC (@sql)

• vivekkumar7

  SSC Rookie

  Points: 29

  More actions

  July 30, 2015 at 9:00 am

  #1816170

  Thanks.

  This really help me.