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
([_bigint] IS NOT NULL AND [id]>(5)).
sys.index_columns can be viewed to tell us both the
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
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.