Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need all databases, tables and indexes in T-sql Expand / Collapse
Author
Message
Posted Tuesday, August 5, 2008 3:50 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 6:44 AM
Points: 49, Visits: 370
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
Posted Tuesday, August 5, 2008 4:15 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 4:53 AM
Points: 2,366, Visits: 1,844
Hi

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


"Keep Trying"
Post #546607
Posted Tuesday, August 5, 2008 5:06 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 6:44 AM
Points: 49, Visits: 370
Sorry, don't quite know how
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
Posted Friday, August 8, 2008 6:13 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 4:53 AM
Points: 2,366, Visits: 1,844
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
Posted Friday, August 8, 2008 6:20 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, June 23, 2014 6:03 AM
Points: 920, Visits: 702
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
Posted Friday, August 8, 2008 6:31 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 6:44 AM
Points: 49, Visits: 370
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
Posted Monday, August 11, 2008 12:52 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 4:53 AM
Points: 2,366, Visits: 1,844
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 »

Add to briefcase

Permissions Expand / Collapse