如何获得具有正确名称的视图的定义? [英] How can I get the definition of a view with the correct name?

问题描述

我有一个包含多个视图的数据库，这些视图过去已被用户手动重命名.

如果我尝试获取视图的定义以便我可以在其他地方编写它的创建脚本，

有没有办法获得视图的正确定义，因为它将生成具有新名称的视图?或者，有没有办法提取视图的幕后名称，以便我可以在使用它之前将它replace 定义中的正确名称?

---

真正令人讨厌的是，如果我使用 GUI 将Script View as"创建到 >新的查询编辑器窗口"它会生成正确的 CREATE 脚本，因此 SSMS 显然可以通过某种方式访问​​此信息:

解决方案

有没有办法获得视图的正确定义，因为它将生成具有新名称的视图?

是的.使用 SMO(在代码或 PowerShell 中)或 SSMS(使用 SMO)来编写视图脚本.SMO 是适用于所有 SQL Server 对象的全保真脚本引擎.

使用 Powershell，您可以轻松实现自动化，例如:

$sql = Get-SqlInstance -ServerInstance "localhost";$db = $sql |get-sqldatabase -Name "AdventureWorks2017";foreach($db.Views 中的 $v){[Microsoft.SqlServer.Management.Smo.View] $vv = $v写主机 $vv.Script()}

从 Profiler 来看，这里没有服务器端功能在起作用.SMO 获取视图定义，并有一个 TSQL 解析器，因此它可以在编写对象脚本时修复 DDL.这是 SMO 运行以获取视图正文的查询:

SELECT投掷(服务器属性(N''服务器名'')AS sysname) AS [Server_Name],db_name() AS [Database_Name],SCHEMA_NAME(v.schema_id) AS [架构]，v.name AS [名称],CAST(ISNULL(OBJECTPROPERTYEX(v.object_id,N''ExecIsQuotedIdentOn''),0) AS bit) AS [QuotedIdentifierStatus],投掷(案件当 v.is_ms_shipped = 1 然后 1什么时候 (选择主要_id从sys.extended_properties在哪里Major_id = v.object_id 和minor_id = 0 和类 = 1 和name = N''microsoft_database_tools_support'')不为空则为 1否则 0结尾AS 位) AS [IsSystemObject],CAST(ISNULL(OBJECTPROPERTYEX(v.object_id,N''ExecIsAnsiNullsOn''),0) AS bit) AS [AnsiNullsStatus],v.object_id AS [ID],NULL AS [文本],ISNULL(smv.definition, ssmv.definition) AS [定义]从sys.all_views AS v左外连接 sys.sql_modules AS smv ON smv.object_id = v.object_id左外连接 sys.system_sql_modules AS ssmv ON ssmv.object_id = v.object_id在哪里(v.type = @_msparam_0)and(v.name=@_msparam_1 and SCHEMA_NAME(v.schema_id)=@_msparam_2)订购者[Database_Name] ASC,[Schema] ASC,[Name] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000)

I have a database with several views that have been manually renamed by users in the past.

If I try to get the definition of the view so that I can script its creation elsewhere, the name comes out wrong. I can get a list of databases with "wrong" names behind-the-scenes using:

SELECT OBJECT_NAME(object_id), definition
FROM sys.sql_modules
WHERE convert(nvarchar(200),definition) not like ('%'+OBJECT_NAME(object_id)+'%')

Is there any way to get the correct definition of the view, in that it will generate the view with the new name? Alternatively, is there a way to extract the behind-the-scenes name of the view so that I can replace it with the correct name in the definition before using it?

---

What's really annoying is that if I use the GUI to "Script View as > CREATE to > New Query Editor Window" it results in the correct CREATE script, so SSMS obviously has some way of getting access to this information:

解决方案

Is there any way to get the correct definition of the view, in that it will generate the view with the new name?

Yes. Use SMO (in code or PowerShell) or SSMS (which uses SMO) to script the view. SMO is the full-fidelity scripting engine for all SQL Server objects.

With Powershell you can automate this easilly, eg:

$sql = Get-SqlInstance -ServerInstance "localhost"
$db = $sql | get-sqldatabase -Name "AdventureWorks2017"
foreach ($v in $db.Views)
{
   [Microsoft.SqlServer.Management.Smo.View] $vv = $v
   write-host $vv.Script()
}

And from Profiler, there's no server-side functionality at work here. SMO fetches the view definition, and has a TSQL parser so it can fix the DDL while scripting the object. Here's the query SMO runs to get the view body:

SELECT
CAST(
        serverproperty(N''Servername'')
       AS sysname) AS [Server_Name],
db_name() AS [Database_Name],
SCHEMA_NAME(v.schema_id) AS [Schema],
v.name AS [Name],
CAST(ISNULL(OBJECTPROPERTYEX(v.object_id,N''ExecIsQuotedIdentOn''),0) AS bit) AS [QuotedIdentifierStatus],
CAST(
 case 
    when v.is_ms_shipped = 1 then 1
    when (
        select 
            major_id 
        from 
            sys.extended_properties 
        where 
            major_id = v.object_id and 
            minor_id = 0 and 
            class = 1 and 
            name = N''microsoft_database_tools_support'') 
        is not null then 1
    else 0
end          
             AS bit) AS [IsSystemObject],
CAST(ISNULL(OBJECTPROPERTYEX(v.object_id,N''ExecIsAnsiNullsOn''),0) AS bit) AS [AnsiNullsStatus],
v.object_id AS [ID],
NULL AS [Text],
ISNULL(smv.definition, ssmv.definition) AS [Definition]
FROM
sys.all_views AS v
LEFT OUTER JOIN sys.sql_modules AS smv ON smv.object_id = v.object_id
LEFT OUTER JOIN sys.system_sql_modules AS ssmv ON ssmv.object_id = v.object_id
WHERE
(v.type = @_msparam_0)and(v.name=@_msparam_1 and SCHEMA_NAME(v.schema_id)=@_msparam_2)
ORDER BY
[Database_Name] ASC,[Schema] ASC,[Name] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000)

这篇关于如何获得具有正确名称的视图的定义?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！