Creating Tables, sp , Triggers etc ... from a Snapshot

Question

Creating Tables, sp , Triggers etc ... from a Snapshot

valigula

Valued Member

Points: 60
More actions
December 5, 2008 at 10:30 am

#198541

Is it any API that uses SMO to create the database objects like tables, triggers, sp etc ... from a snapshot??
Thanks

Viewing 12 posts - 1 through 11 (of 11 total)

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

Sorry, I do not follow your question.

SMO is an API, and it can be used to create Tables and other database objects. However, SMO itself actually uses SQL to do almost everything that it does (scripting is one of the few exceptions).

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

valigula Valued Member Points: 60 More actions · Answer 2

first thank you for your reply, and now i will try to explain myself better.

what i need is to be able to create all the tables, triggres etc ( as a SMO objects or other API ) from a snapshot.

The final idea is to be able to compare two snapshots , comparing the objects (without having to create them in a database) and not comparing the snapshots as Strings.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

What do you mean by a "Snapshot"?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

valigula Valued Member Points: 60 More actions · Answer 4

I created a script with all the Database Objects , tables, triggers , SP, views etc .. that is what I called a snapshot. I am trying to create the Database Objects (SMO objects or any other API) again from that script ( snapshot) so after i could compare two scripts by comparing the objects and not as a String.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

Why can't you just execute the script?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

valigula Valued Member Points: 60 More actions · Answer 6

Running the script, will create the objects in a database, right? but i just want to create the smo (or any other API) objects without having to create them in a Database.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

It doesn't work like that. SMO objects are not persistent objects in a database, like SQL Server objects are. SMO objects are ephemeral instances of SMO classes that you create in a client program in order to access, manipulate and manage SQL objects.

There is no tool that I know of to create SMO objects directly from script. The only way that I know of to do it (without writing your own complete Transact-SQL DDL parser, and that's not going to happen anytime soon) is to first create the SQL Objects and then use client code to create the SMO objects from the SQL Objects, which is relatively easy but still requires a lot of code.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

valigula Valued Member Points: 60 More actions · Answer 8

thanks for your time.. i am currenty working in the translator .. but this will take me some time ...

i need it this way because that is a requirement, to compare the 2 snapshot you may not have access to any sql-server.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 9

valigula (12/9/2008)
i need it this way because that is a requirement, to compare the 2 snapshot you may not have access to any sql-server.

Why is this a requirement? It doesn't make any sense.

And why can't you just compare the text of the scripts?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

valigula Valued Member Points: 60 More actions · Answer 10

I think comparing strings it is never as good as comparing objects, extra spaces or tabs .. missing or extra Go´s etc ... depending on the database size that can be an issue, also later you need to reviewed it manually automatic processes are more difficult to achive. Comparing the objects it is always better, also that will be better if later we need to generate a differential script to bring both databases to the same model.

It is long to explain, and dont want to be tedious in the foro.

Thanks for your time

RBarryYoung SSC Guru Points: 143329 More actions · Answer 11

Well, the scripts are not objects and the only thing that can turn them into objects is to execute them in SQL Server. This of course turns them into SQL objects.

So those are your choices:

1) Compare the text

2) Make SQL DB's from them and then compare the DB's (via SMO, 3rd party tools, etc.)

or

3) Write your own SQL DDL parser and build your own object tree from that.

I do not see any other way to do it.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]