Formula in Excel?

Question

Post reply

Formula in Excel?

sunny.tjk

SSCertifiable

Points: 5041
More actions
August 23, 2011 at 7:39 am

#246627

I'm trying to enter rowcount, data size and index size as three fields in an excel sheet for all the tables in a database. Does anyone know how to write a formula or a query in an excel sheet to implement this?
Thanks.

Viewing 10 posts - 1 through 9 (of 9 total)

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

homebrew01 SSC Guru Points: 55548 More actions · Answer 1

You could write a stored procedure, then have Excel run the sp to load the data, or export from SQL into Excel.

sunny.tjk SSCertifiable Points: 5041 More actions · Answer 2

Hmmm.. I'm not sure if I could do that since I just have read permissions to the database.

homebrew01 SSC Guru Points: 55548 More actions · Answer 3

Then you could embed the select statement in Excel and run that instead of the sp.

It's been a while, but I think there's some Excel add-in you need.

sunny.tjk SSCertifiable Points: 5041 More actions · Answer 4

I believe I could use sp_spaceused but I'm not sure how to put the results in 3 different columns.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

The best way, IMHO, is to write a view and create and "External Data Source" on the spreadsheet.

--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)

sunny.tjk SSCertifiable Points: 5041 More actions · Answer 6

My read permissions won't let me do that.

Would it be possible to write a query to push the results of sp_spaceused into a temp table? If yes, can someone please give an example.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

sunny.tjk (8/23/2011)
My read permissions won't let me do that.

Why not write a view and submit it to the folks asking you to do the job so they can "promote it to production?"

And, I'm really confused... if you only have "read" permissions, why are you seeking what is typically considered to be data only an administrator would need?

--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)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 8

Here's a query I wrote a long time ago that makes sp_SpaceUsed look pretty lame...

http://www.sqlservercentral.com/Forums/FindPost730149.aspx

--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)

sunny.tjk SSCertifiable Points: 5041 More actions · Answer 9

Jeff Moden (8/23/2011)
sunny.tjk (8/23/2011)
My read permissions won't let me do that.
Why not write a view and submit it to the folks asking you to do the job so they can "promote it to production?"
And, I'm really confused... if you only have "read" permissions, why are you seeking what is typically considered to be data only an administrator would need?

I can only say that my client's environment is very confusing. They hired me to help the production DBAs in migration.