Cursor work around

Question

Post reply

Cursor work around

sxg6023

SSCrazy

Points: 2918
More actions
December 16, 2003 at 3:44 pm

#76097

The following script gives me a list of tables without any clustered index. I cannot think
of any other way than to use cursors and dynamic SQL to get the results for each database
on the server. I hear several posters on the forum talk about how bad the cursors are and how there
is always a work around. Can the following be done without a cursor ( or a while loop).
SET Quoted_Identifier OFF
GO
DECLARE dbCursor CURSOR
READ_ONLY
FOR select Name FROM master..sysdatabases
DECLARE @DBName nvarchar(40), @command varchar(255)
OPEN dbCursor
FETCH NEXT FROM dbCursor INTO @DBName
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
SELECT '---------------Tables with NO clustered indexes--------------------'
SELECT @command =
"SELECT CAST (tl.table_name AS VARCHAR (30) ) 'Table Name',
rowcnt Rows,
reserved / 128.0 'Space Allocated MB',
used / 128.0 'Space Used MB'
FROM "+@DBName+".information_schema.tables tl LEFT OUTER JOIN sysindexes si
ON si.id = object_id (tl.table_name)
WHERE table_type = 'Base Table' AND
(indid is NULL OR indid = 0)"
SELECT @command
END
FETCH NEXT FROM dbCursor INTO @DBName
END
Close dbCursor
DEALLOCATE dbCursor

Viewing 5 posts - 1 through 4 (of 4 total)

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

tikus SSC-Addicted Points: 463 More actions · Answer 1

Try this:

selectcast(o.name as varchar(30)) 'Table Name',

rowcnt Rows,

reserved / 128.0 'Space Allocated MB',

used / 128.0 'Space Used MB'

fromsysindexes i

join sysobjects o

on i.id = o.id

where ( i.indid is null

or i.indid = 0)

ando.xtype = 'u'

Note: This script has not been fully tested

tikus SSC-Addicted Points: 463 More actions · Answer 2

oops! I didn't read your posting carefully. sorry!

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 3

quote:
The following script gives me a list of tables without any clustered index. I cannot think
of any other way than to use cursors and dynamic SQL to get the results for each database
on the server. I hear several posters on the forum talk about how bad the cursors are and how there
is always a work around. Can the following be done without a cursor ( or a while loop).
<snip/>

I guess I am one of those several posters you refer to. Maybe there is no set based way to write the query you are using, you can of course try sp_MSForEachDB but it is still not set-based. However, I think you are misunderstanding the point about not using cursors. The problem you are solving is a one-time only (or at least once-in-a-while time), and using a cursor there is not a problem in any way.

However, if it was for an application that shall display a list of all tables that doesn't have a clustered index I would still not use a cursor. I would either do a query similar to 'SELECT ... FROM master..sysdatabases', then from that result query each database for the indexes and tables there. Or I would simply use SQL-DMO.

--

Chris Hedgate @ Extralives (http://www.extralives.com/)

Contributor to Best of SQL Server Central 2002 (http://www.sqlservercentral.com/bestof/)

Articles: http://www.sqlservercentral.com/columnists/chedgate/

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

Just wanted to add that there is an OBJECTPROPERTY 'TableHasClustIndex'.

Might be worth a try.

Frank

http://www.insidesql.de

http://www.familienzirkus.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Cursor work around

Cookies on SQLServerCentral