December 16, 2003 at 3:44 pm
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
December 16, 2003 at 6:06 pm
Try this:
select cast(o.name as varchar(30)) 'Table Name',
rowcnt Rows,
reserved / 128.0 'Space Allocated MB',
used / 128.0 'Space Used MB'
from sysindexes i
join sysobjects o
on i.id = o.id
where ( i.indid is null
or i.indid = 0)
and o.xtype = 'u'
Note: This script has not been fully tested
December 16, 2003 at 6:14 pm
oops! I didn't read your posting carefully. sorry!
December 17, 2003 at 8:38 am
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://qa.sqlservercentral.com/bestof/)
Articles: http://qa.sqlservercentral.com/columnists/chedgate/
December 17, 2003 at 1:56 pm
Just wanted to add that there is an OBJECTPROPERTY 'TableHasClustIndex'.
Might be worth a try.
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply