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?

261544 次浏览
小开
最佳答案

哪个版本的 SQLServer？

对于 SQLServer2005及更高版本，可以获得用于创建视图的 SQL 脚本，如下所示:

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'

这将返回一行，其中包含用于创建/更改视图的脚本。

表中的其他列说明了视图编译时的选项。

警告

  • 如果视图最后一次使用 ALTERVIEW 修改，那么脚本将是 ALTERVIEW 语句，而不是 CREATEVIEW 语句。

  • 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.

  • 有些工具会截断输出。例如，MS-SQL 命令行工具 sqlcmd.exe 将数据截断为255个字符。您可以传递参数 -y N来获得使用 N字符的结果。

小开

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'
小开

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

您可以通过以下查询获得表/视图详细信息。

对于 table: < strong > sp _ help table _ name 视图: < strong > 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');
小开

这个例子: < a href = “ https://Learn.microsoft.com/en-us/sql/ado/reference/adox-api/Views-Collection-CommandText-Property-Example-VB? view = sql-server-ver15”rel = “ nofollow noReferrer”> 视图集合，CommandText 属性示例(VB) 演示如何使用 ADOX 通过更改与视图相关的 COMMAND 来维护视图。 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"

试着用这种方式:

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

提交答案