Is there a way to get a view definition with SQL Server using simple ADO?

Question

I successfully retrieve column definitions from databases hosted on an SQL server using the ADO Connection OpenSchema() call in my various incarnations so that I can programmatically recreate these tables in another SQL database. So far so good.

The main interaction with the above tables occurs using several types; while OpenSchema() can return column definitions for a view in the same way that it returns column definitions for a table, there is no critical bit of information - which table and column in the base tables the column in the view maps to.

I tried to access the SQL command used to create the view using the ADOX directory views, but it looks like the SQL Server OLEDB driver that we use does not support this functionality.

Is there any way to get this information to configure the view through ADO, either in such a way that "ColumnX maps to ColumnY in table Z" or in the form of the actual SQL command used to create the view?

Answer 1

What version of SQL Server?

For SQL Server 2005 and later, you can get the SQL script used to create the view as follows:

 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 line containing the script used to create / change the view.

The other columns in the table talk about in-place settings during compilation of the view.

Warning

• If the last change was modified using ALTER VIEW, the script will be an ALTER VIEW statement, not a CREATE VIEW statement.

• The script reflects the name as it is created. The only time it is updated is if you execute ALTER VIEW, or drop and recreate the view using CREATE VIEW. If the view has been renamed (for example, via sp_rename ), or the property is transferred to another scheme, the returned script will display the original CREATE / ALTER VIEW statement: it will not reflect the current name of the object.

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

Answer 2

For SQL 2000 users, 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' 

Answer 3

Microsoft has indicated the following methods for obtaining 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'; 

Answer 4

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