Insert Exec & Combine Columns

Question

Insert Exec & Combine Columns

sxg6023

SSCrazy

Points: 2918
More actions
October 14, 2003 at 4:00 pm

#82802

Is there any way to combine columns like the Union clause does with the rows?
More precisely, If I run dbcc showfilestats I get this,
Fileid FileGroup TotalExtents UsedExtents Name
----------- ----------- ------------ ----------- ---------------
1 1 1743877 1741944 Sales_Data
3 1 64000 62971 Sales_1_Data
5 1 352000 340970 Sales_2_Data
7 1 387200 373702 Sales_3_Data
I would like to get,
DBFileid FileGroup TotalExtents UsedExtents Name
------------------ ----------- ------------ ----------- ---------------
Sales1 1 1743877 1741944 Sales_Data
Sales3 1 64000 62971 Sales_1_Data
Sales5 1 352000 340970 Sales_2_Data
Sales7 1 387200 373702 Sales_3_Data
I can 'insert exec' into a temp table with the structure matching first display shown above,
and then transfer to table structure matching second display with additional columns.
But it is clunky.
BTW, the table variables do not seem to work with 'insert exec', so I had to use temp tables.
Any better Ideas?
Thanks.

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

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

philcart SSC-Forever Points: 47796 More actions · Answer 1

Try this,



SELECT db_name(), fs.*

FROM OPENQUERY(<your server>, 'SET FMTONLY OFF EXEC(''DBCC SHOWFILESTATS'')') as fs

Hope this helps

Phill Carter

--------------------

Colt 45 - the original point and click interface

--------------------
Colt 45 - the original point and click interface

sxg6023 SSCrazy Points: 2918 More actions · Answer 2

Phil:

Thanks! It shows a new way I had never used. I still have a problem with switching the database context within procedure.

If I do dynamic exec (ie exec ('use '+ @dbname) and then if I do db_name() it still returns the static database name. The dynamic context is set only for the dynamic query duration. In the past I executed the database switch before each query execution. Is there any workaround to keep the database switch persist longer, say for a while loop etc,.?

Thanks.

Edited by - sxg6023 on 10/14/2003 8:41:14 PM