How do I select an extra row for each row in the result set in SQL?

Question

I have the below query:

SELECT '['+name+']' FROM sys.schemas
Where name <> 'dbo' AND name <> 'guest' AND name <> 'INFORMATION_SCHEMA'
....

The result of this query is simple:

I need an extra row with text 'GO' after every record like this:

Is there a way to do this? I tried with a foreach statement, but I was unable to replicate the desired results.

Answer 1

Just add Go statement to the Dynamic code by hitting ENTER key and see the result pasting in SSMS. This will populate with GO statement in Next line.

You should also use QUOTENAME rather than concatenating the square brackets yourself (to work correctly for names containing ]) and the where clause can be simplified with NOT IN.

SELECT QUOTENAME(name) +'
GO
' 
FROM sys.schemas 
WHERE name NOT IN  ('dbo', 'guest', 'INFORMATION_SCHEMA')