I want to remove nonclustered indexes from a script using PowerShell possibly Regex. Background: We have lot of data in database, and want to prevent 1 hr of index creation time, from creating or altering the nonclustered indexes. How to conduct this with PowerShell? PowerShell will take input script and edit accordingly.
Publish Profile Script:
Create table dbo.Test(TestId int, ColumnA int, ColumnB int)
GO
CREATE NONCLUSTERED INDEX [ncx_Test_ColumnA]
ON [dbo].[Test]([ColumnA] ASC);
GO
PRINT N'Creating [ncx_Test_ColumnA]...';
GO
CREATE NONCLUSTERED INDEX [ncx_Test_ColumnB]
ON [dbo].[Test]([ColumnB] ASC);
GO
PRINT N'Creating [ncx_Test_ColumnB]...';
GO
create procedure dbo.TestSelect1
as select 1
In the above script, I want to remove the 'Create Nonclustered Index Lines, (and added bonus, not required) would be remove the corresponding Print statements, keep everything else, tables and stored procedures.
Remove these lines only, keep everything else:
CREATE NONCLUSTERED INDEX [ncx_Test_ColumnA]
ON [dbo].[Test]([ColumnA] ASC);
PRINT N'Creating [ncx_Test_ColumnA]...';
CREATE NONCLUSTERED INDEX [ncx_Test_ColumnB]
ON [dbo].[Test]([ColumnB] ASC);
PRINT N'Creating [ncx_Test_ColumnB]...';
Good resource for powershell: trying to figure out
Automatically Create Indexes in Different Filegroup, edit Publish Profile Script
