Replacing DB name reference in objects (views, SPs, UDFs)

Question

Post reply

Replacing DB name reference in objects (views, SPs, UDFs)

johnnycash

Ten Centuries

Points: 1172
More actions
May 16, 2012 at 8:06 am

#255984

Hello to all SQL Gurus!
Once again I come in search of your wisdom and guidance. I have an undetermined number of objects in DB A that have a reference to DB B. Now, DB B needs to be taken offline because a new DB (DB C) is being brought online (C has a much better architecture). Now all references are hardcoded and no synonyms where used.
So my question is, is there a script, tool, or else that can scan the objects, find and replace the name reference? I don't have the time nor the resources to go through every single SP, view, and function (thank god there are no triggers) and do a Find & Replace with the query analyzer.
Any pointers, ideas, pages to visit???
As always you almighty ones, I will forever be in your debt for any help you could provide.
Regards,

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

You might use this to find the words to replace.

CREATE PROCEDURE [dbo].[FindWordsInaSP_5K]

@Watchword varchar(50)

AS

SELECT distinct

'type' = case type

when 'FN' then 'Scalar function'

when 'IF' then 'Inlined table-function'

when 'P' then 'Stored procedure'

when 'TF' then 'Table function'

when 'TR' then 'Trigger'

when 'V' then 'View'

end,

o.[name],

watchword = @Watchword

FROM sys.sysobjects o (NOLOCK)

JOIN sys.syscomments c (NOLOCK)

ON o.id = c.id

WHERE charindex(lower(@Watchword),lower(text)) <> 0

and o.type in ('FN', 'IF', 'P', 'TF', 'TR', 'V')

and o.name NOT LIKE 'dt%' and o.name NOT LIKe 'sys%'

and o.name NOT LIKE 'UDP_FindWordsInaSP'

ORDER BY type, o.[name]

GO

NOT a complete answer to your question, but it will at the least give you a list of items that require alteration.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 2

beware of using syscomments for this - you can get more than one row and the keyword you look for can be split across 2 rows

i would recommend finding them by doing the following

select name,xtype from sysobjects where object_definition(id) like '%myolddb.%'

make sure you use the . in the search string as your 3 part namig will force you to use mydb.myschema.mytable type syntax

MVDBA

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 3

johnnycash (5/16/2012)
Hello to all SQL Gurus!
Once again I come in search of your wisdom and guidance. I have an undetermined number of objects in DB A that have a reference to DB B. Now, DB B needs to be taken offline because a new DB (DB C) is being brought online (C has a much better architecture). Now all references are hardcoded and no synonyms where used.
So my question is, is there a script, tool, or else that can scan the objects, find and replace the name reference? I don't have the time nor the resources to go through every single SP, view, and function (thank god there are no triggers) and do a Find & Replace with the query analyzer.
Any pointers, ideas, pages to visit???
As always you almighty ones, I will forever be in your debt for any help you could provide.
Regards,

I would strongly urge you to make the time. Using T-SQL to find code is rife with pitfalls. It is string searching on a language with many ways to do the same thing, where whitespace is not required to be standardized and you have to account for things like square brackets and quoted identifiers. These are all equivalent as far as the query engine is concerned:

SELECT * FROM dbname.dbo.tablename

SELECT * FROM [dbname] .dbo.tablename

SELECT * FROM dbname . dbo . tablename

SELECT * FROM "dbname". dbo . tablename

Notice the different uses of quoted identifiers, square brackets and whitespace, yet all are valid and equivalent queries.

Here is a query you could use to begin your research:

SELECT s.name AS schema_name,

o.name AS object_name,

o.type_desc,

m.definition

FROM sys.objects o

JOIN sys.schemas s ON o.schema_id = s.schema_id

JOIN sys.sql_modules m ON o.object_id = m.object_id

WHERE m.definition LIKE '%searchtext%';

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Ron McCullough SSC Guru Points: 63877 More actions · Answer 4

bitbucket-25253 (5/16/2012)
You might use this to find the words to replace.
CREATE PROCEDURE [dbo].[FindWordsInaSP_5K]
@Watchword varchar(50)
AS
SELECT distinct
'type' = case type
when 'FN' then 'Scalar function'
when 'IF' then 'Inlined table-function'
when 'P' then 'Stored procedure'
when 'TF' then 'Table function'
when 'TR' then 'Trigger'
when 'V' then 'View'
end,
o.[name],
watchword = @Watchword
FROM sys.sysobjects o (NOLOCK)
JOIN sys.syscomments c (NOLOCK)
ON o.id = c.id
WHERE charindex(lower(@Watchword),lower(text)) <> 0
and o.type in ('FN', 'IF', 'P', 'TF', 'TR', 'V')
and o.name NOT LIKE 'dt%' and o.name NOT LIKe 'sys%'
and o.name NOT LIKE 'UDP_FindWordsInaSP'
ORDER BY type, o.[name]
GO
NOT a complete answer to your question, but it will at the least give you a list of items that require alteration.

