How can I programmatically retrieve the alter view script for a view in SQL Server 2005.

Question

We allow our uses to alter certain views for reports and what not based on some application field meta data that we keep track of in our application. These fields can be created at run time. I have a standard process in place to alter the views when a field is added or removed. I now need to do this programmatically however, which means I need to be able to pull the current Alter view script, make my modifications, and then execute it against the database. The last two steps are easy enough, but the first part is giving me some trouble.

Design decisions aside (as they are out of my hands in this particular instance). I would like to know how to retrieve the Alter view script that Sql server management studio uses for the View->Edit command.

I require the exact same output as that command because I have comment hooks in my scripts that allow my edits to occur.

Related questions, but not quite what I am looking for.

How do I programmatically retrieve SQL Server stored procedure source that is identical to the source returned by the SQL Server Management Studio gui?

In MSSQL, how do I generate a CREATE TABLE statement for a given table?

Answer 1

SSMS uses SMO under the covers. The Scripter can retrieve the definition of any SQL object.

If you want a T-SQL based approach, then use sys.sql_modules, it contains the T-SQL definition of every non-encrypted object in the database, including views.

Answer 2

I think I have the answer to this finally. Poking around the SQL profiler I came across a query that I modified to the following.

DECLARE @ViewName VARCHAR(255)

SET @ViewName = N'vwMyView'

SELECT
    ISNULL(smv.definition, ssmv.definition) AS [Definition]
FROM
    sys.all_views AS v
    LEFT OUTER JOIN sys.sql_modules AS smv ON smv.object_id = v.object_id
    LEFT OUTER JOIN sys.system_sql_modules AS ssmv ON ssmv.object_id = v.object_id
WHERE
    (v.type = 'V') and (v.name = @ViewName and SCHEMA_NAME(v.schema_id) = N'dbo')

This returns my view script as a 'Create View' ddl statement complete with comments. It is on one line however, so I have to insert newlines after any comments. We will have to see how feasible this turns out to be.

Answer 3

SELECT [definition] 
FROM sys.sql_modules
WHERE [object_id] = OBJECT_ID('dbo.' + @ViewName);

-- you don't need the type check if you are passing in the name of a view
-- you can do the schema check using OBJECT_ID instead of an extra join

-- if you want to reference system_sql_modules it is unlikely they
-- will have dbo schema, if you want to support this use a separate query IMHO