Script Out T-SQL Code Programmatically

I'm looking for a way to programmatically (ideally using T-SQL) script
out code objects in order of precedence .
I must make clear that I'm quite satisfied that I have found a way of
listing the names Views, Stored Procs & Functions that need to be
changed in order of precedence (eg if SP spEmployeeUpdate depends on
view vselEmployee which in turn depends on table tblEmployee then if
tblEmployee has been updated you need to recompile (?) vselEmployee
then spEmployeeUpdate). OK, so that's what this post is not about!
So . . . I have a temporary T-SQL table which comprises in order the
code objects which need to be recompiled - all well and good but how
can I script out the objects so I can run the script once and it's
done?
Using the above example, after tblEmployee has been updated I would
like to generate code like this . . .
ALTER VIEW vselEmployee
AS
BEGIN
|
|
END
GO
ALTER PROCEDURE spEmployeeUpdate
AS
BEGIN
|
|
END
GO
I've tried using SysComments.Text (which contains the code) but without
success (truncation of text, loss of formatting etc).
I know that Enterprise Manager allows me to script out a selection of
code objects but 1) I can't control it programmatically; 2) it's not in
any useful order; 3) It uses "DROP" & "CREATE" rather than the
preferred "ALTER" etc.
I've seen several posts on this topic but they tend to get bogged down
on the bit I've already solved.
Any ideas?Hi
You may want to look at using DMO to do this, similar to
http://www.nigelrivett.net/DMOScriptAllDatabases.html
John
"Pete Nurse" wrote:

> I'm looking for a way to programmatically (ideally using T-SQL) script
> out code objects in order of precedence .
> I must make clear that I'm quite satisfied that I have found a way of
> listing the names Views, Stored Procs & Functions that need to be
> changed in order of precedence (eg if SP spEmployeeUpdate depends on
> view vselEmployee which in turn depends on table tblEmployee then if
> tblEmployee has been updated you need to recompile (?) vselEmployee
> then spEmployeeUpdate). OK, so that's what this post is not about!
> So . . . I have a temporary T-SQL table which comprises in order the
> code objects which need to be recompiled - all well and good but how
> can I script out the objects so I can run the script once and it's
> done?
> Using the above example, after tblEmployee has been updated I would
> like to generate code like this . . .
> ALTER VIEW vselEmployee
> AS
> BEGIN
> |
> |
> END
> GO
> ALTER PROCEDURE spEmployeeUpdate
> AS
> BEGIN
> |
> |
> END
> GO
> I've tried using SysComments.Text (which contains the code) but without
> success (truncation of text, loss of formatting etc).
> I know that Enterprise Manager allows me to script out a selection of
> code objects but 1) I can't control it programmatically; 2) it's not in
> any useful order; 3) It uses "DROP" & "CREATE" rather than the
> preferred "ALTER" etc.
> I've seen several posts on this topic but they tend to get bogged down
> on the bit I've already solved.
> Any ideas?
>|||Thanks John, that's excellent code - very clear and well written. I'm
busy now trying to work out what it's doing!