Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Migrations Helper Filegroups MSSQL
public static class MigrationsHelper
{
public static string CreateFilegroup(string filegroupName, byte numberOfFilegroupFiles, string sizeOfEachFile, string sizeOfFileGrown) {
var sqlFromFileGroup = "";
var sqlFromFilesCreated = new StringBuilder(4000);
for( byte filesCreated = 0; filesCreated < numberOfFilegroupFiles; filesCreated++ )
{
sqlFromFilesCreated.Append($@"
ALTER DATABASE CURRENT
ADD FILE (
NAME = [' + DB_NAME() + '_{filegroupName}_{filesCreated}],
FILENAME = N''' + REPLACE(@DatabasePath, N'.mdf', N'_{filegroupName}_{filesCreated}.ndf') + ''',
SIZE = {sizeOfEachFile},
MAXSIZE = UNLIMITED,
FILEGROWTH = {sizeOfFileGrown}
)
TO FILEGROUP [{filegroupName}];
");
}
sqlFromFileGroup = $@"
IF NOT EXISTS (SELECT * FROM sys.filegroups where name = '{filegroupName}') BEGIN
ALTER DATABASE CURRENT
ADD FILEGROUP [{filegroupName}]
END
IF EXISTS (SELECT * FROM sys.filegroups where name = '{filegroupName}') AND NOT EXISTS (SELECT * FROM sys.master_files where name like DB_NAME() + '_{filegroupName}%') BEGIN
DECLARE @DatabasePath nvarchar(max)
DECLARE @SQL nvarchar(max)
SELECT TOP 1 @DatabasePath = physical_name
FROM sys.master_files
WHERE database_id = DB_ID() AND file_id = 1 AND type_desc = N'ROWS'
SET @SQL = N'{sqlFromFilesCreated.ToString()}'
EXECUTE sp_executesql @SQL
END
";
return sqlFromFileGroup;
}
public static string SetFilegroupToTable(string filegroupName, string tableName, string primaryKeyName, string keyColumns = "Id")
{
var sqlFromSettedFilegroup = "";
sqlFromSettedFilegroup = $@"
IF EXISTS (SELECT * FROM sys.filegroups where name = '{filegroupName}') AND EXISTS (SELECT * FROM sys.master_files where name like DB_NAME() + '_{filegroupName}%') BEGIN
CREATE UNIQUE CLUSTERED INDEX [{primaryKeyName}]
ON [dbo].[{tableName}]({keyColumns})
WITH (DROP_EXISTING = ON) ON [{filegroupName}]
END
";
return sqlFromSettedFilegroup;
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.