SQL 如何检查数据库中是否存在视图？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1306009/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I check if a View exists in a Database?
提问by Draco
I have some SQL code that needs to be executed if a certain View exists in a database. How would I go about checking if the View exists?
如果数据库中存在某个视图，我有一些需要执行的 SQL 代码。我将如何检查视图是否存在？
EDIT: The DBMS being used is Microsoft SQL Server
编辑：正在使用的 DBMS 是 Microsoft SQL Server
回答by kemiller2002
FOR SQL SERVER
对于 SQL 服务器
IF EXISTS(select * FROM sys.views where name = '')
回答by zzlalani
There are already many ways specified above but one of my favourite is missing..
上面已经有很多方法了，但我最喜欢的一种方法不见了..
GO
IF OBJECT_ID('nView', 'V') IS NOT NULL
DROP VIEW nView;
GO
WHERE nViewis the name of view
WHEREnView是视图的名称
UPDATE 2017-03-25:as @hanesjw suggested to drop a Store Procedure use Pinstead of Vas the second argument of OBJECT_ID
更新 2017-03-25：正如@hanesjw 建议删除存储过程使用P而不是V作为第二个参数OBJECT_ID
GO
IF OBJECT_ID( 'nProcedure', 'P' ) IS NOT NULL
DROP PROCEDURE dbo.sprocName;
GO
回答by Eric
This is the most portable, least intrusive way:
这是最便携、最少侵入性的方式：
select
count(*)
from
INFORMATION_SCHEMA.VIEWS
where
table_name = 'MyView'
and table_schema = 'MySchema'
Edit: This does work on SQL Server, and it doesn't require you joining to sys.schemasto get the schema of the view. This is less important if everything is dbo, but if you're making good use of schemas, then you should keep that in mind.
编辑：这确实适用于 SQL Server，并且不需要您加入sys.schemas以获取视图的架构。如果一切正常dbo，这就不那么重要了，但是如果您很好地利用了模式，那么您应该记住这一点。
Each RDBMS has their own little way of checking metadata like this, but information_schemais actually ANSI, and I think Oracle and apparently SQLite are the only ones that don't support it in some fashion.
每个 RDBMS 都有自己的像这样检查元数据的小方法，但information_schema实际上是 ANSI，我认为 Oracle 和显然 SQLite 是唯一不以某种方式支持它的方法。
回答by Kaal
if exists (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[MyTable]') )
回答by P?????
For people checking the existence to drop Viewuse this
对于检查存在的人删除View使用这个
From SQL Server 2016 CTP3you can use new DIEstatements instead of big IFwrappers
从SQL Server 2016 CTP3你可以使用新的DIE语句而不是大IF包装器
syntax
句法
DROP VIEW [ IF EXISTS ] [ schema_name . ] view_name [ ...,n ] [ ; ]
删除视图 [如果存在] [ schema_name 。] 视图名称 [ ...,n ] [ ; ]
Query :
询问 ：
DROP VIEW IF EXISTS view_name
More info here
更多信息在这里
回答by UJS
IN SQL Server ,
在 SQL Server 中，
declare @ViewName nvarchar(20)='ViewNameExample'
if exists(SELECT 1 from sys.objects where object_Id=object_Id(@ViewName) and Type_Desc='VIEW')
begin
-- Your SQL Code goes here ...
end
回答by user158017
if it's Oracle you would use the "all_views" table.
如果是 Oracle，您将使用“all_views”表。
It really depends on your dbms.
这真的取决于你的数据库管理系统。
回答by Sriwantha Attanayake
If you want to check the validity and consistency of all the existing views you can use the following query
如果要检查所有现有视图的有效性和一致性，可以使用以下查询
declare @viewName sysname
declare @cmd sysname
DECLARE check_cursor CURSOR FOR
SELECT cast('['+SCHEMA_NAME(schema_id)+'].['+name+']' as sysname) AS viewname
FROM sys.views
OPEN check_cursor
FETCH NEXT FROM check_cursor
INTO @viewName
WHILE @@FETCH_STATUS = 0
BEGIN
set @cmd='select * from '+@viewName
begin try
exec (@cmd)
end try
begin catch
print 'Error: The view '+@viewName+' is corrupted .'
end catch
FETCH NEXT FROM check_cursor
INTO @viewName
END
CLOSE check_cursor;
DEALLOCATE check_cursor;
回答by joe
To expand on Kevin's answer.
扩展凯文的答案。
private bool CustomViewExists(string viewName)
{
using (SalesPad.Data.DataConnection dc = yourconnection)
{
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(String.Format(@"IF EXISTS(select * FROM sys.views where name = '{0}')
Select 1
else
Select 0", viewName));
cmd.CommandType = CommandType.Text;
return Convert.ToBoolean(dc.ExecuteScalar(cmd));
}
}
回答by Reza Jenabi
You can check the availability of the view in various ways
您可以通过多种方式检查视图的可用性
FOR SQL SERVER
对于 SQL 服务器
use sys.objects
使用sys.objects
IF EXISTS(
SELECT 1
FROM sys.objects
WHERE OBJECT_ID = OBJECT_ID('[schemaName].[ViewName]')
AND Type_Desc = 'VIEW'
)
BEGIN
PRINT 'View Exists'
END
use sysobjects
使用系统对象
IF NOT EXISTS (
SELECT 1
FROM sysobjects
WHERE NAME = '[schemaName].[ViewName]'
AND xtype = 'V'
)
BEGIN
PRINT 'View Exists'
END
use sys.views
使用sys.views
IF EXISTS (
SELECT 1
FROM sys.views
WHERE OBJECT_ID = OBJECT_ID(N'[schemaName].[ViewName]')
)
BEGIN
PRINT 'View Exists'
END
use INFORMATION_SCHEMA.VIEWS
使用INFORMATION_SCHEMA.VIEWS
IF EXISTS (
SELECT 1
FROM INFORMATION_SCHEMA.VIEWS
WHERE table_name = 'ViewName'
AND table_schema = 'schemaName'
)
BEGIN
PRINT 'View Exists'
END
use OBJECT_ID
使用OBJECT_ID
IF EXISTS(
SELECT OBJECT_ID('ViewName', 'V')
)
BEGIN
PRINT 'View Exists'
END
use sys.sql_modules
使用sys.sql_modules
IF EXISTS (
SELECT 1
FROM sys.sql_modules
WHERE OBJECT_ID = OBJECT_ID('[schemaName].[ViewName]')
)
BEGIN
PRINT 'View Exists'
END
