Table Information View -- No Cursors!

Question

Table Information View -- No Cursors!

Viewing 8 posts - 16 through 22 (of 22 total)

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

AFAIK, "~TMPCLPnnnnnn" is the name of an MS-ACCESS temporary table. I have no idea what one would be doing in a SQL Server database.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

Manie Verster (4/9/2009)
I would like to take your query further and add dependencies to it because I see a few tables that looks like they might have been created as a temporary table and never deleted and if I know the dependencies e.g. sp's, views etc then I can maybe (very carefully) delete them. I'd appreciate some help on this.

Dependencies are tricky because the dependencies tracker & tables in SQL 2000 and 2005 are NOT reliable. I usually take a brute force approach to this: I script out the whole database to one big file/query window and then I search for the Table Name.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Charles Kincaid SSChampion Points: 13593 More actions · Answer 3

Barry: Nice script. I'm adding it to my cache as well.

ATBCharles Kincaid

Charles Kincaid SSChampion Points: 13593 More actions · Answer 4

Bruce, nice to see somebody using a CTE. wavesmash asked about selecting against Barry's script. I would wrap it in a CTE.

ATBCharles Kincaid

Glen Cooper SSCommitted Points: 1917 More actions · Answer 5

Glen Cooper

SSCommitted

Points: 1917

June 29, 2009 at 3:55 pm

#1017275

Nice script Barry.

R Glen Cooper

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

Thanks Glenn.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

fercasas Old Hand Points: 307 More actions · Answer 7

Very good script ... I will realize small modifications only to customize it. I ask your permission to publish the amended version on a small blog that I'm building ... I can do it? 🙂

Fernando Casas Osorio

Greg Drake Ten Centuries Points: 1071 More actions · Answer 8

Here's a link to a similar query I published in June.

http://www.sqlservercentral.com/scripts/99716/[/url]

For contrast, that one was attempting to stay as close to the original sp_spaceused results as possible, whereas this new one throws in some extra metadata. That's to say, Barry's version is more customized instead of trying to be super-exact.

My main recommendation would be to put an ORDER BY clause in there. I also notice that this script won't show some user objects that take up space, like an indexed view.

Thanks for sharing!