Using regex to extract schema name and object name from an SQL

Question

I have several thousands of *.sql files all containing differently formatted T-SQL text and I want to match all the schema names and object names from those files.

I have seperated the following possible formats of schema names + object names

dbo.[MainPart.SubPartA.SubPartB.SubPartC]
[dbo].[MainPart.SubPartA.SubPartB.SubPartC]
specialschema.[MainPart.SubPartA.SubPartB.SubPartC]
specialschema.MainPart
[MainPart.SubPartA.SubPartB.SubPartC]

I already created a regex to match the first four cases

    (\[{0,1}(?<schema>\b\w*?\b){0,1}\]{0,1}\.){0,1}\[{0,1}(?<object>(\w|\.)+)\]{0,1}

It will create two groups "schema" and "object" for every match.

The problem is that the last case states that schema="MainPart" and object="SubPartA.SubPartB.SubPartC"

At this moment I am considering to break the regex into several parts to make it simpler (and more readeable), because I already have the match, only the groups aren't correct.

Or is there another regex technique to get the correct groups for all five cases (and still maintain or even improve the readability)?

An example of a SQL file:

/******************************************************************
 Comment block
******************************************************************/ 

CREATE PROCEDURE [MainPart.SubPartA.SubPartB.SubPartC]
    @Param  INT = NULL
AS
BEGIN

    SET NOCOUNT ON

    SELECT * FROM dbo.[Table] WHERE fldParam = @Param

    SET NOCOUNT OFF

END
GO

Can you post an example of one of the SQL files?

David B
– David B

2012-07-05 14:25:45 +00:00
Commented Jul 5, 2012 at 14:25 — David B
– David B, Commented Jul 5, 2012 at 14:25

Aaron Bertrand · Accepted Answer · 2012-07-05 14:25:47Z

1

Why not just:

SELECT [schema] = OBJECT_SCHEMA_NAME(OBJECT_ID('dbo.[whatever.bob]')),
       [object] = OBJECT_NAME(OBJECT_ID('dbo.[whatever.bob]'));

Or if the objects don't exist yet:

SELECT [schema] = PARSENAME('dbo.[whatever.bob]', 2),
       [object] = PARSENAME('dbo.[whatever.bob]', 1);

You'll need to use coalesce if the schema part is missing:

SELECT [schema] = COALESCE(PARSENAME('[whatever.bob]', 2), 'dbo'),
       [object] = PARSENAME('[whatever.bob]', 1);

Do you have cases where this breaks? Seems to work fine on the 5 values you showed in the question.

answered Jul 5, 2012 at 14:25

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sjoerd van Loon Over a year ago

Because the transact SQL is not yet stored in the database server, but it is still stored in the script files. Creating all the objects in SQL before quering their schema and object names is a whole different approach to my problem.

Collectives™ on Stack Overflow

Using regex to extract schema name and object name from an SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related