Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

add NOT FOR REPLICATION in a txt file

Posted on 2016-08-01
6
Medium Priority
?
29 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 85

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 85

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
Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

 

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 85

Accepted Solution

by:
oBdA earned 2000 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

Lessons on Wi-Fi & Recommendations on KRACK

Simplicity and security can be a difficult  balance for any business to tackle. Join us on December 6th for a look at your company's biggest security gap. We will also address the most recent attack, "KRACK" and provide recommendations on how to secure your Wi-Fi network today!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are times when we need to generate a report on the inbox rules, where users have set up forwarding externally in their mailbox. In this article, I will be sharing a script I wrote to generate the report in CSV format.
In this post, I will showcase the steps for how to create groups in Office 365. Office 365 groups allow for ease of flexibility and collaboration between staff members.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Screencast - Getting to Know the Pipeline

886 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