August 5, 2008 at 3:50 am
This is related to an article on mssql tips: http://www.mssqltips.com/tip.asp?tip=1545
I have a number of databases with the same structure and indexes. Is there a way I can run the following query on all databases? I think I need to join to master..sysdatabases where databasename like 'McBam%' , but how do I do that?
SELECT DB_NAME() AS DATABASENAME,
OBJECT_NAME(B.OBJECT_ID) AS TABLENAME,
B.NAME AS INDEXNAME,
B.INDEX_ID
FROM SYS.OBJECTS A
INNER JOIN SYS.INDEXES B
ON A.OBJECT_ID = B.OBJECT_ID
WHERE NOT EXISTS (SELECT *
FROM SYS.DM_DB_INDEX_USAGE_STATS C
WHERE B.OBJECT_ID = C.OBJECT_ID
AND B.INDEX_ID = C.INDEX_ID)
AND A.TYPE <> 'S'
AND B.NAME > ''
AND B.NAME not like '%rowid%'
ORDER BY 1, 2, 3
I have this, but it takes a looong time to run:
DECLARE @Database TABLE(Naam VarChar(20))
DECLARE@IndexNaamTABLE(
DatabaseNameVarChar(Max)
,TableNameVarChar(Max)
,IndexNameVarChar(Max)
,IndexIdInt
)
DECLARE@TableCountInt
DECLARE@SqlCmdVarChar(Max)
INSERT @DataBase(Naam)
SELECT name FROM sys.databases D
WHERE D.name like 'McBam%'
SELECT * FROM @DataBase
SELECT @TableCount = (SELECT COUNT(*) FROM @DataBase)
Print @TableCount
SET@SqlCmd = 'SELECT DB_NAME() AS DATABASENAME,
OBJECT_NAME(B.OBJECT_ID) AS TABLENAME,
B.NAME AS INDEXNAME,
B.INDEX_ID
FROM SYS.OBJECTS A
INNER JOIN SYS.INDEXES B
ON A.OBJECT_ID = B.OBJECT_ID
WHERE NOT EXISTS (SELECT *
FROM SYS.DM_DB_INDEX_USAGE_STATS C
WHERE B.OBJECT_ID = C.OBJECT_ID
AND B.INDEX_ID = C.INDEX_ID)
AND A.TYPE <> '''+'S'+'''
AND B.NAME > ''''
AND B.NAME not like '''+'%rowid%'+'''
ORDER BY 1, 2, 3 '
INSERT @IndexNaam (DatabaseName, TableName, IndexName, IndexId)
EXEC sp_MSforeachdb @SqlCmd
August 5, 2008 at 4:15 am
Hi
U can use sys.databases by joining it with SYS.DM_DB_INDEX_USAGE_STATS on database_id column.
"Keep Trying"
August 5, 2008 at 5:06 am
Sorry, don't quite know how :hehe:
Here's something that sort of does what I want:
DECLARE @Database TABLE(Naam VarChar(20))
DECLARE@IndexNaamTABLE(
DatabaseNameVarChar(Max)
,TableNameVarChar(Max)
,IndexNameVarChar(Max)
,IndexIdInt
)
DECLARE@TableCountInt
DECLARE@SqlCmdnVarChar(Max)
INSERT @DataBase(Naam)
SELECT name FROM sys.databases D
WHERE D.name like 'McBam%'
SELECT * FROM @DataBase
SELECT @TableCount = (SELECT COUNT(*) FROM @DataBase)
Print @TableCount
SET@SqlCmd = 'USE ?;SELECT DB_NAME() AS DATABASENAME,
OBJECT_NAME(B.OBJECT_ID) AS TABLENAME,
B.NAME AS INDEXNAME,
B.INDEX_ID
FROM SYS.OBJECTS A
INNER JOIN SYS.INDEXES B
ON A.OBJECT_ID = B.OBJECT_ID
WHERE NOT EXISTS (SELECT *
FROM SYS.DM_DB_INDEX_USAGE_STATS C
WHERE B.OBJECT_ID = C.OBJECT_ID
AND B.INDEX_ID = C.INDEX_ID)
AND A.TYPE <> '''+'S'+'''
AND B.NAME > ''''
AND B.NAME not like '''+'%rowid%'+'''
ORDER BY 1, 2, 3 '
INSERT @IndexNaam (DatabaseName, TableName, IndexName, IndexId)
EXEC sp_MSforeachdb @SqlCmd
SELECT TableName, IndexName, Count(*) as Aantal FROM @DataBase
Inner Join @IndexNaam on DatabaseName = Naam
Group by TableName, IndexName
Having Count(*) = @TableCount
Order By TableName, IndexName
But it puts ALL unused indexes in my Variable table because it questions ALL databases.
Can I restrict the use of sp_MSforeachdb to the tables called 'McBam%' ??
Thanks for any insight and/or tips.
Cees
August 8, 2008 at 6:13 am
Hi
In the @Database table variable store database id also.
Then in you select query change this part
"FROM SYS.DM_DB_INDEX_USAGE_STATS C
WHERE B.OBJECT_ID = C.OBJECT_ID
AND B.INDEX_ID = C.INDEX_ID
INNER JOIN @Database D ON C.database_id = D.database_id
".
Try it out and let me know....
"Keep Trying"
August 8, 2008 at 6:20 am
SELECTDB_NAME() AS DATABASENAME,
OBJECT_NAME(B.OBJECT_ID) AS TABLENAME,
B.NAME AS INDEXNAME,
B.INDEX_ID
FROMSYS.OBJECTS A
INNER JOIN SYS.INDEXES B ON A.OBJECT_ID = B.OBJECT_ID
WHERENOT EXISTS (SELECT *
FROM SYS.DM_DB_INDEX_USAGE_STATS C
INNER JOIN master.sys.databases DB ON C.database_id = DB.database_id
WHERE B.OBJECT_ID = C.OBJECT_ID AND DB.name LIKE '%'
AND B.INDEX_ID = C.INDEX_ID
) AND A.TYPE <> 'S'
AND B.NAME > ''
AND B.NAME not like '%rowid%'
ORDER BY 1, 2, 3
Abhijit - http://abhijitmore.wordpress.com
August 8, 2008 at 6:31 am
Abhijit, your solution gives me only unused indexes in the current database. I'm looking for a solution to find all unused indexes in a number of databases, which have all the same structure, but are named differently.
Chirag, your solution gives me errors at the Inner join
DECLARE @Database TABLE(Naam VarChar(20))
DECLARE @IndexNaam TABLE(
DatabaseName VarChar(Max)
,Database_IdInt
, TableName VarChar(Max)
, IndexName VarChar(Max)
, IndexId Int
)
DECLARE @TableCount Int
DECLARE @SqlCmd nVarChar(Max)
INSERT @DataBase(Naam)
SELECT name FROM sys.databases D
WHERE D.name like 'McBam%'
SELECT * FROM @DataBase
SELECT @TableCount = (SELECT COUNT(*) FROM @DataBase)
Print @TableCount
SET @SqlCmd = 'USE ?;SELECT DB_NAME() AS DATABASENAME,
OBJECT_NAME(B.OBJECT_ID) AS TABLENAME,
B.NAME AS INDEXNAME,
B.INDEX_ID
FROM SYS.DM_DB_INDEX_USAGE_STATS C
WHERE B.OBJECT_ID = C.OBJECT_ID
AND B.INDEX_ID = C.INDEX_ID
INNER JOIN @Database D ON C.database_id = D.database_id
WHERE NOT EXISTS (SELECT *
FROM SYS.DM_DB_INDEX_USAGE_STATS C
WHERE B.OBJECT_ID = C.OBJECT_ID
AND B.INDEX_ID = C.INDEX_ID)
AND A.TYPE <> '''+'S'+'''
AND B.NAME > ''''
AND B.NAME not like '''+'%rowid%'+'''
ORDER BY 1, 2, 3 '
INSERT @IndexNaam (DatabaseName, TableName, IndexName, IndexId)
EXEC sp_MSforeachdb @SqlCmd
SELECT TableName, IndexName, Count(*) as Aantal FROM @DataBase
Inner Join @IndexNaam on DatabaseName = Naam
Group by TableName, IndexName
Having Count(*) = @TableCount
Order By TableName, IndexName
So, I guess it boils down to: How do I restrict the sp_MSforeachdb to only the databases I want?
Cees
August 11, 2008 at 12:52 am
SET @SqlCmd = 'USE ?;SELECT DB_NAME() AS DATABASENAME,
OBJECT_NAME(B.OBJECT_ID) AS TABLENAME,
B.NAME AS INDEXNAME,
B.INDEX_ID
FROM SYS.DM_DB_INDEX_USAGE_STATS C
WHERE B.OBJECT_ID = C.OBJECT_ID
AND B.INDEX_ID = C.INDEX_ID
INNER JOIN @Database D ON C.database_id = D.database_id
WHERE NOT EXISTS (SELECT *
FROM SYS.DM_DB_INDEX_USAGE_STATS C
WHERE B.OBJECT_ID = C.OBJECT_ID
AND B.INDEX_ID = C.INDEX_ID)
-- new condition.
AND DB_NAME() LIKE 'MCBAM%'
AND A.TYPE <> '''+'S'+'''
AND B.NAME > ''''
AND B.NAME not like '''+'%rowid%'+'''
ORDER BY 1, 2, 3 '
When u execute the query for each database using sp_msforechdb, this query will execute in each db but
will not return data for databases whose name is NOT like MCBam.
"Keep Trying"
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply