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?
EDIT: The DBMS being used is Microsoft SQL Server
This question is related to
sql
sql-server
database
stored-procedures
view
FOR SQL SERVER
IF EXISTS(select * FROM sys.views where name = '')
IN 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
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;
You can check the availability of the view in various ways
FOR SQL SERVER
use 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
IF EXISTS (
SELECT 1
FROM sys.views
WHERE OBJECT_ID = OBJECT_ID(N'[schemaName].[ViewName]')
)
BEGIN
PRINT 'View Exists'
END
use 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
IF EXISTS(
SELECT OBJECT_ID('ViewName', 'V')
)
BEGIN
PRINT 'View Exists'
END
use sys.sql_modules
IF EXISTS (
SELECT 1
FROM sys.sql_modules
WHERE OBJECT_ID = OBJECT_ID('[schemaName].[ViewName]')
)
BEGIN
PRINT 'View Exists'
END
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));
}
}
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
syntax
DROP VIEW [ IF EXISTS ] [ schema_name . ] view_name [ ...,n ] [ ; ]
Query :
DROP VIEW IF EXISTS view_name
More info here
if exists (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[MyTable]') )
if it's Oracle you would use the "all_views" table.
It really depends on your dbms.
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.
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.
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
GO
IF OBJECT_ID( 'nProcedure', 'P' ) IS NOT NULL
DROP PROCEDURE dbo.sprocName;
GO
Source: Stackoverflow.com