I asked a question earlier about how to tell of a field exists. Now I'm
needing a query to tell if a particular index (index name) exists.I know I can SELECT INDEXPROPERTY.
Is this the recommended approach?
"Les Stockton" wrote:
> I asked a question earlier about how to tell of a field exists. Now I'm
> needing a query to tell if a particular index (index name) exists.
>|||Index names are not unique by themselves; the index name must be unique only
within the scope of the parent table or view.
IF EXISTS(
SELECT *
FROM sysindexes
WHERE
id = OBJECT_ID('dbo.MyTable') AND
name = 'IndexName'
)
PRINT 'exists'
ELSE
PRINT 'does not exist'
Note that indexes may also support primary key and unique constraints. You
might want to keep this in mind, depending on the reason you are checking
for existence.
Hope this helps.
Dan Guzman
SQL Server MVP
"Les Stockton" <LesStockton@.discussions.microsoft.com> wrote in message
news:104B48DE-467E-4235-B3DE-FA72D8893D4F@.microsoft.com...
>I asked a question earlier about how to tell of a field exists. Now I'm
> needing a query to tell if a particular index (index name) exists.
>|||This method can work as can the sysindexes method I suggested. In fact,
INDEXPROPERTY is probably a better method.
Hope this helps.
Dan Guzman
SQL Server MVP
"Les Stockton" <LesStockton@.discussions.microsoft.com> wrote in message
news:DCCEB372-A292-48BA-9432-7B499962B64C@.microsoft.com...
>I know I can SELECT INDEXPROPERTY.
> Is this the recommended approach?
> "Les Stockton" wrote:
>|||I tried the following from inside EnterpriseManager, but it doesn't return
anything.
SELECT INDEXPROPERTY(OBJECT_ID(TEST_MASTER.UserPreferences'),
'PK_UserPreferences', 'IndexID') AS IdxID
Before doing this, I did a right-click in the list of tables in the
database, and selected
"All Tasks" and then "Manage Indexes". I am able to list that the
UserPreferences table has an index called PK_UserPreferences, which
corresponds to the UserID field in the table.
Any ideas why this isn't working?
I go into the
"Dan Guzman" wrote:
> Index names are not unique by themselves; the index name must be unique on
ly
> within the scope of the parent table or view.
> IF EXISTS(
> SELECT *
> FROM sysindexes
> WHERE
> id = OBJECT_ID('dbo.MyTable') AND
> name = 'IndexName'
> )
> PRINT 'exists'
> ELSE
> PRINT 'does not exist'
> Note that indexes may also support primary key and unique constraints. Yo
u
> might want to keep this in mind, depending on the reason you are checking
> for existence.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Les Stockton" <LesStockton@.discussions.microsoft.com> wrote in message
> news:104B48DE-467E-4235-B3DE-FA72D8893D4F@.microsoft.com...
>
>|||You're missing a single quote (') after OBJECT_ID(
Also, make sure you are in the context of the database that contains the
UserPreferences table when you run the query. INDEXPROPERTY will return NULL
if the object id cannot be found in the current database. Use USE
<databasename> to set the context to the correct database.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"Les Stockton" <LesStockton@.discussions.microsoft.com> wrote in message
news:72470E25-FF59-439D-BBFF-10CEA09BD301@.microsoft.com...
>I tried the following from inside EnterpriseManager, but it doesn't return
> anything.
> SELECT INDEXPROPERTY(OBJECT_ID(TEST_MASTER.UserPreferences'),
> 'PK_UserPreferences', 'IndexID') AS IdxID
> Before doing this, I did a right-click in the list of tables in the
> database, and selected
> "All Tasks" and then "Manage Indexes". I am able to list that the
> UserPreferences table has an index called PK_UserPreferences, which
> corresponds to the UserID field in the table.
> Any ideas why this isn't working?
> I go into the
> "Dan Guzman" wrote:
>|||Still doesn't work. Test_Master is the name of the database. I name it
there with the table, as well as I am in the context of the database when
running this query.
It still shows nothing returned.
"Gail Erickson [MS]" wrote:
> You're missing a single quote (') after OBJECT_ID(
> Also, make sure you are in the context of the database that contains the
> UserPreferences table when you run the query. INDEXPROPERTY will return NU
LL
> if the object id cannot be found in the current database. Use USE
> <databasename> to set the context to the correct database.
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> "Les Stockton" <LesStockton@.discussions.microsoft.com> wrote in message
> news:72470E25-FF59-439D-BBFF-10CEA09BD301@.microsoft.com...
>
>|||> Still doesn't work. Test_Master is the name of the database.
If Test_Master is the database name, then the format you're using in the
OBJECT_ID clause ((TEST_MASTER.UserPreferences') is incorrect. What you have
indicates that TEST_MASTER is the object owner, not the database name. The
correct format must either be 'TEST_MASTER.OwnerName.UserPreferences' or
'TEST_MASTER..UserPreferences'. If dbo is the table owner, then use
'TEST_MASTER.dbo.UserPreferences'
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"Les Stockton" <LesStockton@.discussions.microsoft.com> wrote in message
news:08DEBC04-6886-4876-A681-C02557732ADD@.microsoft.com...
> Still doesn't work. Test_Master is the name of the database. I name it
> there with the table, as well as I am in the context of the database when
> running this query.
> It still shows nothing returned.
> "Gail Erickson [MS]" wrote:
>|||For a bit less fuss with index properties:
http://milambda.blogspot.com/2005/0...-with-kick.html
ML
http://milambda.blogspot.com/|||Hi Les
A two part name indicates the owner of an object, and then the object name.
So TEST_MASTER.UserPreferences would indicate an object called
.UserPreferences owned by a user called TEST_MASTER.
If you have no such user, you will get null.
As Gail said, you must be in the db to use indexproperty, so you can repalce
TEST_MASTER with the object owner, whether it is dbo or some other user.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Les Stockton" <LesStockton@.discussions.microsoft.com> wrote in message
news:08DEBC04-6886-4876-A681-C02557732ADD@.microsoft.com...
> Still doesn't work. Test_Master is the name of the database. I name it
> there with the table, as well as I am in the context of the database when
> running this query.
> It still shows nothing returned.
> "Gail Erickson [MS]" wrote:
>
>
" <anonymous@.discussions.microsoft.com> wrote in message