[sql-server] Is there a way to retrieve the view definition from a SQL Server using plain ADO?

I'm successfully extracting column definitions from databases hosted on a SQL server using the ADO Connection OpenSchema() call in its various incarnations so I can programmatically recreate those tables in another SQL database. So far, so good.

The main interaction with the above tables happens using multiple views; while OpenSchema() is able to return the column definitions for the view in the same way that it returns column definitions for a table, a crucial bit of information is missing - which table and column in the underlying tables the column in the view maps to.

I tried to access the SQL command used to create the view using ADOX Catalog Views, but it appears that the OLEDB driver for SQL Server that we're using doesn't support this functionality.

Is there any way to get at this information for the view configuration via ADO, either in a way that states "ColumnX maps to ColumnY in table Z" or in the form of the actual SQL command used to create the view?

This example:Views Collection, CommandText Property Example (VB) Shows how to use ADOX to maintain VIEWS by changing COMMAND related to VIEW. But instead using it like this:

 Set cmd = cat.Views("AllCustomers").Command  

' Update the CommandText of the command.  
cmd.CommandText = _  
"Select CustomerId, CompanyName, ContactName From Customers"  

just try to use this way:

Set CommandText = cat.Views("AllCustomers").Command.CommandText

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'

SELECT object_definition (OBJECT_ID(N'dbo.vEmployee'))

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';

You can get table/view details through below query.

For table :sp_help table_name For View :sp_help view_name

SELECT definition, uses_ansi_nulls, uses_quoted_identifier, is_schema_bound  
FROM sys.sql_modules  
WHERE object_id = OBJECT_ID('your View Name');