0

I ran a sql script that uses a cursor to execute sp_table_privileges on each table in a database.

If you run the script and save the output to a file you get this after each iteration:

data,data,data,data,data,data,data

(x row(s) affected)

next_table_name
TABLE_QUALIFIER,TABLE_OWNER,TABLE_NAME,GRANTOR,GRANTEE,PRIVILEGE,IS_GRANTABLE

I was able to remove the (x row(s) affected) in Notepad++ by using the regular expression replace with (\(.*\)) but it replaced it with a blank line.

So now I have 3 blank lines, the next tables name and the columns.

I know you can remove blank lines in Notepad++.

But, can I use a regular expression to find 3 blank lines, remove them and two lines below them?

If not is there a better way to do this?

Community
  • 1
  • 1
Neil Hoff
  • 2,025
  • 4
  • 29
  • 53

2 Answers2

2

If I understood you correcly. Find

(\r\n){4}.*\r\n.*

replace with (Replace All)

leave empty

Box ".matches newline" leave unchecked

And if you want to delete everything beneath the three empty lines find

(\r\n){3}.*

replace with (Replace All)

leave empty

In this case Box ".matches newline" has to be checked

Placido
  • 1,386
  • 1
  • 12
  • 24
1

Just to clarify : So you get something like in my screenshot once you applied your regular expression ? correct ? And then you delete the 3 blank lines like I do here ?

enter image description here

And you get : 

> data,data,data,data,data,data,data 
> next_table_name
> TABLE_QUALIFIER,TABLE_OWNER,TABLE_NAME,GRANTOR,GRANTEE,PRIVILEGE,IS_GRANTABLE

And now you want to remove "next.." and "TABLE_QUALIFIER"...etc automatically so you just keep the data,data,data etc ?

[Editing with Placido's solution for the Author of the question]

Have a look, that's what you wanted, correct ?

enter image description here

And then :

enter image description here

Melvin
  • 226
  • 1
  • 6