executing dynamic DDL for each object in my database...

Question

executing dynamic DDL for each object in my database...

Eric Mamet

SSChampion

Points: 11728
More actions
January 7, 2016 at 11:01 am

#307574

Hi there,
I have to generate a database as an "almost" copy of another one.
So for most objects, I am planning to make a straight copy of their definition in the source database in my current db (using sys.syscomments).
For most tables, I just want to create a view redirecting to the remote table
I have a stored proc to generate a view for one remote table at a time.
I could write a loop (our coding standards ban cursors) going through each table and firing the stored proc on that table...
This will work and I don't expect performance problems because there are not millions of tables, stored procs, etc to copy.
BUT loops in TSQL are not very "elegant"... 😎
Is there a way I to execute my stored proc on a list of tables returned as a record set.
Something a bit like a CROSS APPLY but executing a stored proc for each row instead of joining two recordsets?
Thanks
Eric

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

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

Lowell SSC Guru Points: 323546 More actions · Answer 1

using cursors or loops to generate commands that pull from metadata related to objects are what i consider the exception to the general rule of avoiding cursors/loops.

the avoid cursor rule is really realted to pulling Data, as it's almost always slower than a set based command.

you can create a single, big old varchar(max) with all of your commands, but i would execute them one at a time in a loop or cursor.

something like this is a one time thing, it's not like you are going to recreate the views on a daily basis.

SELECT '

IF OBJECT_ID(''[dbo].[vw' + name + ']'') IS NOT NULL

DROP VIEW [dbo].[vw' + name + ']

GO

CREATE VIEW [dbo].[vw' + name + ']

AS

SELECT * FROM [dbo].' + name from sys.tables WHERE name LIKE 'Staging%'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Phil Parkin SSC Guru Points: 247180 More actions · Answer 2

Or, if the copy really is 'almost' the same, perhaps you could consider generating an exact copy and then applying the changes to that.

Eric Mamet SSChampion Points: 11728 More actions · Answer 3

I agree that cursors are not always bad but we ban them because it's safer as we have different levels of expertise in house...

OK, I just wondered whether there would be some kind of magic statement I missed!

Thanks

Eric Mamet SSChampion Points: 11728 More actions · Answer 4

Phil Parkin (1/7/2016)
Or, if the copy really is 'almost' the same, perhaps you could consider generating an exact copy and then applying the changes to that.

The "almost" the same database must always contain exactly the same data as the original (hence the views across databases)... BUT for a few cases where the original data must be sort of merge with a few adjustments

And it must keep in sync with the original in terms of data AND of software code updates as well.

I think the approach was right but I was just checking whether there was some kind of "I'll do it all myself for you" command I had missed

Lowell SSC Guru Points: 323546 More actions · Answer 5

just to confirm, there is no way to cross apply and execute RBAR style;

since you mentioned you had a procedure you want to call, you could modify or copy procedure itself to do whatever it does, but since it's doing DDL, it's still a loop somewhere.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!