有没有一种方法可以使用普通ADO从SQL Server检索视图定义？ [英] Is there a way to retrieve the view definition from a SQL Server using plain ADO?

问题描述

我已经成功地使用ADO Connection OpenSchema（）调用从SQL服务器上托管的数据库中提取列定义，因此可以以编程方式在另一个SQL数据库。

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.

与上述表格的主要交互是通过多个视图进行的；尽管 OpenSchema（）能够以返回表的列定义的相同方式返回视图的列定义，但缺少了关键的信息-哪个表

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.

我尝试使用ADOX Catalog Views访问用于创建视图的SQL命令，但是看起来我们正在使用的用于SQL Server的OLEDB驱动程序不支持此功能。

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.

是否可以通过ADO获取有关视图配置的信息？表示 ColumnX映射到表Z中的ColumnY还是采用用于创建视图的实际SQL命令的形式？

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?

推荐答案

哪个版本的SQL Server？

Which version of SQL Server?

对于SQL Server 2005和更高版本，您可以获取用于创建视图的SQL脚本，如下所示：

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.

注意事项

• 如果上次使用ALTER VIEW修改了视图，则脚本将是ALTER VIEW语句，而不是CREATE VIEW语句。

• If the view was last modified with ALTER VIEW, then the script will be an ALTER VIEW statement rather than a CREATE VIEW statement.

该脚本反映了创建时的名称。它只有在执行ALTER VIEW或使用CREATE VIEW拖放并重新创建视图后才更新。如果视图已重命名（例如，通过 sp_rename ）或所有权已转移到其他模式，则返回的脚本将反映原始的CREATE / ALTER VIEW语句：

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 个字符的结果。

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.

这篇关于有没有一种方法可以使用普通ADO从SQL Server检索视图定义？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！