Cleaning User Stuff from the master Database

This is an upgrade to a script I wrote a long time ago. It's handy when you find a system whose master database is somehow full of user objects. (I know that none of us ever make that mistake...)

The biggest improvement is that this script now only generates DROP statement, it doesn't execute them. You have to do that yourself, and you should be sure to back up your master database (you do that on a regular basis anyway, right?), and be darn sure to read all the comments, and of course examine the generated T-SQL code, before executing it. Use at your own risk!

-------------------------------------------------------------------------------
-- Drops all user objects that have been accidentally created in 'master'.     
-- This script erases everything in the master database that is not Microsoft- 
-- shipped.  HOWEVER, be careful: some user objects BELONG in the master       
-- database (database triggers, security objects, for example). Backing up the 
-- master database first would be a prudent step.  Use at your own risk.       
-- NOTE: This script only generates the T-SQL code required.  You have to copy 
-- it and do the actual execution yourself.  You may have to run this script   
-- several times due to dependencies; you may also get error messages about    
-- objects that cannot be found, also due to dependencies.                     
-------------------------------------------------------------------------------

USE master

DECLARE @Sql            NVARCHAR(MAX)       SET @Sql            = ''
DECLARE @ObjectName     sysname             SET @ObjectName     = ''
DECLARE @ConstraintName sysname             SET @ConstraintName = ''
DECLARE @TableName      sysname             SET @TableName      = ''
DECLARE @TypeDesc       NVARCHAR(4000)      SET @TypeDesc       = ''
DECLARE @Type           NVARCHAR(1024)      SET @Type           = ''
DECLARE @Score          INT                 SET @Score          = 0
DECLARE @ObjectID       INT                 SET @ObjectID       = 0

DECLARE @ENumber        INT                 
DECLARE @ESeverity      INT                 
DECLARE @EState         INT                 
DECLARE @EProcedure     NVARCHAR(126)       
DECLARE @ELine          INT                 
DECLARE @EMessageRecv   NVARCHAR(2048)      
DECLARE @EMessageSent   NVARCHAR(440)       

RAISERROR('-- Execute this code only *AFTER* you have examined it throughly!', 10, 1) WITH NOWAIT
RAISERROR('USE master', 10, 1) WITH NOWAIT


-------------------------------------------------------------------------------
-- Most objects follow this pattern.                                           
-------------------------------------------------------------------------------

DECLARE cur CURSOR DYNAMIC FOR
    SELECT name,
           type_desc
      FROM sys.objects
     WHERE is_ms_shipped = 0
     ORDER BY type_desc, name

OPEN cur
FETCH FIRST FROM cur INTO @ObjectName, @TypeDesc

WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY
        IF @TypeDesc = 'VIEW'
        BEGIN
            SET @Sql = 'DROP VIEW ' + QUOTENAME(@ObjectName)
            RAISERROR('%s', 10, 1, @Sql) WITH NOWAIT    
        END
        ELSE IF @TypeDesc NOT LIKE '%_CONSTRAINT' ESCAPE '$'
        BEGIN
            SET @Type  = REVERSE(@TypeDesc)
            SET @Score = CHARINDEX('_', @Type)
            SET @Type  = SUBSTRING(@Type, 0, @Score)
            SET @Type  = REVERSE(@Type)
            SET @Sql   = 'DROP ' + @Type + ' ' + QUOTENAME(@ObjectName)
            RAISERROR('%s', 10, 1, @Sql) WITH NOWAIT    
        END
    END TRY
    BEGIN CATCH
        SET @ENumber      = ISNULL(ERROR_NUMBER(),          -1)
        SET @ESeverity    = ISNULL(ERROR_SEVERITY(),        -1)
        SET @EState       = ISNULL(ERROR_STATE(),            0)    IF @EState = 0 SET @EState = 42
        SET @EProcedure   = ISNULL(ERROR_PROCEDURE(), N'{N/A}')
        SET @ELine        = ISNULL(ERROR_LINE(),            -1)
        SET @EMessageRecv = ISNULL(ERROR_MESSAGE(),        N'')
        SET @EMessageSent = N''

        IF ERROR_PROCEDURE() IS NOT NULL   SET @EMessageSent = N'Error %d, Level %d, State %d, Procedure %s, Line %d, Message: '
        SET @EMessageSent = @EMessageSent + ERROR_MESSAGE()
        RAISERROR(@EMessageSent, 10 /* @ESeverity */ , @EState, @ENumber, @ESeverity, @EState, @EProcedure, @ELine) WITH LOG
    END CATCH

    FETCH NEXT FROM cur INTO @ObjectName, @TypeDesc
