如何检查数据库中是否存在视图?

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。

9 个解决方案

#1

131

FOR SQL SERVER

对SQL SERVER

IF EXISTS(select * FROM sys.views where name = '')

#2

112

Although 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 nView is the name of view

视图的名字在哪里

UPDATE 2017-03-25: as @hanesjw suggested to drop a Store Procedure use P instead of V as 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

#3

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.schemas to 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。获取视图模式的模式。如果所有内容都是dbo，那么这就不那么重要了，但是如果您正在很好地使用模式，那么您应该记住这一点。

Each RDBMS has their own little way of checking metadata like this, but information_schema is 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是唯一不支持它的。

#4

if exists (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[MyTable]') )

#5

For people checking the existence to drop View use this

对于想要查看存在与否的人，请使用这个

From SQL Server 2016 CTP3 you can use new DIE statements instead of big IF wrappers

从SQL Server 2016 CTP3中，可以使用新的DIE语句，而不是大的IF包装器。

syntax

语法

DROP VIEW [ IF EXISTS ] [ schema_name . ] view_name [ ...,n ] [ ; ]

删除视图[IF存在][schema_name。]view_name[…n][;]

Query :

查询:

DROP VIEW IF EXISTS view_name

More info here

更多的信息在这里

#6

if it's Oracle you would use the "all_views" table.

如果是Oracle，则使用“all_views”表。

It really depends on your dbms.

这取决于你的数据库管理系统。

#7

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;

#8

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));
        }
    }

#9

IN SQL Server ,

在SQL服务器,

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

#1

131