using System;
using System.Text.RegularExpressions;
public class Example
{
public static void Main()
{
string pattern = @"(?<text>N?'((?:''|[^'])*)'|N?""((?:""|[^""])*)"")";
string input = @"USE [master]
GO
IF OBJECT_ID('dbo.sp_foreach_db') IS NOT NULL
DROP PROCEDURE [dbo].[sp_foreach_db]
GO
CREATE PROCEDURE [dbo].[sp_foreach_db]
(
@tsql NVARCHAR(MAX) = NULL
,@replacedatabasenamepattern NVARCHAR(20) = N'?'
,@break_on_error BIT = 1
,@db_name_like NVARCHAR(128) = NULL
,@db_name_in NVARCHAR(4000) = NULL
,@db_name_not_in NVARCHAR(4000) = NULL
,@db_use_system_db BIT = 0
,@db_use_temp_db BIT = 0
,@push_info_msg BIT = 1
)
WITH EXECUTE AS CALLER
AS
/*
Version: 1.0.1.0
Developer: Paw Jershauge
Created: 18-03-2016 13:01:00
*/
BEGIN
IF @tsql IS NULL
RETURN 0;
SET NOCOUNT ON;
DECLARE @dbtsql NVARCHAR(MAX)
,@firecode NVARCHAR(MAX)
,@db_name NVARCHAR(128)
,@dbnames NVARCHAR(MAX) = N''
,@has_replacement BIT = 0
,@msg_str NVARCHAR(2044)
SET @dbtsql = N'SELECT @dbnames += [name] + CHAR(10) FROM [master].[sys].[databases] WHERE [state] = 0'
SET @dbtsql += IIF(ISNULL(@db_use_temp_db, 0) = 1, N'', N' AND [database_id] <> 2')
SET @dbtsql += IIF(ISNULL(@db_use_system_db, 0) = 1, N'', N' AND [database_id] > 4')
SET @dbtsql += IIF(NULLIF(@db_name_like, N'') IS NULL, N'', N' AND [name] LIKE ''' + @db_name_like + N'''')
SET @dbtsql += IIF(NULLIF(@db_name_in, N'') IS NULL, N'', N' AND [name] IN (' + @db_name_in + N')')
SET @dbtsql += IIF(NULLIF(@db_name_not_in, N'') IS NULL, N'', N' AND [name] NOT IN (' + @db_name_not_in + N')')
EXEC sp_executesql @dbtsql, N'@dbnames NVARCHAR(MAX) OUTPUT', @dbnames = @dbnames OUTPUT;
SELECT @has_replacement = CAST(CHARINDEX(ISNULL(@replacedatabasenamepattern, N'?'), @tsql) AS BIT)
DECLARE cur_db CURSOR LOCAL FORWARD_ONLY FAST_FORWARD READ_ONLY FOR SELECT [value] FROM STRING_SPLIT(@dbnames, CHAR(10)) WHERE [value] <> N'' ORDER BY [value]
OPEN cur_db
FETCH NEXT FROM cur_db INTO @db_name
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
SELECT @firecode = N'EXEC (N''' + IIF(@has_replacement = 0,N'Use [' + @db_name + N']; ', N'') + REPLACE(REPLACE(@tsql, ISNULL(@replacedatabasenamepattern, N'?'), @db_name), N'''', N'''''') + N''')'
IF @push_info_msg = 1
BEGIN
SET @msg_str = CONVERT(NVARCHAR(40),GETDATE(), 121) + N':Executing on ' + @db_name
RAISERROR (@msg_str, 0, 1) WITH NOWAIT
END
EXEC sp_executesql @firecode
IF @push_info_msg = 1
BEGIN
SET @msg_str = CONVERT(NVARCHAR(40),GETDATE(), 121) + N':' + @db_name + N' Done!'
RAISERROR (@msg_str, 0, 1) WITH NOWAIT
END
END TRY
BEGIN CATCH
SET @msg_str = 'Database ' + @db_name + ' failed with the following message:'
RAISERROR (@msg_str, 0, 1) WITH NOWAIT
SET @msg_str = ERROR_MESSAGE()
RAISERROR (@msg_str, 0, 1) WITH NOWAIT --Workaround of the PRINT Command that is stacked and released in reverse order, on remote servers
IF @break_on_error = 1
THROW
END CATCH
FETCH NEXT FROM cur_db INTO @db_name
END
CLOSE cur_db
DEALLOCATE cur_db
END
GO
EXEC sys.sp_MS_marksystemobject sp_foreach_db
GO";
RegexOptions options = RegexOptions.Multiline | RegexOptions.Singleline;
foreach (Match m in Regex.Matches(input, pattern, options))
{
Console.WriteLine("'{0}' found at index {1}.", m.Value, m.Index);
}
}
}
Please keep in mind that these code samples are automatically generated and are not guaranteed to work. If you find any syntax errors, feel free to submit a bug report. For a full regex reference for C#, please visit: https://msdn.microsoft.com/en-us/library/system.text.regularexpressions.regex(v=vs.110).aspx