SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by RBarryYoung / Table Information View -- No Cursors! / Latest Posts

RE: Table Information View -- No Cursors!

fercasas — Tue, 29 Jan 2013 13:02:40 GMT

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? :-)

RE: Table Information View -- No Cursors!

RBarryYoung — Tue, 30 Jun 2009 00:27:39 GMT

Thanks Glenn.

RE: Table Information View -- No Cursors!

Glen Cooper — Mon, 29 Jun 2009 15:55:49 GMT

Nice script Barry.R Glen Cooper

RE: Table Information View -- No Cursors!

Charles Kincaid — Fri, 24 Apr 2009 08:45:48 GMT

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

RE: Table Information View -- No Cursors!

Charles Kincaid — Fri, 24 Apr 2009 08:43:50 GMT

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

RE: Table Information View -- No Cursors!

RBarryYoung — Thu, 09 Apr 2009 08:56:59 GMT

[quote][b]Manie Verster (4/9/2009)[/b][hr]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.[/quote]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.

RE: Table Information View -- No Cursors!

RBarryYoung — Thu, 09 Apr 2009 08:52:25 GMT

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

RE: Table Information View -- No Cursors!

Manie Verster — Thu, 09 Apr 2009 08:22:51 GMT

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.

RE: Table Information View -- No Cursors!

Bruce W Cassidy — Tue, 17 Mar 2009 14:10:38 GMT

[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:[code]select object_name(id) as [Table], Rowsfrom sysindexeswhere indid in (0, 1) and object_name(id) not like 'sys%';[/code]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]

RE: Table Information View -- No Cursors!

RBarryYoung — Mon, 16 Mar 2009 21:08:21 GMT

Thanks, Bruce

RE: Table Information View -- No Cursors!

Bruce W Cassidy — Mon, 16 Mar 2009 16:56:49 GMT

[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.[code]withspart 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_datefrom sys.tables as tblinner 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_idleft join spart on tbl.object_id = spart.object_idleft join sz on tbl.object_id = sz.object_id;[/code][/font]

RE: Table Information View -- No Cursors!

RBarryYoung — Fri, 06 Feb 2009 10:59:23 GMT

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.

RE: Table Information View -- No Cursors!

GabyYYZ — Fri, 06 Feb 2009 06:42:14 GMT

[quote][b]bitbucket (4/8/2008)[/b][hr]Thanks for your unique point of view - it works like a charm ... will be added to my bag of "tricks"[/quote]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.

RE: Table Information View -- No Cursors!

RBarryYoung — Tue, 25 Nov 2008 21:42:26 GMT

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...

RE: Table Information View -- No Cursors!

Jack Corbett — Tue, 25 Nov 2008 20:31:50 GMT

Nice script Barry. Is there one that will work on 2000?

RE: Table Information View -- No Cursors!

shadab08 — Mon, 08 Sep 2008 01:08:54 GMT

[font="Arial Narrow"][size="6"][sup][sub]:P[/sub][/sup][/size][/font]hello how r u dudesi want to know abt the normalisations

RE: Table Information View -- No Cursors!

jezman — Tue, 12 Aug 2008 04:57:40 GMT

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!

RE: Table Information View -- No Cursors!

RBarryYoung — Tue, 08 Apr 2008 16:33:53 GMT

[quote][b]wavesmash (4/8/2008)[/b][hr]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[/quote]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 [i]anywhere[/i] 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.

RE: Table Information View -- No Cursors!

RBarryYoung — Tue, 08 Apr 2008 16:30:21 GMT

[quote][b]bitbucket (4/8/2008)[/b][hr]Thanks for your unique point of view - it works like a charm ... will be added to my bag of "tricks"[/quote]Thanks for the feedback Bit.

RE: Table Information View -- No Cursors!

wavesmash — Tue, 08 Apr 2008 11:20:20 GMT

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

RE: Table Information View -- No Cursors!

bitbucket-25253 — Tue, 08 Apr 2008 08:38:08 GMT

Thanks for your unique point of view - it works like a charm ... will be added to my bag of "tricks"

Table Information View -- No Cursors!

RBarryYoung — Mon, 17 Mar 2008 01:24:38 GMT

Comments posted to this topic are about the item [B]Table Information View -- No Cursors![/B]