END

CLOSE cur
DEALLOCATE cur


-------------------------------------------------------------------------------
-- Constraints are the only exception to the pattern above.                    
-------------------------------------------------------------------------------

DECLARE cur CURSOR DYNAMIC FOR
    SELECT name,
           type_desc,
           OBJECT_NAME(parent_object_id)
      FROM sys.objects
     WHERE is_ms_shipped = 0
       AND type_desc LIKE '%_CONSTRAINT' ESCAPE '$'
  ORDER BY type_desc, name

OPEN cur    
FETCH FIRST FROM cur INTO @ConstraintName, @TypeDesc, @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY
        SET @Sql = 'ALTER TABLE ' + QUOTENAME(@TableName) + ' DROP CONSTRAINT ' + QUOTENAME(@ConstraintName)
        RAISERROR('%s', 10, 1, @Sql) WITH NOWAIT    
    END TRY
    BEGIN CATCH
        SET @ENumber      = ISNULL(ERROR_NUMBER(),          -1)
        SET @ESeverity    = ISNULL(ERROR_SEVERITY(),        -1)
        SET @EState       = ISNULL(ERROR_STATE(),            0)    IF @EState = 0 SET @EState = 42
        SET @EProcedure   = ISNULL(ERROR_PROCEDURE(), N'{N/A}')
        SET @ELine        = ISNULL(ERROR_LINE(),            -1)
        SET @EMessageRecv = ISNULL(ERROR_MESSAGE(),        N'')
        SET @EMessageSent = N''

        IF ERROR_PROCEDURE() IS NOT NULL   SET @EMessageSent = N'Error %d, Level %d, State %d, Procedure %s, Line %d, Message: '
        SET @EMessageSent = @EMessageSent + ERROR_MESSAGE()
        RAISERROR(@EMessageSent, 10 /* @ESeverity */ , @EState, @ENumber, @ESeverity, @EState, @EProcedure, @ELine) WITH LOG
    END CATCH

    FETCH NEXT FROM cur INTO @ConstraintName, @TypeDesc, @TableName
END

CLOSE cur
DEALLOCATE cur


-------------------------------------------------------------------------------
-- Done.                                                                       
-------------------------------------------------------------------------------

RAISERROR('-- Execute this code only *AFTER* you have examined it throughly!', 10, 1) WITH NOWAIT

"Select Top 1000 Rows" Doesn't Show SPARSE Columns?

A colleague pointed out something I'd never noticed about SQL Server Management Studio's (SSMS) "Select Top 1000 Rows" feature: it doesn't display SPARSE columns. This is not a bug, but rather by design: SQL Server tables can have up to 30,000 SPARSE columns: imagine the issues with viewing thousands of columns at a time!

The annoying thing, however, is that no matter how few columns a table has, not even a single SPARSE column will be displayed. To workaround this limitation, I've written a script that creates views for all tables with SPARSE columns. Since views don't suffer from this limitation in SSMS, you can use "Select Top 1000 Rows" on them to effectively see all the columns on the table.

I personally run it as a "startup script", just to semi-automate the maintenance of re-creating the views when tables change over time, but it could just a easily be set to run as a SQL Agent Job, or manually if desired.

-------------------------------------------------------------------------------
-- Declare variables.                                                          
-------------------------------------------------------------------------------

DECLARE @Prefix     NVARCHAR(50)  = '_'
DECLARE @Suffix     NVARCHAR(50)  = '_SPARSE'
DECLARE @SchemaName sysname       = ''
DECLARE @TableName  sysname       = ''
DECLARE @ViewName   sysname       = ''
DECLARE @Sql        NVARCHAR(MAX) = ''


-------------------------------------------------------------------------------
-- Since tables can be added, modified, and deleted, start with a clean slate. 
-------------------------------------------------------------------------------

DECLARE cur CURSOR FOR
    SELECT SCHEMA_NAME(schema_id)   AS 'SchemaName'
         , name                     AS 'TableName'
     FROM sys.views
    WHERE is_ms_shipped = 0 
      AND name LIKE '%' + @Suffix ESCAPE '$'

