I need a way to automatically move clustered indexes into one filegroup : ClusteredFilegroup, and all nonclustered indexes to a different filegroup NonClusteredFilegroup upon DDL creation . We have sql publish profile which creates similar script below every weekly deployment. How do I utilize powershell to conduct this?
I would like to have powershell add words
ON [ClusteredFilegroup] after every table creation
or ON [NonClusteredFilegroup] for every nonclustered index.
Powershell should be able to read original script (testscript.sql), and run text edits on it.
Original Script:
GO
CREATE TABLE [dbo].[Dim_Product] (
[DimProductId] INT IDENTITY (1, 1) NOT NULL,
[ProductName] VARCHAR(64) NOT NULL,
[ProductDescription] VARCHAR(64) NOT NULL,
[BeginDate] DATETIME NOT NULL,
[EndDate] DATETIME NOT NULL,
CONSTRAINT [PK_DimProductId] PRIMARY KEY CLUSTERED ([DimProductId] ASC)
);
GO
CREATE NONCLUSTERED INDEX [NCX_Product_ProductName]
ON [dbo].[Dim_Product]([ProductName] ASC);
GO
CREATE NONCLUSTERED INDEX [NCX_Product_BeginDate]
ON [dbo].[Dim_Product]([BeginDate] ASC);
GO
CREATE TABLE [dbo].[Dim_Customer] (
[DimCustomertId] INT IDENTITY (1, 1) NOT NULL,
[CustomerName] VARCHAR(64) NOT NULL,
[CustomerDescription] VARCHAR(64) NOT NULL,
[BeginDate] DATETIME NOT NULL,
[EndDate] DATETIME NOT NULL,
CONSTRAINT [PK_DimCustomerId] PRIMARY KEY CLUSTERED ([DimCustomerId] ASC)
);
GO
CREATE NONCLUSTERED INDEX [NCX_Customer_CustomerName]
ON [dbo].[Dim_Customer]([CustomerName] ASC);
GO
CREATE NONCLUSTERED INDEX [NCX_Customer_BeginDate]
ON [dbo].[Dim_Customer]([BeginDate] ASC);
Goal:
CREATE TABLE [dbo].[Dim_Product] (
[DimProductId] INT IDENTITY (1, 1) NOT NULL,
[ProductName] VARCHAR(64) NOT NULL,
[ProductDescription] VARCHAR(64) NOT NULL,
[BeginDate] DATETIME NOT NULL,
[EndDate] DATETIME NOT NULL,
CONSTRAINT [PK_DimProductId] PRIMARY KEY CLUSTERED ([DimProductId] ASC)
) ON [ClusteredFilegroup];
GO
CREATE NONCLUSTERED INDEX [NCX_Product_ProductName]
ON [dbo].[Dim_Product]([ProductName] ASC) ON [NonClusteredFilegroup];
I am trying to research these scripts:
Add text to every line in text file using PowerShell
Search a text file for specific word if found copy the entire line to new file in powershell
