Question a) Its possible to do this by using the "syscomments" table. This
table contains the source for the objects that you ask for. Only thing to
remember is that the source of the object is split into 4K chunks (although
the column is called text :-)) Thus, you might get multiple lines which you
will have to add-up.

Question b) You can remote control enterprise manager to an extent using
SQLNS (SQL Namespaces), but I've not worked extensively with it. Another
option is to use SQLDMO, which supports object creation scripts using its
API. Its really powerful. Lastly, I would consider DTS.

Both SQLNS and SQLDMO have sufficient samples in books online.
--
HTH,
SriSamp
Please reply to the whole group only!
[url]http://www32.brinkster.com/srisamp[/url]

"Ralf Hermanns" <ralf.hermannsgmx.de> wrote in message
news:uFTqrCGRDHA.2036TK2MSFTNGP12.phx.gbl...
> Hello
>
> I am writing myself a tool to compare two SQL Server Databases, to find
> missing or changed objects. I know such tools exist, but I consider it a
> learning experience. In a second version, my tool should perhaps be able
to
> copy/replace the objects that need to be updated.
>
> I think I understood how to use INFORMATION_SCHEMA.xyz to get a list of
> tables, columns or views.
>
> Question a)
> What I cannot find out is how to get the source sql statement of a view.
Is
> there a way to retrieve that information? If there is, can it be used for
> stored procedures or user defined functions as well?
>
> Question b)
> As mentioned above, I would like to "copy" missing objects from one db to
> the other. What way would be best to do that?
> Can you "remote control" the enterprise manager, to use its script
> generation for those objects to be copied?
> Or is it advised to instruct DTS to duplicate the tables, views and other
> objects? If so, how?
>
> I looked into SQL Server Books online and google, but could not really
find
> a good answer.
> If someone can post a line of response to my questions, or maybe supply a
> link explaining the things asked, I would really appreciate it.
>
> Thanks (and have a good week)
> Ralf
>
>