OPEN cur
FETCH NEXT FROM cur INTO @SchemaName, @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @ViewName = QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName)

    IF OBJECT_ID(@SchemaName, @ViewName) IS NULL
    BEGIN        
        SET @Sql = 'DROP VIEW ' + @ViewName
        EXEC sys.sp_executesql @Sql 
    END

    FETCH NEXT FROM cur INTO @SchemaName, @TableName
END

CLOSE cur
DEALLOCATE cur


-------------------------------------------------------------------------------
-- Iterate through all tables and create view for those with SPARSE columns.   
-------------------------------------------------------------------------------

DECLARE cur CURSOR FOR
    SELECT DISTINCT SCHEMA_NAME(t.schema_id)   AS 'SchemaName'
                  , t.name                     AS 'TableName'
     FROM sys.tables    t
     JOIN sys.columns   c
       ON t.object_id = c.
       object_id
    WHERE t.type_desc     = 'USER_TABLE'
      AND t.is_ms_shipped = 0 
      AND c.is_sparse     = 1

OPEN cur
FETCH NEXT FROM cur INTO @SchemaName, @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @ViewName  = QUOTENAME(@SchemaName) + '.' + QUOTENAME(@Prefix + @TableName + @Suffix)
    SET @TableName = QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName)
    
    IF OBJECT_ID(@SchemaName, @ViewName) IS NULL
    BEGIN        
        SET @Sql = 'CREATE VIEW ' + @ViewName + ' AS  SELECT * FROM ' + @TableName
        EXEC sys.sp_executesql @Sql 
    END

    FETCH NEXT FROM cur INTO @SchemaName, @TableName
END

CLOSE cur
DEALLOCATE cur

Once the above code has been run, any tables you have with SPARSE columns will have views created for them. (If you don't happen to have any, here's some code to create a small test table.)

SET NOCOUNT ON

-------------------------------------------------------------------------------
-- Set up test table.                                                          
-------------------------------------------------------------------------------

IF OBJECT_ID('MyTable') IS NOT NULL
    DROP TABLE MyTable

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE MyTable
(
    TableID            INT                  NOT NULL,
    MySparseColumn     NVARCHAR (50) SPARSE     NULL,
    MyNonSparseColumn  NVARCHAR (50)        NOT NULL
)

GO

INSERT MyTable (TableID, MySparseColumn, MyNonSparseColumn)
VALUES (1, NULL, 'ABC')

GO 10

Once you've got the views created, simply use the "Select Top 1000 Rows" feature as usual, and you'll be able to see all the columns in your tables...

... even the SPARSE ones.

Instantly Find Size of Tables on Disk

Here's a script to tell you how big your table(s) are, subtotaled by indexes, with a grand total. Includes LOBs, and excludes dropped pages.

SELECT CASE WHEN GROUPING(t.name) = 0 THEN t.name ELSE 'GRAND TOTAL' END    AS 'Table Name'
     , CASE WHEN GROUPING(i.name) = 0 THEN i.name ELSE 'TABLE TOTAL' END    AS 'Index Name'
     , SUM(au.total_pages) * 8 / 1024                                       AS 'Allocated MB'
     , SUM(au.used_pages)  * 8 / 1024                                       AS 'Used MB'

  FROM sys.allocation_units   au

  LEFT JOIN sys.partitions    pHobt
    ON au.container_id = pHobt.hobt_id
   AND (au.type_desc = 'IN_ROW_DATA'  OR  au.type_desc = 'ROW_OVERFLOW_DATA')

  LEFT JOIN sys.partitions    pLob
    ON au.container_id = pLob.partition_id
   AND au.type_desc    = 'LOB_DATA'

  JOIN sys.indexes            i  
    ON (i.object_id = pHobt.object_id  AND  i.index_id = pHobt.index_id)
         OR
       (i.object_id = pLob.object_id   AND  i.index_id = pLob.index_id)
   
  JOIN sys.tables             t
    ON i.object_id  = t.object_id
  
 WHERE au.type_desc != 'DROPPED'
   AND t.type_desc   = 'USER_TABLE'
   AND COALESCE(pHobt.rows, pLob.rows, 0) > 0
   AND t.name LIKE '%MyTable%'
  
 GROUP BY ROLLUP(t.name, i.name)
 ORDER BY t.name, i.name 

Easy Documentation: Does Anything Even Use That Index?

I don't know if it's ever happened to you, but I always find myself wishing I had documented what the devil I was thinking when I added an index to a table. I mean, it's all well and good to create an index, but it's annoying when six months later you can't remember what it was for. Is anything actually using this index? Can I maybe drop it?

