Procedure: [AdventureWorks2016CTP3].[SQLCop].[test Forwarded Records]

CollapseAll image

Collapse image Procedure properties


 Name   Value 
 Schema   [SQLCop] 
 Owner   [dbo] 
 Creation date   19.09.2016 
 Type   P 
 Encrypted   
 ID   283864078 
 Implementation type   Transact SQL 
 Is native compiled   

Collapse image Creation options


Name Value
QUOTED_IDENTIFIER ON
ANSI_NULLS ON

Collapse image Objects that [SQLCop].[test Forwarded Records] depends on


Object name Object type Dep level
[SQLCop] Schema 1
[tSQLt].[Fail] Unknown 1
Total 2 object(s)

Collapse image SQL


SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [SQLCop].[test Forwarded Records]
AS
BEGIN
    -- Written by George Mastros
    -- February 25, 2012
    -- http://sqlcop.lessthandot.com
    -- http://wiki.lessthandot.com/index.php/Finding_Forwarded_Records_SQL_Server_2008
    
    SET NOCOUNT ON
    
    DECLARE @Output VarChar(max)
    SET @Output = ''

    If Exists(Select cmptlevel from master.dbo.sysdatabases Where dbid = db_ID() And cmptlevel > 80)
        Begin
            Create Table #Results(ProblemItem VarChar(1000))
            
            Insert Into #Results(ProblemItem)
            Exec ('    SELECT    SCHEMA_NAME(O.Schema_Id) + ''.'' + O.name As ProblemItem
                    FROM    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, ''DETAILED'') AS ps
                            INNER JOIN sys.indexes AS i      
                                ON ps.OBJECT_ID = i.OBJECT_ID
                                AND ps.index_id = i.index_id
                            INNER JOIN sys.objects as O
                                On i.OBJECT_ID = O.OBJECT_ID
                    WHERE  ps.forwarded_record_count > 0
                    Order By SCHEMA_NAME(O.Schema_Id),O.name')
            
            If Exists(Select 1 From #Results)
                Select    @Output = @Output + ProblemItem + Char(13) + Char(10)
                From    #Results
            
        End
    Else
      Set @Output = 'Unable to check index forwarded records when compatibility is set to 80 or below'

    If @Output > '' 
        Begin
            Set @Output = Char(13) + Char(10) 
                          + 'For more information:  '
                          + 'http://wiki.lessthandot.com/index.php/Finding_Forwarded_Records_SQL_Server_2008'
                          + Char(13) + Char(10) 
                          + Char(13) + Char(10) 
                          + @Output
            EXEC tSQLt.Fail @Output
        End      
  
END;

GO

Collapse image See also


List of procedures