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 map开发者_开发问答s 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?

---

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'

---

SELECT object_definition (OBJECT_ID(N'dbo.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');  

---

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