Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Table Information View -- No Cursors! Expand / Collapse
Author
Message
Posted Monday, March 17, 2008 1:24 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 9:14 AM
Points: 9,294, Visits: 9,483
Comments posted to this topic are about the item Table Information View -- No Cursors!

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #470113
Posted Tuesday, April 8, 2008 8:38 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 10:58 AM
Points: 5,333, Visits: 25,272
Thanks for your unique point of view - it works like a charm ... will be added to my bag of "tricks"

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #481595
Posted Tuesday, April 8, 2008 11:20 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 2:59 PM
Points: 11, Visits: 352
This is what I use, however it would need to be modified to insert into a table if you wanted to select against it.

sp_msforeachdb 'use ?;exec sp_spaceused'

cheers,
Andrew



Post #481750
Posted Tuesday, April 8, 2008 4:30 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 9:14 AM
Points: 9,294, Visits: 9,483
bitbucket (4/8/2008)
Thanks for your unique point of view - it works like a charm ... will be added to my bag of "tricks"

Thanks for the feedback Bit.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #481927
Posted Tuesday, April 8, 2008 4:33 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 9:14 AM
Points: 9,294, Visits: 9,483
wavesmash (4/8/2008)
This is what I use, however it would need to be modified to insert into a table if you wanted to select against it.

sp_msforeachdb 'use ?;exec sp_spaceused'

cheers,
Andrew

That's how I used to do it too Andrew, and that is actually the reason why I wrote this View. Besides being undocumented, sp_msForEachDB encapsulates a cursor to accomplish its magic, which carries along with it all of the limitations and problems of a cursor.

I wanted something that could be used anywhere in SQL Server and that means a View. So I did essentially what you suggest: I looked at how sp_spaceused works and then extended it to a Select in order to build the View.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #481928
Posted Tuesday, August 12, 2008 4:57 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:53 AM
Points: 23, Visits: 119
I always like a query that gets down and dirty with system tables ;)! Nice. But I'm not sure occasionally cursoring through a few hundred tables is really that much of a chore for SQL Server! It's the counting that takes the time! Still, it's always nice to complete these challenges we set ourselves!
Post #550860
Posted Monday, September 8, 2008 1:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 8, 2008 1:07 AM
Points: 1, Visits: 0
:P
hello how r u dudes
i want to know abt the normalisations
Post #565238
Posted Tuesday, November 25, 2008 8:31 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:11 AM
Points: 10,282, Visits: 13,264
Nice script Barry. Is there one that will work on 2000?



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #608811
Posted Tuesday, November 25, 2008 9:42 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 9:14 AM
Points: 9,294, Visits: 9,483
I believe that this can be made to work on SQL 2000, Jack. But I would need some time on one of the few SQL 2000 systems that I still have access to, to work it out...

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #608832
Posted Friday, February 6, 2009 6:42 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 9:05 AM
Points: 644, Visits: 2,141
bitbucket (4/8/2008)
Thanks for your unique point of view - it works like a charm ... will be added to my bag of "tricks"

Very nice, I too will add it to my toolkit. Especially love getting the schema details. I tried writing one to work with AdventureWorks which has multiple schemas, and got one to work, but no where near as elegant as your script.


Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein
Post #651581
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse