running sql 2008 enterprise and this script fails 🙁

it repeats the message >

Msg 208, Level 16, State 1, Line 1

Invalid object name 'tablename'.

for each one of my tables....

I believe you've omitted the schemaname from the code

The script will work fine if you have one schema in the database and run it as a user who has that schema as their default.

e.g. at line 295 where it loops tables to get>

SET @Tmp = ' UPDATE #ClusteredIndexes

SET TableRowCount = (SELECT COUNT(*)

FROM '+@TableName+'

WHERE ClusteredIndexID = ' + CONVERT(VARCHAR,@ClusteredIndexID) + ')

WHERE ClusteredIndexID = ' + CONVERT(VARCHAR,@ClusteredIndexID) + ' '

You need to add the schemaname to the code to pick up the row counts correctly. Otherwise the script could also fail if 2 or more tables are named the same but belong to different schemas (e.g. Import.MyData and Processing.MyData)

I would also suggest an alternative (faster) way of getting your row counts >

select

S.name as schemaname,

T.name as tablename,

P.rows

from

sys.partitions P Join

sys.tables T On

P.object_Id = T.object_id Join

sys.schemas S On

T.schema_id = S.schema_id

Where

P.index_id in (0,1)

Looks really good though when these are ironed out and i'd certainly use.

Inspired..., thank you.

r

--------------------------------------------------

Richard Doering

MCITP SQL 2008 Administrator + Developer

MCDBA SQL 2000 | MCSE Windows 2000

http://sqlsolace.blogspot.com/[/url]

http://uk.linkedin.com/pub/richard-doering/5/268/37

--------------------------------------------------