I have some queries that run against Query Store, the view sys.query_store_wait_stats
is not in SQL Server 2016, but it is in SQL Server 2017. I want my queries to work in SQL Server 2016 and SQL Server 2017+.
To accomplish this I used IF EXISTS
as below
EXEC sp_query_store_flush_db;
SELECT * INTO Admin.dbo.query_store_runtime_stats FROM sys.query_store_runtime_stats;
SELECT * INTO Admin.dbo.query_store_runtime_stats_interval FROM sys.query_store_runtime_stats_interval;
SELECT * INTO Admin.dbo.query_store_plan FROM sys.query_store_plan;
SELECT * INTO Admin.dbo.query_store_query FROM sys.query_store_query;
SELECT * INTO Admin.dbo.query_store_query_text FROM sys.query_store_query_text;
IF EXISTS(select * FROM sys.views where name = 'query_store_wait_stats') -- View not in SQL Server 2016
Begin
SELECT * INTO Admin.dbo.query_store_wait_stats FROM sys.query_store_wait_stats;
End
SELECT * INTO Admin.dbo.query_context_settings FROM sys.query_context_settings;
Which would seem like a good solution, EXCEPT that, the below does not return any results on SQL Server 2017 database with Query Store active
select * FROM sys.views where name = 'sys.query_store_wait_stats'
Microsoft has this answer: How do I find all views in a database?
USE <database_name>;
GO
SELECT name AS view_name
,SCHEMA_NAME(schema_id) AS schema_name
,OBJECTPROPERTYEX(object_id,'IsIndexed') AS IsIndexed
,OBJECTPROPERTYEX(object_id,'IsIndexable') AS IsIndexable
,create_date
,modify_date
FROM sys.views;
But it only returns user views, NOT system views.
A couple of simple tests also do not return the system view, which is present. You can try them without a WHERE
and not get any system views.
select * FROM sys.views where name = 'sys.query_store_wait_stats'
SELECT *
-- DISTINCT NAME
FROM SYS.OBJECTS
WHERE TYPE IN ('U','V')
AND NAME= 'query_store_wait_stats'
I could test for SQL Server version, but If the view gets added to SQL Server 2016 in a Service Pack, I want my query to have only checked for the presence of the view.
How do I find (or test for) the presence of a system view?