在尝试删除集之前测试它是否存在 [英] Test if a set exists before trying to drop it

问题描述

在SQL中，在删除表之前，我将首先检查它是否存在，以免引起错误，例如:

In SQL before dropping a table I will check if it exists first so as not to cause an error, like so:

IF OBJECT_ID('TEMPDB..#table') IS NOT NULL 
  BEGIN 
      DROP TABLE #table 
  END

在MDX中，我可以采用一种方法来检查set或member在尝试删除之前是否已经存在.

In MDX I could do with a way of checking if a set or member already exists before trying to drop it.

目前，我的某些.mdx文件包含以下结构.我将自定义集添加到多维数据集，然后在随后的多个脚本中使用该集，即它们是多批脚本.

Currently I have the following stucture within some of my .mdx files. I add a custom set to the cube and then use that set in several scripts that follow i.e. they are mult-batch scripts.

/*
//I run the following manually...
DROP SET [XCube].[xSet]
*/

CREATE 
    SET [XCube].[xSet] AS
        blah blah

1.
SELECT x FROM XCube USING variousconditions AND xSet
GO

2.
SELECT y FROM XCube USING variousconditions AND ySet
GO

3.
SELECT y FROM XCube USING variousconditions AND ySet
GO

将以上上下文放到一边，我的问题很简单:

Putting the above context to one side my question is quite simple:

如何测试xSet是否存在，以便仅在需要时才能执行DROP SET [XCube].[xSet]?

How do I test if xSet exists so that I can execute DROP SET [XCube].[xSet] only if required?

推荐答案

我可以为您提供部分答案:您可以在

I can give you a partial answer: You can find information about the objects of your cubes in schema rowsets, which are similar to the INFORMATION_SCHEMA views in relational databases. From SQL Server 2008 onwards, you can access these with a limited subset of SQL (no joins, just simple column_name = 'value' conditions in the where clause, ...) like this:

select * 
  from $SYSTEM.MDSCHEMA_SETS
 where SET_NAME = 'xSet'
   and CUBE_NAME = 'XCube'

Analysis Services会自动检测查询是SQL还是MDX(或从SQL2012，DAX).

Analysis Services automatically detects if the query is SQL or MDX (or, from SQL2012, DAX).

如果该集存在，这将给您一个记录，如果不存在，则不给您任何记录.但是我不知道MDX中有一种机制可以根据结果有条件地执行代码.

This would give you one record back if the set exists, and none if it does not exist. But I am not aware of a mechanism within MDX to execute code conditionally based on the result.

这篇关于在尝试删除集之前测试它是否存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！