問(wèn)題描述

我有一個(gè)包含多個(gè)視圖的數(shù)據(jù)庫(kù)，這些視圖過(guò)去已被用戶手動(dòng)重命名.

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

有沒(méi)有辦法獲得視圖的正確定義，因?yàn)樗鼘⑸删哂行旅Q的視圖?或者，有沒(méi)有辦法提取視圖的幕后名稱，以便我可以在使用它之前將它replace 定義中的正確名稱?

真正令人討厭的是，如果我使用 GUI 將Script View as"創(chuàng)建到 >新的查詢編輯器窗口"它會(huì)生成正確的 CREATE 腳本，因此 SSMS 顯然可以通過(guò)某種方式訪問(wèn)??此信息:

有沒(méi)有辦法獲得視圖的正確定義，因?yàn)樗鼘⑸删哂行旅Q的視圖?

是的.使用 SMO(在代碼或 PowerShell 中)或 SSMS(使用 SMO)來(lái)編寫視圖腳本.SMO 是適用于所有 SQL Server 對(duì)象的全保真腳本引擎.

使用 Powershell，您可以輕松實(shí)現(xiàn)自動(dòng)化，例如:

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

從 Profiler 來(lái)看，這里沒(méi)有服務(wù)器端功能在起作用.SMO 獲取視圖定義，并有一個(gè) TSQL 解析器，因此它可以在編寫對(duì)象腳本時(shí)修復(fù) DDL.這是 SMO 運(yùn)行以獲取視圖正文的查詢:

SELECT投擲(服務(wù)器屬性(N''服務(wù)器名'')AS sysname) AS [Server_Name],db_name() AS [Database_Name],SCHEMA_NAME(v.schema_id) AS [架構(gòu)]，v.name AS [名稱],CAST(ISNULL(OBJECTPROPERTYEX(v.object_id,N''ExecIsQuotedIdentOn''),0) AS bit) AS [QuotedIdentifierStatus],投擲(案件當(dāng) v.is_ms_shipped = 1 然后 1什么時(shí)候 (選擇主要_id從sys.extended_properties在哪里Major_id = v.object_id 和minor_id = 0 和類 = 1 和name = N''microsoft_database_tools_support'')不為空則為 1否則 0結(jié)尾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)訂購(gòu)者[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)

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