Yes, yes, I suppose I could document my indexes, and I suppose some people do that kind of thing... but it just seems like so much... work.

Wouldn't it be cool if there was a way to automatically document which stored procedures use which indexes? And how many times each procedure used each index? And when the last time that was? And was integrated into SQL Server Management Studio, and all this with no work required on your part?

Of course it would be cool; so here it is. In this post, I'll just present the code, and what it will do for you; I'll walk through it in my next post.

Briefly, this script examines the current and cached query plans, and parses the info we want out of them; then it adds extended properties to each index.

After you run this script, your indexes will have extended properties like this, one for each stored procedure that uses it. Of course, the index had to have been in the query plan cache to show up here; since the script only takes a few seconds to run, I have a job that runs it every so often. Over the long run, all the indexes eventually get found in the cache; it just depends on how often your various applications run. Note that the date in each extended property is updated every time the script is run.

The Script

-------------------------------------------------------------------------------
-- Document Indexes via Query Plans and Extended Properties.sql                
--                                                                             
-- 2011-06-11 - Larry Leonard - http://sqlsoundings.blogspot.com               
--                                                                             
-- (Note: In the comments below "index" is shorthand for "index or primary key 
-- constraint", and "stored procedure" is shorthand for "stored procedure,     
-- trigger, etc.".)                                                            
--                                                                             
-- This script first examines each currently executing query plan and extracts 
-- (a) the name of each index each query plan is using and (b) the name of the 
-- executing stored procedure for the query plan.  An Extended Property for    
-- each stored procedure is added to the corresponding index(es).              
--                                                                             
-- As this script is run, over time, every index (assuming it's ever caught in 
-- use) gathers a set of Extended Properties.  Each Extended Property displays 
-- the *name* of the stored procedure that used it, how many *times* since the 
-- last SQL Server restart it used it, and the *date* this script last updated 
-- this Extended Property.  For example:                                       
--                                                                             
--    usp_CalculateInvoice     Used this constraint  3315 times   (2011-03-22) 
--    TR_Customer_Update       Used this index         42 times   (2011-05-01) 
--                                                                             
-- This script runs against the the current database.  It should be run on an  
-- irregular basis so that every query is more likely to be caught executing   
-- at least once.  How long it takes for every query to have executed at least 
-- once depends entirely on your system.  If after a sufficient amount of time 
-- has passed, an index does not have any Extended Properties created by this  
-- script, that doesn't prove the index is unused, only that the index needs   
-- investigation. Don't drop indexes based solely on the usage of this script. 
-- You should read and test this script thoroughly before using it in a        
-- production environment.                                                     
-------------------------------------------------------------------------------


RAISERROR('"Document Indexes via Query Plans and Extended Properties.sql" starting on "%s"' , 10, 1, @@SERVERNAME) WITH NOWAIT, LOG

IF DB_ID() < 5
BEGIN
    RAISERROR('This is a system database.', 16, 1)
    RETURN
END

SET NOCOUNT ON
SET XACT_ABORT ON

-- We don't want to kill some other process doing real work.
SET DEADLOCK_PRIORITY -10


-------------------------------------------------------------------------------
-- Variables. None of these should need to be changed in normal use.           
-------------------------------------------------------------------------------

DECLARE @DatabaseName             sysname        = ''
DECLARE @ProcedureName            sysname        = ''
DECLARE @SchemaName               sysname        = ''
DECLARE @SchemaNameUnquoted       sysname        = ''
DECLARE @TableName                sysname        = ''
DECLARE @TableNameUnquoted        sysname        = ''
DECLARE @IndexName                sysname        = ''
DECLARE @PlanUseCount             INT            = 0
DECLARE @PlanUseCountString       NVARCHAR(50)   = ''
DECLARE @Len                      INT            = 0
DECLARE @IndexNamePrev            sysname        = ''
DECLARE @IndexNameUnquoted        sysname        = ''
DECLARE @ExtendedPropertyName     sysname        = ''
DECLARE @ExtendedPropertyValue    NVARCHAR(3750) = ''       -- The value of an extended property is a sql_variant value that can contain up to 7,500 bytes of data. 
DECLARE @ExtendedPropertyValueOld NVARCHAR(3750) = ''
DECLARE @Level2Type               VARCHAR(50)    = ''
DECLARE @Now                      NVARCHAR(50)   = CONVERT(NVARCHAR, CAST(GETDATE() AS DATE), 120)
DECLARE @Msg                      NVARCHAR(2048) = ''
DECLARE @Error                    INT            = 0
DECLARE @AddedCount               INT            = 0
DECLARE @UpdatedCount             INT            = 0
DECLARE @UnchangedCount           INT            = 0

RAISERROR('Looking for indexes in the plan cache...', 10, 1) WITH NOWAIT

DECLARE cur CURSOR FOR 
   WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan'),
    cteCachedPlansXml
    AS
    (
         SELECT DISTINCT
                cp.plan_handle                                       AS plan_handle

              , COALESCE(RelOp.op.value(N'TableScan [1]/Object[1]/@Database',          N'varchar(128)'),
                         RelOp.op.value(N'OutputList[1]/ColumnReference[1]/@Database', N'varchar(128)'),
                         RelOp.op.value(N'IndexScan [1]/Object[1]/@Database',          N'varchar(128)'))
                                                                     AS DatabaseName

              , COALESCE(RelOp.op.value(N'TableScan [1]/Object[1]/@Schema',            N'varchar(128)'),
                         RelOp.op.value(N'OutputList[1]/ColumnReference[1]/@Schema',   N'varchar(128)'),
                         RelOp.op.value(N'IndexScan [1]/Object[1]/@Schema',            N'varchar(128)')) 
                                                                     AS SchemaName

              , COALESCE(RelOp.op.value(N'TableScan [1]/Object[1]/@Table',             N'varchar(128)'),
                         RelOp.op.value(N'OutputList[1]/ColumnReference[1]/@Table',    N'varchar(128)'),
                         RelOp.op.value(N'IndexScan [1]/Object[1]/@Table',             N'varchar(128)'))
                                                                     AS TableName
             
              , COALESCE(RelOp.op.value(N'TableScan [1]/Object[1]/@Index',             N'varchar(128)'),
                         RelOp.op.value(N'OutputList[1]/ColumnReference[1]/@Index',    N'varchar(128)'),
                         RelOp.op.value(N'IndexScan [1]/Object[1]/@Index',             N'varchar(128)'))
                                                                     AS IndexName

              , QUOTENAME(OBJECT_NAME(qp.objectid))                  AS ProcedureName
              , ISNULL(cp.usecounts, 0)                              AS PlanUseCount              
           FROM sys.dm_exec_cached_plans                 cp
    CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle)   qp
    CROSS APPLY qp.query_plan.nodes(N'//RelOp')          RelOp (op)
          WHERE qp.dbid         = DB_ID()
            AND cp.cacheobjtype IN ('Compiled Plan', 'Compiled Plan Stub', 'Parse Tree', 'Extended Proc', 'CLR Compiled Func', 'CLR Compiled Proc')
            AND cp.objtype      IN ('Proc', 'Prepared', 'Adhoc', 'ReplProc', 'Trigger', 'View', 'Default', 'UsrTab', 'SysTab', 'Check', 'Rule')            
    )
    ,
    cteCachedPlans AS
    (
         SELECT DISTINCT 
                ctecpx.SchemaName                                                                     AS SchemaName
              , ctecpx.TableName                                                                      AS TableName
              , ctecpx.IndexName                                                                      AS IndexName
              , SUBSTRING(ctecpx.IndexName, 2, LEN(ctecpx.IndexName) - 2)                             AS IndexNameUnquoted
              , CASE MIN(CAST(i.is_primary_key as int)) WHEN 0 THEN 'INDEX' ELSE 'CONSTRAINT' END     AS Level2Type
              , ctecpx.ProcedureName                                                                  AS ProcedureName
              , MIN(ctecpx.PlanUseCount)                                                              AS PlanUseCountMin
              , MAX(ctecpx.PlanUseCount)                                                              AS PlanUseCountMax
              , SUM(ctecpx.PlanUseCount)                                                              AS PlanUseCountSum
           FROM cteCachedPlansXml                        ctecpx               
           JOIN sys.dm_exec_query_stats                  qs
             ON ctecpx.plan_handle = qs.plan_handle
           JOIN sys.indexes                              i
             ON OBJECT_ID(ctecpx.TableName) = i.object_id
            AND INDEXPROPERTY(OBJECT_ID(ctecpx.TableName), SUBSTRING(ctecpx.IndexName, 2, LEN(ctecpx.IndexName) - 2), 'IndexId') = i.index_id
           JOIN sys.tables                               t
             ON  i.object_id = t.object_id
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle)      st
          WHERE st.dbid              = DB_ID()
            AND ctecpx.DatabaseName  = QUOTENAME(DB_NAME())
            AND ctecpx.SchemaName   != QUOTENAME('sys')
            AND ctecpx.SchemaName    IS NOT NULL 
            AND ctecpx.TableName     NOT LIKE '$[$#%' ESCAPE '$'
            AND ctecpx.TableName     IS NOT NULL 
            AND ctecpx.IndexName     IS NOT NULL 
            AND ctecpx.ProcedureName IS NOT NULL
            AND t.type_desc NOT IN ('SQL_SCALAR_FUNCTION')          -- Added to as I find them. 
       GROUP BY ctecpx.SchemaName
              , ctecpx.TableName
              , ctecpx.IndexName
              , ctecpx.ProcedureName
    )
    ,
    cteIndexExtendedProperties AS
    (
        SELECT ctecp.SchemaName
             , ctecp.TableName
             , ctecp.IndexName
             , ctecp.IndexNameUnquoted
             , ctecp.ProcedureName
             , ctecp.PlanUseCountSum
             , ISNULL(CAST(ep.value AS NVARCHAR(3750)), '')   AS ExtendedPropertyValue
             , ctecp.Level2Type                               AS Level2Type
          FROM cteCachedPlans             ctecp
     LEFT JOIN sys.extended_properties    ep
            ON ep.major_id = OBJECT_ID(ctecp.SchemaName + '.' + ctecp.TableName, 'U')
           AND ep.minor_id = INDEXPROPERTY(OBJECT_ID(ctecp.SchemaName + '.' + ctecp.TableName, 'U'), ctecp.IndexNameUnquoted, 'IndexId')
         WHERE ISNULL(ep.class_desc, 'INDEX') = 'INDEX'
    )
    ,
    ctePkExtendedProperties AS
    (
        SELECT ctecp.SchemaName
             , ctecp.TableName
             , ctecp.IndexName
             , ctecp.IndexNameUnquoted
             , ctecp.ProcedureName
             , ctecp.PlanUseCountSum    
             , ISNULL(CAST(ep.value AS NVARCHAR(3750)), '')   AS ExtendedPropertyValue
             , ctecp.Level2Type                               AS Level2Type
          FROM cteCachedPlans             ctecp
     LEFT JOIN sys.extended_properties    ep
            ON ep.major_id = OBJECT_ID(ctecp.SchemaName + '.' + ctecp.TableName + '.' + ctecp.IndexName, 'PK')
           AND ep.minor_id = 0                                                              -- 0 means on the index, not on any columns. 
         WHERE ISNULL(ep.class_desc, 'OBJECT_OR_COLUMN') = 'OBJECT_OR_COLUMN'
    )
    ,
    cteExtendedProperties AS
    (
        SELECT cteiep.SchemaName                               AS SchemaName
             , cteiep.TableName                                AS TableName
             , cteiep.IndexName                                AS IndexName
             , cteiep.ProcedureName                            AS ProcedureName
             , SUM(cteiep.PlanUseCountSum)                     AS PlanUseCount 
             , MIN(cteiep.ExtendedPropertyValue)               AS ExtendedPropertyValue
             , ISNULL(cteiep.Level2Type, ctepkep.Level2Type)   AS Level2Type
          FROM cteIndexExtendedProperties   cteiep
     LEFT JOIN ctePkExtendedProperties      ctepkep
            ON cteiep.SchemaName    = ctepkep.SchemaName
           AND cteiep.TableName     = ctepkep.TableName
           AND cteiep.IndexName     = ctepkep.IndexName
           AND cteiep.ProcedureName = ctepkep.ProcedureName
      GROUP BY cteiep.SchemaName, cteiep.TableName, cteiep.IndexName, cteiep.ProcedureName, ISNULL(cteiep.Level2Type, ctepkep.Level2Type)
    )
    SELECT SchemaName
         , TableName
         , IndexName
         , ProcedureName
         , PlanUseCount 
         , ExtendedPropertyValue
         , Level2Type
      FROM cteExtendedProperties        
  ORDER BY SchemaName
         , TableName
         , IndexName
         , ProcedureName
         , PlanUseCount 
         , ExtendedPropertyValue
         , Level2Type


-------------------------------------------------------------------------------
-- Now that we've created the SELECT statement, iterate it through a cursor.   
-------------------------------------------------------------------------------

OPEN cur
RAISERROR('Found %d query plans with index usage in the cache', 10, 1, @@CURSOR_ROWS) WITH NOWAIT

FETCH NEXT FROM cur
 INTO @SchemaName, @TableName, @IndexName, @ProcedureName, @PlanUseCount, @ExtendedPropertyValue, @Level2Type
 
WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY

        -- Build some values we're going to need. 
        SET @Len = LEN(@ProcedureName)  SET @ExtendedPropertyName = SUBSTRING(@ProcedureName, 2, @Len - 2)
        SET @Len = LEN(@SchemaName)     SET @SchemaNameUnquoted   = SUBSTRING(@SchemaName,    2, @Len - 2)
        SET @Len = LEN(@TableName)      SET @TableNameUnquoted    = SUBSTRING(@TableName,     2, @Len - 2)
        SET @Len = LEN(@IndexName)      SET @IndexNameUnquoted    = SUBSTRING(@IndexName,     2, @Len - 2)
        SET @Len = LEN(@Level2Type)     SET @Level2Type           = @Level2Type + SPACE(LEN('CONSTRAINT') - @Len)
        
        SET @ExtendedPropertyValueOld = @ExtendedPropertyValue
        SET @PlanUseCountString       = REPLACE(CONVERT(NVARCHAR(50), CAST(@PlanUseCount AS MONEY), 1), '.00', '')
        SET @PlanUseCountString       = SPACE(10 - LEN(@PlanUseCountString)) + @PlanUseCountString
        SET @ExtendedPropertyValue    = 'Used this ' + LOWER(@Level2Type) + ' ' + @PlanUseCountString 
        SET @ExtendedPropertyValue   += CASE @PlanUseCount WHEN 1 THEN ' time' ELSE ' times' END 
        SET @ExtendedPropertyValue   += '   (' + @Now + ')'

        -- If the index name is new, create a display heading for it; just for readability. 
        IF @IndexName != @IndexNamePrev
        BEGIN
            SET @IndexNamePrev = @IndexName
            SET @Msg = @SchemaNameUnquoted + '.' + @TableNameUnquoted + '.' + @IndexNameUnquoted
            RAISERROR(' ',  10, 1) WITH NOWAIT
            RAISERROR(@Msg, 10, 1) WITH NOWAIT
        END   
        
        -- If the value hasn't changed, nothing to do. 
        BEGIN TRAN 

        IF @ExtendedPropertyValue = @ExtendedPropertyValueOld
        BEGIN
            RAISERROR('   Unchanged: %-40s = %s', 10, 1, @ExtendedPropertyName, @ExtendedPropertyValue) WITH NOWAIT
            SET @UnchangedCount += 1
        END
        ELSE IF EXISTS (SELECT *
                         FROM sys.extended_properties    ep
                        WHERE ep.class_desc = 'INDEX'
                          AND ep.name       = @ExtendedPropertyName
                          AND ep.major_id   = OBJECT_ID(@SchemaNameUnquoted + '.' + @TableNameUnquoted, 'U')
                          AND ep.minor_id   = INDEXPROPERTY(OBJECT_ID(@SchemaNameUnquoted + '.' + @TableNameUnquoted, 'U'), @IndexNameUnquoted, 'IndexId')
                       )
                  OR

                EXISTS (SELECT *
                          FROM sys.extended_properties    ep
                     LEFT JOIN sys.key_constraints        kc
                            ON ep.major_id = kc.parent_object_id
                           AND ep.minor_id = 0   
                         WHERE ep.class_desc = 'OBJECT_OR_COLUMN'
                           AND ep.name       = @ExtendedPropertyName
                           AND ep.major_id   = OBJECT_ID(@SchemaNameUnquoted + '.' + @IndexNameUnquoted, 'PK')
                       )
        BEGIN    
            -- Update any existing one. 
            EXEC @Error = sys.sp_updateextendedproperty 
                @name       = @ExtendedPropertyName, 
                @value      = @ExtendedPropertyValue,
                @level0type = 'SCHEMA',    @level0name = @SchemaNameUnquoted, 
                @level1type = 'TABLE',     @level1name = @TableNameUnquoted,
                @level2type = @Level2Type, @level2name = @IndexNameUnquoted

            IF @Error = 0
            BEGIN
                SET @Msg = @SchemaNameUnquoted + '.' + @TableNameUnquoted + '.' + @IndexNameUnquoted
                RAISERROR('   Updated:   %-40s = %s', 10, 1, @ExtendedPropertyName, @ExtendedPropertyValue) WITH NOWAIT
                SET @UpdatedCount += 1
            END
            ELSE
            BEGIN
                SET @Msg = ISNULL(ERROR_MESSAGE(), '{ No error }')
                RAISERROR(@Msg, 16, 1) WITH NOWAIT, LOG
            END
        END
        ELSE
        BEGIN
            -- Write extended property. 
            EXEC @Error = sys.sp_addextendedproperty 
                @name       = @ExtendedPropertyName, 
                @value      = @ExtendedPropertyValue,
                @level0type = 'SCHEMA',    @level0name = @SchemaNameUnquoted, 
                @level1type = 'TABLE',     @level1name = @TableNameUnquoted,
                @level2type = @Level2Type, @level2name = @IndexNameUnquoted

            IF @Error = 0
            BEGIN
                SET @Msg = @SchemaNameUnquoted + '.' + @TableNameUnquoted + '.' + @IndexNameUnquoted
                RAISERROR('   Added:     %-40s = %s', 10, 1, @ExtendedPropertyName, @ExtendedPropertyValue) WITH NOWAIT
                SET @AddedCount += 1
            END
            ELSE
            BEGIN
                SET @Msg = ISNULL(ERROR_MESSAGE(), '{ No error }')
                RAISERROR(@Msg, 16, 42) WITH NOWAIT, LOG
            END
        END
        
        -- Done with this extended property. 
        COMMIT
    END TRY
    BEGIN CATCH
        -- MS bug: if sp_addextendedproperty or sp_dropextendedproperty throw an exception, they 
        -- leave a transaction open.  You can verify this by examining the @@TRANCOUNT value. 
        SET @Msg  = '   *** ERROR ' + CAST(@@ERROR AS NVARCHAR) + ': ' + ISNULL(ERROR_MESSAGE(), '{ No error }') + CHAR(10)
        SET @Msg += '   The @@TRANCOUNT is ' + CAST(@@TRANCOUNT AS NVARCHAR) + CHAR(10) + '   '
        
        -- If  1, the transaction is committable. 
        -- If -1, the transaction is uncommittable and should be rolled back. 
        -- If  0, there is no transaction and a commit or rollback operation would generate an error. 
        IF XACT_STATE() = -1 
        BEGIN
            SET @Msg += 'The transaction is in an uncommittable state.  Rolling back all transactions.'
            ROLLBACK
        END
        ELSE IF XACT_STATE() = 1 
        BEGIN
            SET @Msg += 'The transaction is committable.  Committing transaction.'
            COMMIT 
        END 

        -- If this is a deadlock, the rest of the indexes on this table will probably deadlock too. 
        -- Skip to the next table. 
        IF ERROR_NUMBER() = 1205
        BEGIN
            WHILE @@FETCH_STATUS = 0
            BEGIN
                FETCH NEXT FROM cur
                 INTO @SchemaName, @TableName, @IndexName, @ProcedureName, @PlanUseCount, @ExtendedPropertyValue, @Level2Type
            
                IF QUOTENAME(@TableNameUnquoted) != @TableName  BREAK
                RAISERROR('   Skipping %s.%s.%s to get to the next table', 10, 1, @SchemaName, @TableName, @IndexName)
            END
        END
        ELSE
        BEGIN
            -- If it wasn't a deadlock, something else bad must have happenned, so we don't want to 
            -- continue - this will cause an abort that displays the error message. 
            RAISERROR(@Msg, 16, 1) WITH NOWAIT, LOG
        END
    END CATCH

    -- Next row. 
    FETCH NEXT FROM cur
     INTO @SchemaName, @TableName, @IndexName, @ProcedureName, @PlanUseCount, @ExtendedPropertyValue, @Level2Type
END

-- Close and deallocate the cursor. 
CLOSE cur
DEALLOCATE cur

RAISERROR(' ',    10, 1) WITH NOWAIT
RAISERROR('Done - Extended properties: added %d new, updated %d existing, %d unchanged', 10, 1, @AddedCount, @UpdatedCount, @UnchangedCount) WITH NOWAIT, LOG

GO

Related Articles

• Modifying and Deleting Extended Properties (sqlservercentral.com)
• Industrial-strength Database Documentation using Extended Properties (sqlservercentral.com)