Need all databases, tables and indexes in T-sql

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Need all databases, tables and indexes in...

Need all databases, tables and indexes in T-sql

Rate Topic

Display Mode

Topic Options

Author

Message

Cees Cappelle-442904

Posted Tuesday, August 05, 2008 3:50 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 3:07 AM
Points: 39, Visits: 281

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

Post #546600

ChiragNS

Posted Tuesday, August 05, 2008 4:15 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 5:02 AM
Points: 2,365, Visits: 1,825

Hi

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

"Keep Trying"

Post #546607

Cees Cappelle-442904

Posted Tuesday, August 05, 2008 5:06 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 3:07 AM
Points: 39, Visits: 281

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

Post #546628

ChiragNS

Posted Friday, August 08, 2008 6:13 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 5:02 AM
Points: 2,365, Visits: 1,825

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"

Post #549042

Abhijit More

Posted Friday, August 08, 2008 6:20 AM

SSC Eights!

Group: General Forum Members
Last Login: Thursday, May 16, 2013 8:06 AM
Points: 900, Visits: 654

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

Post #549049

Cees Cappelle-442904

Posted Friday, August 08, 2008 6:31 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 3:07 AM
Points: 39, Visits: 281

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

Post #549065

ChiragNS

Posted Monday, August 11, 2008 12:52 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 5:02 AM
Points: 2,365, Visits: 1,825

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"

Post #550028

« Prev Topic | Next Topic »

Permissions