問題描述

我有一個包含多個視圖的數據庫，這些視圖過去已被用戶手動重命名.

如果我嘗試獲取視圖的定義以便我可以在其他地方編寫它的創建腳本，

有沒有辦法獲得視圖的正確定義，因為它將生成具有新名稱的視圖?或者，有沒有辦法提取視圖的幕后名稱，以便我可以在使用它之前將它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)

這篇關于如何獲得具有正確名稱的視圖的定義?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！