Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Table Information View -- No Cursors!


Table Information View -- No Cursors!

Author
Message
RBarryYoung
RBarryYoung
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9432 Visits: 9517
Thanks, Gaby. Yeah, schema are the missing piece in a lot of the nicer scripts and tools for SQL Server. things changed so much from 2000 to 2005 that it's hard to come up with something that works in both but still has the additional info that I typically want in 2005. So on this one I decided to focus on getting that additional information.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Bruce W Cassidy
Bruce W Cassidy
Right there with Babe
Right there with Babe (785 reputation)Right there with Babe (785 reputation)Right there with Babe (785 reputation)Right there with Babe (785 reputation)Right there with Babe (785 reputation)Right there with Babe (785 reputation)Right there with Babe (785 reputation)Right there with Babe (785 reputation)

Group: General Forum Members
Points: 785 Visits: 1033
The subqueries where a bit complicated for my liking, so I took your code and pulled out the subqueries into CTEs. Also, I have had indexes turn up with -ve numbers, so I don't like relying on the index_id being < 2 (I use an explicit (0, 1). That may be a hang over from statistics in SQL Server 2000, but I figure it's better safe than sorry.

Runs a wee bit quicker this way in my testing. Anyway, the ideas are still yours. Here's the code variation.


with
spart as (
select object_id,
sum([rows]) as [RowCount]
from sys.partitions
where index_id in (0, 1)
group by
object_id
),
sz as (
select i.object_id,
cast(round(
cast(v.low as decimal(36,4)) *
sum(
case when a.type <> 1 then 0
when p.index_id in (0, 1) then a.used_pages - a.data_pages
else a.used_pages
end)
/ 1024.00,
0)
as int) as [IndexKB],
cast(round(
cast(v.low as decimal(36,4)) *
sum(
case when a.type <> 1 then a.used_pages
when p.index_id in (0, 1) then a.data_pages
else 0
end)
/ 1024.00,
            0)
as int) as [DataKB]
from sys.indexes as i
inner join
sys.partitions as p
on p.object_id = i.object_id and
p.index_id = i.index_id
inner join
sys.allocation_units as a
on a.container_id = p.partition_id
inner join
master.dbo.spt_values v
on v.number = 1 and
v.type = 'E'
group by
v.low,
i.object_id
)
select schema_name(tbl.schema_id) as [Schema],
tbl.Name as [Table],
isnull(pr.name, schema_name(tbl.schema_id)) as [Owner],
tbl.max_column_id_used as [Columns],
cast(idx.index_id as bit) as [ClusteredIndex],
isnull(spart.[RowCount], 0) as [RowCount],
isnull(sz.[IndexKB], 0) as [IndexKB],
isnull(sz.[DataKB], 0) as [DataKB],
tbl.create_date,
tbl.modify_date
from sys.tables as tbl
inner join
sys.indexes as idx
on idx.object_id = tbl.object_id and
idx.index_id in (0, 1)
left join
sys.database_principals pr
on pr.principal_id = tbl.principal_id
left join
spart
on tbl.object_id = spart.object_id
left join
sz
on tbl.object_id = sz.object_id;




RBarryYoung
RBarryYoung
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9432 Visits: 9517
Thanks, Bruce

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Bruce W Cassidy
Bruce W Cassidy
Right there with Babe
Right there with Babe (785 reputation)Right there with Babe (785 reputation)Right there with Babe (785 reputation)Right there with Babe (785 reputation)Right there with Babe (785 reputation)Right there with Babe (785 reputation)Right there with Babe (785 reputation)Right there with Babe (785 reputation)

Group: General Forum Members
Points: 785 Visits: 1033
Welcome. It's a good script, and modifying it was easy. Smile

I used to do this on SQL Server 2000, just by selecting from sysindexes with a script like the following snippet:


select object_name(id) as [Table], Rows
from sysindexes
where indid in (0, 1) and object_name(id) not like 'sys%';



You can do this with nolock on the table to see the rows building.

However, I wanted to come up with something like that in SQL Server 2005, and had a bit of a play with it. Tracing the various connections between the system allocation tables turned into too much of a time waste at the time. But it turns out that you did the hard work for me!

So again, a good script. Added to my toolbox.

Manie Verster
Manie Verster
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1303 Visits: 982
Barry, a very nice script indeed and I have already added it to my bag of tricks. A question though: I see some tables in my database that looks like this "~TMPCLP156961". What are they and can I delete them?
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.

:-PManie Verster
Developer
Johannesburg
South Africa

I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
RBarryYoung
RBarryYoung
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9432 Visits: 9517
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.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
RBarryYoung
RBarryYoung
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9432 Visits: 9517
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.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Charles Kincaid
Charles Kincaid
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1025 Visits: 2382
Barry: Nice script. I'm adding it to my cache as well.

ATB

Charles Kincaid
Charles Kincaid
Charles Kincaid
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1025 Visits: 2382
Bruce, nice to see somebody using a CTE. wavesmash asked about selecting against Barry's script. I would wrap it in a CTE.

ATB

Charles Kincaid
Glen Cooper
Glen Cooper
SSC Journeyman
SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)

Group: General Forum Members
Points: 87 Visits: 287
Nice script Barry.

R Glen Cooper

R Glen Cooper
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search