SQL 2005 schema table and sys.dm_index_physical_stats

The below 2 queries give in combination what I would like to have in 1 query.  The first query includes table names and row counts.  The 2nd query includes index names and their % of fragmentation.  I would like one query that supplies me with
All Table Names, Row Counts, Index Names, Index % Fragmentation, and Database name

SELECT '[' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + ']' AS fulltable_name, SCHEMA_NAME(t.schema_id) AS schema_name, t.name AS table_name,
i.rows
FROM sys.tables AS t INNER JOIN
 sys.sysindexes AS i ON t.object_id = i.id AND i.indid < 2
ORDER BY rows DESC
 
 
 SELECT ps.database_id, ps.OBJECT_ID,
 ps.index_id, b.name,
 ps.avg_fragmentation_in_percent
 FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
 INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
 AND ps.index_id = b.index_id
 WHERE ps.database_id = DB_ID()
 ORDER BY ps.OBJECT_ID
dastaubAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

appariCommented:
try this
;with A as (SELECT i.indid,t.object_id,'[' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + ']' AS fulltable_name, 
SCHEMA_NAME(t.schema_id) AS schema_name, t.name AS table_name, i.rows 
FROM sys.tables AS t INNER JOIN  sys.sysindexes AS i 
ON t.object_id = i.id AND i.indid < 2 ),
B as ( SELECT ps.database_id, ps.OBJECT_ID,
 ps.index_id, b.name,
 ps.avg_fragmentation_in_percent
 FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) 
AS ps  INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID  
AND ps.index_id = b.index_id  
WHERE ps.database_id = DB_ID()  )
Select * from A left outer join B
on A.object_id = b.Object_id
ORDER BY A.OBJECT_ID

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AnujSQL Server DBACommented:
SELECT S.OBJECT_ID, QUOTENAME(OBJECT_SCHEMA_NAME(i.id) )+'.'+QUOTENAME(OBJECT_NAME(i.ID)) FullTableName ,OBJECT_SCHEMA_NAME(i.ID) SchemaName,
OBJECT_NAME(S.OBJECT_ID) ObjectName, ROWS, database_id, s.index_id, i.NAME,avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,'LIMITED') S
INNER JOIN sysindexes I ON s.object_id = i.ID
AND s.index_id = i.indid
sachinpatil10dCommented:
Check this
select '[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']' fullTableName, TABLE_SCHEMA [schema_name], table_name, 
I.rows, I.name ,database_id,object_id,index_id, avg_fragmentation_in_percent
from INFORMATION_SCHEMA.TABLES T
Inner Join sys.sysindexes I on OBJECT_ID(table_name) = id AND indid < 2
inner join sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) Stat on I.id = Stat.object_id

Open in new window

dastaubAuthor Commented:
can the database_id be converted to its database name?
Anthony PerkinsCommented:
Look up the system function DB_NAME()
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.