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 Friday, February 06, 2009 10:59 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902, Visits: 9,479
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."
Post #651861
Posted Monday, March 16, 2009 4:56 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 1:41 PM
Points: 957, Visits: 1,028
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;

Post #677043
Posted Monday, March 16, 2009 9:08 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902, Visits: 9,479
Thanks, Bruce

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #677134
Posted Tuesday, March 17, 2009 2:10 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 1:41 PM
Points: 957, Visits: 1,028
Welcome. It's a good script, and modifying it was easy. :)

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.
Post #677866
Posted Thursday, April 09, 2009 8:22 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, March 28, 2014 5:06 AM
Points: 1,204, Visits: 915
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.


Manie 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)
Post #694041
Posted Thursday, April 09, 2009 8:52 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902, Visits: 9,479
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."
Post #694080
Posted Thursday, April 09, 2009 8:56 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902, Visits: 9,479
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."
Post #694086
Posted Friday, April 24, 2009 8:43 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, February 06, 2014 12:59 PM
Points: 801, Visits: 1,962
Barry: Nice script. I'm adding it to my cache as well.

ATB

Charles Kincaid

Post #704019
Posted Friday, April 24, 2009 8:45 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, February 06, 2014 12:59 PM
Points: 801, Visits: 1,962
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

Post #704022
Posted Monday, June 29, 2009 3:55 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, April 21, 2014 4:40 PM
Points: 51, Visits: 256
Nice script Barry.

R Glen Cooper


R Glen Cooper
glencooper.tel
Post #744050
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse