Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Nice script, good thinking, but needs some more effort to make more robust Expand / Collapse
Author
Message
Posted Tuesday, April 20, 2010 1:02 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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 »

Add to briefcase

Permissions Expand / Collapse