Checking for index in another database

Question

Checking for index in another database

mw_sql_developer

SSCoach

Points: 19445
More actions
April 30, 2013 at 8:28 am

#272009

Folks:
How can i check for the presence of an index in another database ?
Lets say I am in DATABASE A and i want to check for an object in database B. The following syntax does not work. I tried it.
To make it even easier how can I check for the sys.indexes view in
another database ?
USE DATAEXCH;
GO
IF EXISTS (
SELECT name FROM sys.indexes WHERE
name = N'inx_patient_encounter1'
AND
object_id = OBJECT_ID(N'NGPROD.dbo.patient_encounter')
)
Begin
Print 'Index Found'
End
else
Begin
Print 'Index Not Found'
End

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

mw112009 (4/30/2013)
Folks:
How can i check for the presence of an index in another database ?
Lets say I am in DATABASE A and i want to check for an object in database B. The following syntax does not work. I tried it.
To make it even easier how can I check for the sys.indexes view in
another database ?
USE DATAEXCH;
GO
IF EXISTS (
SELECT name FROM sys.indexes WHERE
name = N'inx_patient_encounter1'
AND
object_id = OBJECT_ID(N'NGPROD.dbo.patient_encounter')
)
Begin
Print 'Index Found'
End
else
Begin
Print 'Index Not Found'
End

Add the database to your object qualifier.

IF EXISTS (

SELECT name FROM NGPROD.sys.indexes WHERE

name = N'inx_patient_encounter1'

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

IF EXISTS (SELECT 1 FROM OtherDatabase.sys.tables t inner join OtherDatabase.sys.indexes i on t.object_id = i.object_id WHERE i.name = 'IndexOfInterest' AND t.name = 'TableOfInterest')

PRINT 'Index Exists';

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ScottPletcher SSC Guru Points: 101099 More actions · Answer 3

That could find the wrong entry and/or miss the entry if it was an index on a view instead of a table. Therefore, the code below is more robust:

IF EXISTS(

SELECT 1

FROM NGPROD.sys.objects o

WHERE

o.schema_id = (SELECT s.schema_id FROM NGPROD.sys.schemas s WHERE s.name = N'dbo') AND

o.name = N'patient_encounter' AND

EXISTS(

SELECT 1

FROM NGPROD.sys.indexes i

WHERE

i.object_id = o.object_id AND

i.name = N'inx_patient_encounter1'

)

BEGIN

PRINT 'Index Found'

END --IF

ELSE

BEGIN

PRINT 'Index Not Found'

END --ELSE

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".