Regex replace in SSMS

In SSMS I would like to include square brackets for schema. If dbo is schema name is dbo otherwise the schema name is xxxx.yyyy

dbo.spSomeName should be replaced as [dbo].[spSomeName]
dbo.tSomeName   should be replaced  as  [dbo].[tSomeName]
MyDB.AnySchema.spSomeName should be replaced  as  [MyDB.AnySchema].[spSomeName]
MyDB.AnySchema.tSomeName should be replaced  as  [MyDB.AnySchema].[tSomeName]

How to achieve this using Regex replace? Please suggest
LVL 16
Easwaran ParamasivamAsked:
Who is Participating?
 
Terry WoodsConnect With a Mentor IT GuruCommented:
Regular expressions in SSMS are really horrible and non-standard, even by Microsoft's track record!

Documentation is available here: http://msdn.microsoft.com/en-us/library/ms174214%28v=sql.90%29.aspx

My forte is Regular Expressions in general, and I'm not a Microsoft programmer, so I can't test this for you, however I think this might work for you:

Match:
{([a-zA-Z0-9_]+\.)*[a-zA-Z0-9_]+}\.{[a-zA-Z0-9_]+}
Replace with:
[\1].[\2]

It will also match cases like:
otherstuff.morestuff.MyDB.AnySchema.SomeName

If that's a problem, we can probably alter the pattern to fix that.

Let me know how it goes.
0
 
Terry WoodsIT GuruCommented:
This might be slightly better (ie I think it fixes the problem I previously pointed out), and uses the same replacement:

Match:
{([a-zA-Z0-9_]+\.|)[a-zA-Z0-9_]+}\.{[a-zA-Z0-9_]+}
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.

All Courses

From novice to tech pro — start learning today.