Re: Get source statement of a view, proc or func

#1

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!
http://www32.brinkster.com/srisamp

"Ralf Hermanns" <ralf.hermanns (AT) gmx (DOT) de> wrote

Quote:

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: Get source statement of a view, proc or func

microsoft.public.sqlserver.tools microsoft.public.sqlserver.tools

Re: Get source statement of a view, proc or func - 07-07-2003 , 03:35 AM