Temp table and table variable

Question

Temp table and table variable

mtz676

SSCrazy Eights

Points: 9212
More actions
April 20, 2020 at 9:04 am

#3744206

DECLARE @command varchar(1000)
SELECT @command = 'USE ? SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.alloc_unit_type_desc,
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc'
EXEC sp_MSforeachdb @command
When I run the above command I get sql results in different windows one for each db.How do get all the sql rows of all databases in a single window using temp table and table variable

Thanks

SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.alloc_unit_type_desc,
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Create a temp table and insert the results of the EXEC statement into the temp table, then select from that table at the end.

John

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 2

I know it seems like a strange question because the answer may seem obvious but why are you doing this? If the answer is to do supposed "Best Practice" index maintenance, then don't waste your time rolling your own. Look for Ola Hallengren's index maintenance. A lot of people have rated it as the "Gold Standard" for doing the "Best Practice" index maintenance as "Best Practice" is currently defined. Here's the link to his index and statistics maintenance code...

https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

If you're doing this to more deeply analyze your indexes, that's a different story. Let us know.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)