Generate SQL scripts - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

Generate SQL scripts

Author

Topic

dcobb
Yak Posting Veteran

76 Posts

Posted - 2004-02-13 : 11:41:57

Just tried to create some scripts for all of the stored procedures in my database (backing them up). Stored all of the stored procedures in one file. But for some reason I get duplicate copies of some (not all) of the them.

Any reason as to why this should happen?

Cheers

dC

X002548
Not Just a Number

15586 Posts

Posted - 2004-02-13 : 11:53:33

You sure you didn't cut and paste it?

What version?

Never heard of this...do you have sprocs that look similar?

Brett

8-)

nr
SQLTeam MVY

12543 Posts

Posted - 2004-02-13 : 13:07:51

Same name different owner?
see
http://www.nigelrivett.net/DMOScriptAllDatabases.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

MuadDBA

628 Posts

Posted - 2004-02-13 : 15:49:01

I would bet what happened is that you renamed the procedure using sp_rename (or via enterprise manager). Unfrotunately, this does NOT alter their create syntax stored in the system tables. It's a pain in the but and a good reason to avoid using the sp_rename function on stored procedures.

dcobb
Yak Posting Veteran

76 Posts

Posted - 2004-02-15 : 11:09:24

The renaming does ring a bell. Is there anyway of deleting the duplicates at all without deleting the ones I need?

Cheers

Dave

dcobb
Yak Posting Veteran

76 Posts

Posted - 2004-02-17 : 06:37:56

So how can I remove the duplicate stored procedures from the system tables?

Anyone?

TIA

Dave

nr
SQLTeam MVY

12543 Posts

Posted - 2004-02-17 : 08:07:13

Seems if you create a procedure then rename it and create a new one with the old name when you script you get the original create statements - i.e. you don't get a create for the renamed SP with the correct name - but you do get the drops.

As crazyjoe says the cause of the problem is that the entry in syscomments is for the original create.
And this is what you will get if you try to edit in enterprise manager or run sp_helptext.

You can write a script to go through syscomments checking the create proc name against the object name - there are problems with comments before the create statement - I'll see about writing a script

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-02-17 : 08:41:20

Well this should identify those that have a name different from that in the text.
You can then drop and recreate them.

I've tried to cater for all the create syntaxes I can think of - you will probably find others but can add them.

create table #a (procid int, data nvarchar(4000))
insert #a select o.id, substring(c.text,1,4000) from syscomments c, sysobjects o where c.id = o.id and o.type = 'P' and c.colid = 1
declare @whitespace varchar(10)
declare @notwhitespace varchar(10)
select @whitespace = '%[ ' + char(10) + char(13) + ']%'
select @notwhitespace = '%[^ ' + char(10) + char(13) + ']%'
-- get rid of data before the create
update #a set data = substring(data,patindex('%' + char(10) + '[cC][rR][eE][aA][tT][eE]%',data)+1,4000)
-- get rid of create
update #a set data = substring(data,patindex(@notwhitespace,data),4000)
-- get rid of procedure
update #a set data = substring(data,patindex(@whitespace,data)+1,4000)
update #a set data = substring(data,patindex(@notwhitespace,data),4000)
-- get rid of whitespace before the name
update #a set data = substring(data,patindex(@whitespace,data)+1,4000)
update #a set data = substring(data,patindex(@notwhitespace,data),4000)
-- get rid of data after the name
update #a set data = substring(data,1,patindex(@whitespace,data)-1)
-- cater for no whitespace before parameters
update #a set data = substring(data,1,patindex('%(%',data)-1) where patindex('%(%',data) > 1
-- get rid of the owner
update #a set data = substring(data,patindex('%.%',data)+1,4000)
-- get rid of []
update #a set data = replace(data,'[','')
update #a set data = replace(data,']','')
-- data should now be the proc name
-- get the ones that don't match
select object_name = object_name(procid), syscomments_create = data from #a where data <> object_name(procid)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

dcobb
Yak Posting Veteran

76 Posts

Posted - 2004-02-17 : 09:59:22

NR, your a star!

Using your script I managed to find out that I had several 'backup' stored procedures that must have been a copy of the originals, just renamed through enterprise manager. I have now deleted all the offending little buggers and it works a treat.

Thank you very much.

Dave

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources