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

Question

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

User · Answer

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

User · Answer

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     lt view name here gt     and o type         V

User · Answer

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  quot AllCustomers quot   Command      Update the CommandText of the command    cmd CommandText        quot Select CustomerId  CompanyName  ContactName From Customers quot     just try to use this way  Set CommandText   cat Views  quot AllCustomers quot   Command CommandText

User · Answer

SELECT object definition  OBJECT ID N dbo vEmployee

User · Answer

SELECT definition  uses ansi nulls  uses quoted identifier  is schema bound   FROM sys sql modules   WHERE object id   OBJECT ID  your View Name

User · Answer

You can get table view details through below query   For table  sp help table name For View  sp help view name

User · Answer

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

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

Examples related to sql-server

Examples related to view

Examples related to ado