SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need all databases, tables and indexes in T-sql


Need all databases, tables and indexes in T-sql

Author
Message
Cees Cappelle-442904
Cees Cappelle-442904
SSC Veteran
SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)

Group: General Forum Members
Points: 245 Visits: 372
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 @IndexNaam TABLE(
DatabaseName VarChar(Max)
, TableName VarChar(Max)
, IndexName VarChar(Max)
, IndexId Int
)
DECLARE @TableCount Int
DECLARE @SqlCmd VarChar(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


ChiragNS
ChiragNS
SSCertifiable
SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)

Group: General Forum Members
Points: 6371 Visits: 1865
Hi

U can use sys.databases by joining it with SYS.DM_DB_INDEX_USAGE_STATS on database_id column.

"Keep Trying"
Cees Cappelle-442904
Cees Cappelle-442904
SSC Veteran
SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)

Group: General Forum Members
Points: 245 Visits: 372
Sorry, don't quite know how Hehe
Here's something that sort of does what I want:
DECLARE @Database TABLE(Naam VarChar(20))
DECLARE @IndexNaam TABLE(
DatabaseName VarChar(Max)
, 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.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
ChiragNS
ChiragNS
SSCertifiable
SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)

Group: General Forum Members
Points: 6371 Visits: 1865
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"
Abhijit More
Abhijit More
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1887 Visits: 767
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
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
Cees Cappelle-442904
Cees Cappelle-442904
SSC Veteran
SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)

Group: General Forum Members
Points: 245 Visits: 372
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_Id Int
, 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
ChiragNS
ChiragNS
SSCertifiable
SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)

Group: General Forum Members
Points: 6371 Visits: 1865
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"
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search