After reading the various comments I tested the above code in this series:

FindWordsInaSP_5K 'Exists In Proc'

GO

FindWordsInaSP_5K '"Exists In Proc"'

GO

FindWordsInaSP_5K '(_text)'

GO

FindWordsInaSP_5K '_text)'

GO

FindWordsInaSP_5K '_text'

GO

FindWordsInaSP_5K 'GETDATE()'

GO

FindWordsInaSP_5K '%_WA_Sys%'

GO

FindWordsInaSP_5K '[object_id]'

GO

FindWordsInaSP_5K 'object_id]'

GO

FindWordsInaSP_5K 'object_id'

Each test returned the correct results for SPs and Views, unfortunately I do not have any triggers in my Sandbox DB.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 5

I think all that was pointed out with respect to syscomments is that it will not contain the entire proc definition in a single row if the proc definition contains more than 4000 bytes.

Try compiling a proc like this:

CREATE PROC dbo.some_proc

AS

BEGIN

SET NOCOUNT ON;

--find_this_string

--1

--2

--3

--4

--5

--6

--7

--8

--9

--10

--11

--12

--13

--14

--15

--16

--17

--18

--19

--20

--21

--22

--23

--24

--25

--26

--27

--28

--29

--30

--31

--32

--33

--34

--35

--36

--37

--38

--39

--40

--41

--42

--43

--44

--45

--46

--47

--48

--49

--50

--51

--52

--53

--54

--55

--56

--57

--58

--59

--60

--61

--62

--63

--64

--65

--66

--67

--68

--69

--70

--71

--72

--73

--74

--75

--76

--77

--78

--79

--80

--81

--82

--83

--84

--85

--86

--87

--88

--89

--90

--91

--92

--93

--94

--95

--96

--97

--98

--99

--100

--101

--102

--103

--104

--105

--106

--107

--108

--109

--110

--111

--112

--113

--114

--115

--116

--117

--118

--119

--120

--121

--122

--123

--124

--125

--126

--127

--128

--129

--130

--131

--132

--133

--134

--135

--136

--137

--138

--139

--140

--141

--142

--143

--144

--145

--146

--147

--148

--149

--150

--151

--152

--153

--154

--155

--156

--157

--158

--159

--160

--161

--162

--163

--164

--165

--166

--167

--168

--169

--170

--171

--172

--173

--174

--175

--176

--177

--178

--179

--180

--181

--182

--183

--184

--185

--186

--187

--188

--189

--190

--191

--192

--193

--194

--195

--196

--197

--198

--199

--200

--201

--202

--203

--204

--205

--206

--207

--208

--209

--210

--211

--212

--213

--214

--215

--216

--217

--218

--219

--220

--221

--222

--223

--224

--225

--226

--227

--228

--229

--230

--231

--232

--233

--234

--235

--236

--237

--238

--239

--240

--241

--242

--243

--244

--245

--246

--247

--248

--249

--250

--251

--252

--253

--254

--255

--256

--257

--258

--259

--260

--261

--262

--263

--264

--265

--266

--267

--268

--269

--270

--271

--272

--273

--274

--275

--276

--277

--278

--279

--280

--281

--282

--283

--284

--285

--286

--287

--288

--289

--290

--291

--292

--293

--294

--295

--296

--297

--298

--299

--300

--301

--302

--303

--304

--305

--306

--307

--308

--309

--310

--311

--312

--313

--314

--315

--316

--317

--318

--319

--320

--321

--322

--323

--324

--325

--326

--327

--328

--329

--330

--331

--332

--333

--334

--335

--336

--337

--338

--339

--340

--341

--342

--343

--344

--345

--346

--347

--348

--349

--350

--351

--352

--353

--354

--355

--356

--357

--358

--359

--360

--361

--362

--363

--364

--365

--366

--367

--368

--369

--370

--371

--372

--373

--374

--375

--376

--377

--378

--379

--380

--381

--382

--383

--384

--385

--386

--387

--388

--389

--390

--391

--392

--393

--394

--395

--396

--397

--398

--399

--400

--401

--402

--403

--404

--405

--406

--407

--408

--409

--410

--411

--412

--413

--414

--415

--416

--417

--418

--419

--420

--421

--422

--423

--424

--425

--426

--427

--428

--429

--430

--431

--432

--433

--434

--435

--436

--437

--438

--439

--440

--441

--442

--443

--444

--445

--446

--447

--448

--449

--450

--451

--452

--453

--454

--455

--456

--457

--458

--459

--460

--461

--462

--463

--464

--465

--466

--467

--468

--469

--470

--471

--472

--473

--474

--475

--476

--477

--478

--479

--480

--481

--482

--483

--484

--485

--486

--487

--488

--489

--490

--491

--492

--493

--494

--495

--496

--497

--498

--499

--500

--501

--502

--503

--504

--505

--506

--507

--508

--509

--510

--511

--512

--513

--514

--515

--516

--517

--518

--519

--520

--521

--522

--523

--524

--525

--526

--527

--528

--529

--530

--531

--532

--533

--534

--535

--536

--537

--538

--539

--540

--541

--542

--543

--544

--545

--546

--547

--548

--549

--550

--551

--552

--553

--554

--555

--556

--557

--558

--559

--560

--561

--562

--563

--564

--565

--566

--567

--568

--569

--570

--571

--572

--573

--574

--575

--576

--577

--578

--579

--580

--581

--582

--583

--584

--585

--586

--587

--588

--589

--590

--591

--592

--593

--594

--595

--596

--597

--598

--599

--600

--601

--602

--603

--604

--605

--606

--607

--608

--609

--610

--611

--612

--613

--614

--615

--616

--617

--618

--619

--620

--621

--622

--623

--624

--625

--626

--627

--628

--629

--630

--631

--632

--633

--634

--635

--636

--637

--638

--639

--640

--641

--642

--643

--644

--645

--646

--647

--648

--649

--650

--651

--652

--653

--654

--655

--656

--657

--658

--659

--660

--661

--662

--663

--664

--665

--666

--667

--668

--669

--670

--671

--672

--673

--674

--675

--676

--677

--678

--679

--680

--681

--682

--683

--684

--685

--686

--687

--688

--689

--690

--691

--692

--693

--694

--695

--696

--697

--698

--699

--700

--701

--702

--703

--704

--705

--706

--707

--708

--709

--710

--711

--712

--713

--714

--715

--716

--717

--718

--719

--720

--721

--722

--723

--724

--725

--726

--727

--728

--729

--730

--731

--732

--733

--734

--735

--736

--737

--738

--739

--740

--741

--742

--743

--744

--745

--746

--747

--748

--749

--750

--751

--752

--753

--754

--755

--756

--757

--758

--759

--760

--761

--762

--763

--764

--765

--766

--767

--768

--769

--770

--771

--772

--773

--774

--775

--776

--777

--778

--779

--780

--781

--782

--783

--784

--785

--786

--787

--788

--789

--790

--791

--792

--793

--794

--795

--796

--797

--798

--799

--800

--801

--802

--803

--804

--805

--806

--807

--808

--809

--810

--811

--812

--813

--814

--815

--816

--817

--818

--819

--820

--821

--822

--823

--824

--825

--826

--827

--828

--829

--830

--831

--832

--833

--834

--835

--836

--837

--838

--839

--840

--841

--842

--843

--844

--845

--846

--847

--848

--849

--850

--851

--852

--853

--854

--855

--856

--857

--858

--859

--860

--861

--862

--863

--864

--865

--866

--867

--868

--869

--870

--871

--872

--873

--874

--875

--876

--877

--878

--879

--880

--881

--882

--883

--884

--885

--886

--887

--888

--889

--890

--891

--892

--893

--894

--895

--896

--897

--898

--899

--900

--901

--902

--903

--904

--905

--906

--907

--908

--909

--910

--911

--912

--913

--914

--915

--916

--917

--918

--919

--920

--921

--922

--923

--924

--925

--926

--927

--928

--929

--930

--931

--932

--933

--934

--935

--936

--937

--938

--939

--940

--941

--942

--943

--944

--945

--946

--947

--948

--949

--950

--951

--952

--953

--954

--955

--956

--957

--958

--959

--960

--961

--962

--963

--964

--965

--966

--967

--968

--969

--970

--971

--972

--973

--974

--975

--976

--977

--978

--979

--980

--981

--982

--983

--984

--985

--986

--987

--988

--989

--990

--991

--992

--993

--994

--995

--996

--997

--998

--999

--1000

--find_this_string

END

GO

Then look for it in syscomments:

SELECT *

FROM sys.syscomments

WHERE id IN (

SELECT id

FROM sys.syscomments

WHERE text LIKE '%find_this_string%'

);

You can still find the name using DISTINCT, as in the search proc, but I think the OP wanted a way to magically generate new object definitions using T-SQL with the code ready to recompile to make the DB name change. I would not recommend that approach at all regardless of the catalog-view.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 6

johnnycash (5/16/2012)
So my question is, is there a script, tool, or else that can scan the objects, find and replace the name reference? I don't have the time nor the resources to go through every single SP, view, and function (thank god there are no triggers) and do a Find & Replace with the query analyzer.

Another angle on your question...

Tools like Red Gate Smart Rename (part of SQL Refactor) try to add some intelligence to renaming database objects but I am not sure if it will work on renaming a database used in a 3-part object reference.

You can also look into Visual Studio 2010 Database Projects. There is a refactoring component built into that as well, but again I am not sure if it will apply to a database name as a reference or only objects within a database.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato