March 17, 2008 at 1:24 am
Comments posted to this topic are about the item Table Information View -- No Cursors!
[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]
April 8, 2008 at 8:38 am
Thanks for your unique point of view - it works like a charm ... will be added to my bag of "tricks"
April 8, 2008 at 11:20 am
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
April 8, 2008 at 4:30 pm
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.
[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]
April 8, 2008 at 4:33 pm
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.
[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]
August 12, 2008 at 4:57 am
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!
September 8, 2008 at 1:08 am
[font="Arial Narrow"]😛[/font]
hello how r u dudes
i want to know abt the normalisations
November 25, 2008 at 8:31 pm
Nice script Barry. Is there one that will work on 2000?
Jack Corbett
Consultant - Straight Path Solutions
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
November 25, 2008 at 9:42 pm
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...
[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]
February 6, 2009 at 6:42 am
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
February 6, 2009 at 10:59 am
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.
[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]
March 16, 2009 at 4:56 pm
[font="Verdana"]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;
[/font]
March 16, 2009 at 9:08 pm
Thanks, Bruce
[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]
March 17, 2009 at 2:10 pm
[font="Verdana"]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.
[/font]
April 9, 2009 at 8:22 am
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)
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply