What can be the most efficient way to parse the object name from the script?

Question

I have some inputs as

CREATE PROCEDURE dbo._ws_CallLogs_DeleteAll
(
    @UserId uniqueidentifier 
)
AS

OR

/*========== Script Analyzed by Sql Eye  on 11/30/2012 2:55:12 PM =======

  *====================== Total warnings : 0  =================================== */ 






SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
SET ANSI_PADDING ON
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_CachedPlan_cached_plan_Job_id]') 
                AND parent_object_id = OBJECT_ID(N'[dbo].[CachedPlan]'))
ALTER TABLE [dbo].[CachedPlan] DROP CONSTRAINT [FK_CachedPlan_cached_plan_Job_id]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CachedPlan]') AND TYPE IN (N'U'))
DROP TABLE [dbo].[CachedPlan]
GO
CREATE TABLE [dbo].[CachedPlan](
    [cached_plan_id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [cached_plan_Job_id] [int] NOT NULL,
    [dbId] [int] NOT NULL,
    [dbname] [varchar](100) NOT NULL,
    [plan_type] [varchar](50) NOT NULL,
    [objId] [int] NOT NULL,
    [objname] [varchar](100) NOT NULL,
    [sql_batch] [varchar](max) NOT NULL,

I need to pick out

PROCEDURE dbo._ws_CallLogs_DeleteAll

OR

TABLE [dbo].[CachedPlan]

What is the most efficient way to do so?

Have you tried anything already? And in what way do you need it to be efficient - in terms of code, or performance, or disk access...? — Dan Puzey
– Dan Puzey, Commented May 14, 2013 at 9:27
I am trying with Microsoft.Data.Schema.ScriptDom.Sql,Microsoft.Data.Schema.ScriptDom api's... give me some time — priyanka.sarkar
– priyanka.sarkar, Commented May 14, 2013 at 9:32
Are you trying to validate the scripts or to extract strings from them? — Alex Filipovici
– Alex Filipovici, Commented May 14, 2013 at 9:36

huysentruitw · Accepted Answer · 2013-05-14 09:45:34Z

2

Use a regular expression:

Match m = Regex.Match(inputString, @"CREATE\s+(?<obj>.+?)\s*\(", RegexOptions.Singleline);
string objectName = m.Groups["obj"].Value;

edited May 14, 2013 at 9:45

answered May 14, 2013 at 9:39

huysentruitw

28.2k10 gold badges95 silver badges141 bronze badges

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

Collectives™ on Stack Overflow

What can be the most efficient way to parse the object name from the script?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related