4

Given a table of the form...

create table dbo.FilterIDXTest ( 
    id int not null identity primary key clustered,
    _bigint bigint,
    _varchar10 varchar(10),
    _varchar20 varchar(20),
    _guid uniqueidentifier
);

go

create unique index uq_FilterIDXTest
    on dbo.FilterIDXTest ( _varchar10, _varchar20 ) 
    include ( _guid )
    where _bigint is not null 
        and id > 5;

go

... in what (if any) catalog view (or other well-formed reference object) can the columns names in in the WHERE clause be located and rigorously identified?

sys.indexes can be viewed to remind us that the filter_definition is ([_bigint] IS NOT NULL AND [id]>(5)).

sys.index_columns can be viewed to tell us both the ON and INCLUDE columns but lists only 3 of the 5 columns in the original CREATE INDEX command. The filter_predicate columns are not found here.

I must assume that the filter_predicate dependencies are rigorously checked at some stage because sp_rename 'dbo.FilterIDXTest._bigint', 'foo_bigint', 'column' fails with the the error...

Caution: Changing any part of an object name could break scripts and stored procedures.
Msg 5074, Level 16, State 1, Procedure sp_rename, Line 611
The index 'uq_FilterIDXTest' is dependent on column '_bigint'.
Msg 4922, Level 16, State 9, Procedure sp_rename, Line 611
RENAME COLUMN _bigint failed because one or more objects access this column.

...and I can't bring myself to believe that the only reason this fails is because of some text matching at sys.indexes.filter_definition.


For reference, the task at hand is to extract the source code from a number of undocumented legacy servers. My request to use RedGate/some other paid helper app for this purpose has already been rejected since "You can just script out the objects from the UI, right?". Perhaps I'm been overly rigorous, but I'd like to have a very high level of confidence that the index I'm checking in to source control is the index that actually exists. I'm testing this script on sql server 2012, but the target servers on which it will ultimately be executed range from 2005 SP1 to 2008 R2 SP3.

5

I think this information is stored in sys.sql_expression_dependencies. See the table in MSDN, sys.sql_expression_dependencies:

THIS TOPIC APPLIES TO:
yes: SQL Server (starting with 2008)
no: Azure SQL Database
yes: Azure SQL Data Warehouse
yes: Parallel Data Warehouse

Remarks

The following table lists the types of entities for which dependency information is created and maintained. Dependency information is not created or maintained for rules, defaults, temporary tables, temporary stored procedures, or system objects.

Entity type          Referencing entity    Referenced entity  
Table                Yes*                  Yes  
View                 Yes                   Yes  
Filtered index       Yes**                 No  
Filtered statistics  Yes**                 No  
...

** Each column used in the filter predicate is tracked as a referencing entity.

Also note that filtered indexes were added as a feature in version 2008 and this system view does not exist in 2005 (thnx to @Michael Eriksson for noting this).

  • The quoted part of the manual is confusing. After creating the index and looking it up in sys.sql_expression_dependencies I could see the columns referenced in WHERE as referenced objects (so that (referenced_id, referenced_minor_id) pointed to (object_id, column_id) in sys.columns`). – Andriy M Sep 23 '16 at 9:45
  • @AndriyM thnx it seems you are right (but I can't test right now) and the first paragraph in the page, has the correct info ("Contains one row for each by-name dependency on a user-defined entity in the current database. This includes ... A dependency between two entities is created when one entity, called the referenced entity, appears by name in a persisted SQL expression of another entity, called the referencing entity. For example, when a table is referenced in the definition of a view, the view, as the referencing entity, depends on the table, the referenced entity. ...") – ypercubeᵀᴹ Sep 23 '16 at 9:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.