Nice script, good thinking, but needs some more effort to make more robust

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Cameron Wells » Nice script, good thinking, but needs some...

Nice script, good thinking, but needs some more effort to make more robust

Rate Topic

Display Mode

Topic Options

Author

Message

hmi

Posted Tuesday, April 20, 2010 1:02 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, December 16, 2011 1:25 AM
Points: 10, Visits: 174

The script did not work for me on SQL Server 2008.
After I added an extra JOIN to sys.SCHEMAS in the next piece of the script it did not give any runtime errors.

------------------------------------------------------------------------
-- UPDATING the number of NONCLUSTERED INDEXES per TABLE
------------------------------------------------------------------------
UPDATE #ClusteredIndexes
SET NonClusteredIndexCount = ISNULL((
SELECT 'NonClusteredIndexCount' = COUNT(*)
FROM sys.INDEXES I

JOIN sys.OBJECTS O -- Joining on sys.OBJECTS to get the TableName
ON O.OBJECT_ID = I.Object_ID
JOIN sys.SCHEMAS S
ON S.schema_id = O.schema_id
--AND S.name = @strSchemaName

WHERE I.type = @NonClusteredIndexType
AND QUOTENAME(O.Name) = T.TableName
AND QUOTENAME(S.Name) = T.SchemaName

GROUP BY I.object_id),0)

Post #906579

« Prev Topic | Next Topic »

Permissions