CODEWITHSUNDEEP
sqlsql-servert-sql
alpha
alpha

Reputation: 13

Check if View exists before querying it

I want to check if a specific View exists before querying it. I use dynamic SQL to create the query:

DECLARE @sqlCommand varchar(1000)
DECLARE @viewName varchar(1000)
DECLARE @otherDB varchar(1000)

SET @sqlCommand = 'IF EXISTS(SELECT 1 FROM ' + @otherDB + '.sys.views WHERE name=''' + @viewName + ''')
BEGIN
SELECT * FROM ' + @viewName + '
END'

EXEC (@sqlCommand)

So everything works fine as long as @viewName actually exists. However, if @viewName is a View that does not exist in sys.views, I get an error from the compiler:

The OLE DB provider "SQLNCLI11" for linked server "server" does not contain the table @viewName. The table either does not exist or the current user does not have permiossions on that table.

I would have thought that since an IF statement is used, it would just skip the querying of the View. However, seems like the View has to exist otherwise I get the above error.

I've tried alternate solutions, such as using strings for the View names, but no luck. I've also tried the solution in: How to check the existence of a view, but at some point I have to reference the View name in my query, and it would complain

Any info would be greatly appreciated!

Upvotes: 1

Views: 3189

Answers (4)

BCdotWEB
BCdotWEB

Reputation: 1048

Beware if your view is in a different schema, because then you need to also check the SCHEMAS table:

SELECT 1 FROM SYS.VIEWS 
INNER JOIN SYS.SCHEMAS ON SYS.SCHEMAS.schema_id = SYS.VIEWS.schema_id
WHERE SYS.VIEWS.TYPE='V'
    AND SYS.SCHEMAS.NAME=@Your_Schema_Name  
    AND SYS.VIEWS.NAME=@Your_View_Name

Upvotes: 0

Paul McLoughlin
Paul McLoughlin

Reputation: 2293

What version of SQL Server are you using? I only have SQL Server 2014 available to test with, but the T-SQL below works for both missing and not missing views. I wonder whether the fact that you are checking for existence of the view in otherdb.sys.views but are not qualifying otherdb when selecting from the view is to blame?

declare @viewName varchar(50) = 'MissingView';
declare @sqlCommand nvarchar(1000);
declare @otherdb varchar(20) = 'MyTestDatabase';

set @sqlCommand = N'if exists
    (
        select 1
        from ' + @otherdb + '.sys.views as v
        where v.name = ''' + @viewName + '''
    )
    begin
        select * from ' + @otherdb + '.dbo.' + @viewName + ';
    end
    else 
    begin
        select ''Nah mate - missing view'';
    end';


print @sqlCommand;

execute sp_executesql @sqlCommand;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Check for the existence of the view outside the dynamic SQL. You are trying to prevent the compile-time error of the view not existing in the select. There is no issue with the if:

IF EXISTS(SELECT 1 FROM sys.views WHERE name = @viewName)
BEGIN
    SET @sqlCommand = 'SELECT * FROM ' + @viewName
    EXEC(@sqlCommand)
END;

Although it doesn't make a difference in this case, if you are using dynamic SQL, learn about sp_executesql -- it is more powerful than exec() because you can pass variables in and out.

EDIT:

In that case, you essentially have to do dynamic SQL inside dynamic SQL. The following is not tested, so there could be a syntax error:

DECLARE @viewName varchar(1000);
DECLARE @otherDB varchar(1000);

declare @sql nvarchar(max) = '
IF EXISTS (SELECT 1 FROM @otherDB.sys.views WHERE name = ''@viewName'')
BEGIN
    DECLARE @sqlCommand nvarchar(max);
    SET @sqlCommand = ''SELECT * FROM @viewName'';
    EXEC(@sqlCommand);
END;';

SET @sql = replace(replace(@ql, '@otherDB', @otherDB), '@viewName', @viewName);

EXEC(@sql);

Upvotes: 2

HaveNoDisplayName
HaveNoDisplayName

Reputation: 8497

You can use the Else condition when not exists to set error message

DECLARE @sqlCommand varchar(1000)
DECLARE @viewName varchar(1000)
SET @viewName = 'vwName'
SET @sqlCommand = 'IF EXISTS(SELECT 1 FROM sys.views WHERE name=''' + @viewName + ''')
BEGIN
SELECT * FROM ' + @viewName + '
END
ELSE
BEGIN
SELECT ''View not exists''
END
'    
EXEC (@sqlCommand)

Upvotes: 0

Related Questions