Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 141
  • Last Modified:

remove string in script sql

Hello,

I search to remove two string like this in a script file
IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[MSmerge_del*]'))
DROP TRIGGER [dbo].[MSmerge*]

Open in new window

I try the following without good result
$DIRECTORY="c:\temp"
$script=$DIRECTORY + "\" + ".Drop.Triggers.sql"
$Sql = [IO.File]::ReadAllText($script)
$Sql -replace '(?s)drop trigger \[MSmerge_.*?\r\nGO' -replace '(?s) \[MSmerge_.*?\r' , '' | Out-File $script

Open in new window


Why?

Thanks
0
bibi92
Asked:
bibi92
  • 5
  • 3
1 Solution
 
Raheman M. AbdulSenior Infrastructure Support Analyst & Systems DeveloperCommented:
$sql -replace  'DROP TRIGGER \[dbo\].\[MSmerge\*\]', ''


send your sample .sql file
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Guess this is a continuation from https://www.experts-exchange.com/questions/28960354/script-smo-triggers.html ?
You want to remove the complete line, I assume. What about the create statement certainly following the drop?
0
 
bibi92Author Commented:
Yes, I always try without result
$DIRECTORY="c:\temp"
$script=$DIRECTORY + "\" + ".Drop.Triggers.sql"
$Sql = [IO.File]::ReadAllText($script)
$Sql -replace '(?s)drop trigger \[MSmerge_.*?\r\nGO' -replace '(?s) \[MSmerge_.*?\r' , '' | Out-File $script
Thanks
0
Making Bulk Changes to Active Directory

Watch this video to see how easy it is to make mass changes to Active Directory from an external text file without using complicated scripts.

 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
I didn't get your last response. You didn't answer to my questions at all. I think you want to remove all lines written out below:
IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[MSmerge_del*]'))
DROP TRIGGER [dbo].[MSmerge*]
GO
CREATE TRIGGER [dbo].[MSmerge*] /* and further text here */
/* ... */
GO

Open in new window

where the "*" means text parts to ignore when searching. Correct?
0
 
bibi92Author Commented:
yes thanks regards
0
 
bibi92Author Commented:
Following, T-SQL code :
IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[MSmerge_del_2DF5CAAAAF3D4CB0B6497DE4645ABC11]'))
DROP TRIGGER [dbo].[MSmerge_del_2DF5CAAAAF3D4CB0B6497DE4645ABC11]
GO
IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[MSmerge_ins_2DF5CAAAAF3D4CB0B6497DE4645ABC11]'))
DROP TRIGGER [dbo].[MSmerge_ins_2DF5CAAAAF3D4CB0B6497DE4645ABC11]
GO
IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[MSmerge_upd_2DF5CAAAAF3D4CB0B6497DE4645ABC11]'))
DROP TRIGGER [dbo].[MSmerge_upd_2DF5CAAAAF3D4CB0B6497DE4645ABC11]

Open in new window


Thanks
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
This works for me:
$DIRECTORY="c:\temp"
$script=$DIRECTORY + "\" + ".Drop.Triggers.sql"
(Get-Content $script) -replace '.+MSMerge_.*', '' -join "`r`n" -replace '\r\n\r\n\r\nGO', '' | Out-File $script

Open in new window

but you might have to add or remove a \r\n combination from the pattern.
0
 
bibi92Author Commented:
Thanks regards
0
 
bibi92Author Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now