April 30, 2013 at 8:28 am
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
April 30, 2013 at 8:50 am
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/
April 30, 2013 at 8:51 am
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
April 30, 2013 at 10:03 am
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) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply