Solved

add NOT FOR REPLICATION in a txt file

Posted on 2016-08-01
6
13 Views
Last Modified: 2016-08-02
Hello,

I search how I can modify a txt file containing :

ALTER TABLE [dbo].[T_tbl]  WITH CHECK ADD  CONSTRAINT [FK_T_tbl_t_test] FOREIGN KEY([proc])
REFERENCES [T_test] ([Id])
GO

Open in new window


by

ALTER TABLE [dbo].[T_tbl]  WITH CHECK ADD  CONSTRAINT [FK_T_tbl_t_test] FOREIGN KEY([proc])
REFERENCES [T_test] ([Id])
NOT FOR REPLICATION
GO

Open in new window


Thanks

Regards
0
Comment
Question by:bibi92
  • 3
  • 3
6 Comments
 
LVL 83

Expert Comment

by:oBdA
ID: 41737582
This should do the trick, including keeping the EOL characters currently used:
$Sql = [IO.File]::ReadAllText("C:\Temp\test.sql")
$Sql -replace "(?s)($([regex]::Escape('REFERENCES [T_test] ([Id])')))(\r?\n)(GO)", '$1$2NOT FOR REPLICATION$2$3'

Open in new window

0
 

Author Comment

by:bibi92
ID: 41737621
Ok thanks sorry my question is not clear. I search to add not for replication for any alter table.
0
 
LVL 83

Expert Comment

by:oBdA
ID: 41737636
Even shorter:
$Sql = [IO.File]::ReadAllText("C:\Temp\test.sql")
$Sql -replace '(?s)(ALTER TABLE .*?)(\r?\n)(GO)', '$1$2NOT FOR REPLICATION$2$3'

Open in new window

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:bibi92
ID: 41738757
Thanks but syntax error is generated for alter table check constraint.
I add option NOT FOR REPLICATION only for ALTER TABLE ... FOREIGN KEY ...
I try :
-replace '(?s)(ALTER TABLE .*? FOREIGN KEY .*?)(\r?\n)(GO)', '$1$2NOT FOR REPLICATION$2$3'
But the replace isn't done.
0
 
LVL 83

Accepted Solution

by:
oBdA earned 500 total points
ID: 41738843
That didn't work because you have a space between "KEY" and ".*?", while the script is "KEY([proc])", so there's no match.
$Sql = [IO.File]::ReadAllText("C:\Temp\test.sql")
$Sql -replace '(?s)(ALTER TABLE .*? FOREIGN KEY.*?)(\r?\n)(GO)', '$1$2NOT FOR REPLICATION$2$3'

Open in new window

0
 

Author Closing Comment

by:bibi92
ID: 41738861
thanks regards
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I thought I'd write this up for anyone who has a request to create an anonymous whistle-blower-type submission form created using SharePoint 2010 (this would probably work the same for 2013). It's not 100% fool-proof but it's as close as you can get…
A procedure for exporting installed hotfix details of remote computers using powershell
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now