Is there a way to retrieve the view definition from a SQL Server using plain ADO?

Which version of SQL Server?

For SQL Server 2005 and later, you can obtain the SQL script used to create the view like this:

select definition
from sys.objects     o
join sys.sql_modules m on m.object_id = o.object_id
where o.object_id = object_id( 'dbo.MyView')
  and o.type      = 'V'

This returns a single row containing the script used to create/alter the view.

Other columns in the table tell about about options in place at the time the view was compiled.

Caveats

• If the view was last modified with ALTER VIEW, then the script will be an ALTER VIEW statement rather than a CREATE VIEW statement.

• The script reflects the name as it was created. The only time it gets updated is if you execute ALTER VIEW, or drop and recreate the view with CREATE VIEW. If the view has been renamed (e.g., via sp_rename) or ownership has been transferred to a different schema, the script you get back will reflect the original CREATE/ALTER VIEW statement: it will not reflect the objects current name.

• Some tools truncate the output. For example, the MS-SQL command line tool sqlcmd.exe truncates the data at 255 chars. You can pass the parameter -y N to get the result with N chars.

Microsoft listed the following methods for getting the a View definition: http://technet.microsoft.com/en-us/library/ms175067.aspx

---

USE AdventureWorks2012;
GO
SELECT definition, uses_ansi_nulls, uses_quoted_identifier, is_schema_bound
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('HumanResources.vEmployee'); 
GO

---

USE AdventureWorks2012; 
GO
SELECT OBJECT_DEFINITION (OBJECT_ID('HumanResources.vEmployee')) 
AS ObjectDefinition; 
GO

---

EXEC sp_helptext 'HumanResources.vEmployee';

For users of SQL 2000, the actual command that will provide this information is:

select c.text
from sysobjects     o
join syscomments    c on c.id = o.id
where o.name = '<view_name_here>'
  and o.type      = 'V'