• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 475
  • Last Modified:

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
0
dastaub
Asked:
dastaub
4 Solutions
 
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

0
 
AnujCommented:
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
0
 
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

0
 
dastaubAuthor Commented:
can the database_id be converted to its database name?
0
 
Anthony PerkinsCommented:
Look up the system function DB_NAME()
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now