Skip to content

Instantly share code, notes, and snippets.

@lionofdezert

lionofdezert/TablesInSPsWithoutIndexes.sql

Created November 29, 2012 14:12
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save lionofdezert/4169335 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save lionofdezert/4169335 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. lionofdezert revised this gist Nov 29, 2012. 1 changed file with 28 additions and 28 deletions.
    56 changes: 28 additions & 28 deletions TablesInSPsWithoutIndexes.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -8,34 +8,34 @@ Purpose: To get list of stored procedures which contains tables but without non

    SELECT *
    FROM ( SELECT SCHEMA_NAME(schema_id) + '.' + NAME AS ProcedureName,
    SUBSTRING(( SELECT DISTINCT
    ', ' + OBJDEP.NAME
    FROM sysdepends
    INNER JOIN sys.objects OBJ ON sysdepends.ID = OBJ.OBJECT_ID
    INNER JOIN sys.objects OBJDEP ON sysdepends.DEPID = OBJDEP.OBJECT_ID
    WHERE obj.type = 'P'
    AND Objdep.type IN ( 'U', 'V' ) --view and tables
    AND EXISTS ( SELECT 1
    FROM sys.indexes
    WHERE type = 2
    AND Objdep.OBJECT_ID = OBJECT_ID )
    AND sysdepends.id = procs.object_id
    FOR
    XML PATH('') ), 2, 8000) AS TablesWithNCIndexes,
    SUBSTRING(( SELECT DISTINCT
    ', ' + OBJDEP.NAME
    FROM sysdepends
    INNER JOIN sys.objects OBJ ON sysdepends.ID = OBJ.OBJECT_ID
    INNER JOIN sys.objects OBJDEP ON sysdepends.DEPID = OBJDEP.OBJECT_ID
    WHERE obj.type = 'P'
    AND Objdep.type IN ( 'U', 'V' ) --view and tables
    AND NOT EXISTS ( SELECT 1
    FROM sys.indexes
    WHERE type = 2
    AND Objdep.OBJECT_ID = OBJECT_ID )
    AND sysdepends.id = procs.object_id
    FOR
    XML PATH('') ), 2, 8000) AS TablesWithOutNCIndexes
    SUBSTRING(( SELECT DISTINCT
    ', ' + OBJDEP.NAME
    FROM sysdepends
    INNER JOIN sys.objects OBJ ON sysdepends.ID = OBJ.OBJECT_ID
    INNER JOIN sys.objects OBJDEP ON sysdepends.DEPID = OBJDEP.OBJECT_ID
    WHERE obj.type = 'P'
    AND Objdep.type IN ( 'U', 'V' ) --view and tables
    AND EXISTS ( SELECT 1
    FROM sys.indexes
    WHERE type = 2
    AND Objdep.OBJECT_ID = OBJECT_ID )
    AND sysdepends.id = procs.object_id
    FOR
    XML PATH('') ), 2, 8000) AS TablesWithNCIndexes,
    SUBSTRING(( SELECT DISTINCT
    ', ' + OBJDEP.NAME
    FROM sysdepends
    INNER JOIN sys.objects OBJ ON sysdepends.ID = OBJ.OBJECT_ID
    INNER JOIN sys.objects OBJDEP ON sysdepends.DEPID = OBJDEP.OBJECT_ID
    WHERE obj.type = 'P'
    AND Objdep.type IN ( 'U', 'V' ) --view and tables
    AND NOT EXISTS ( SELECT 1
    FROM sys.indexes
    WHERE type = 2
    AND Objdep.OBJECT_ID = OBJECT_ID )
    AND sysdepends.id = procs.object_id
    FOR
    XML PATH('') ), 2, 8000) AS TablesWithOutNCIndexes
    FROM sys.procedures procs
    WHERE procs.name NOT LIKE 'sp_%' --skip system stored procedures
    ) InnerTab
  2. lionofdezert revised this gist Nov 29, 2012. 1 changed file with 8 additions and 0 deletions.
    8 changes: 8 additions & 0 deletions TablesInSPsWithoutIndexes.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,3 +1,11 @@
    /*
    Script By: Aasim Abdullah for http://connectsql.blogspot.com
    Purpose: To get list of stored procedures which contains tables but without non clusterd indexes
    */


    SELECT *
    FROM ( SELECT SCHEMA_NAME(schema_id) + '.' + NAME AS ProcedureName,
    SUBSTRING(( SELECT DISTINCT
  3. lionofdezert created this gist Nov 29, 2012.
    35 changes: 35 additions & 0 deletions TablesInSPsWithoutIndexes.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,35 @@
    SELECT *
    FROM ( SELECT SCHEMA_NAME(schema_id) + '.' + NAME AS ProcedureName,
    SUBSTRING(( SELECT DISTINCT
    ', ' + OBJDEP.NAME
    FROM sysdepends
    INNER JOIN sys.objects OBJ ON sysdepends.ID = OBJ.OBJECT_ID
    INNER JOIN sys.objects OBJDEP ON sysdepends.DEPID = OBJDEP.OBJECT_ID
    WHERE obj.type = 'P'
    AND Objdep.type IN ( 'U', 'V' ) --view and tables
    AND EXISTS ( SELECT 1
    FROM sys.indexes
    WHERE type = 2
    AND Objdep.OBJECT_ID = OBJECT_ID )
    AND sysdepends.id = procs.object_id
    FOR
    XML PATH('') ), 2, 8000) AS TablesWithNCIndexes,
    SUBSTRING(( SELECT DISTINCT
    ', ' + OBJDEP.NAME
    FROM sysdepends
    INNER JOIN sys.objects OBJ ON sysdepends.ID = OBJ.OBJECT_ID
    INNER JOIN sys.objects OBJDEP ON sysdepends.DEPID = OBJDEP.OBJECT_ID
    WHERE obj.type = 'P'
    AND Objdep.type IN ( 'U', 'V' ) --view and tables
    AND NOT EXISTS ( SELECT 1
    FROM sys.indexes
    WHERE type = 2
    AND Objdep.OBJECT_ID = OBJECT_ID )
    AND sysdepends.id = procs.object_id
    FOR
    XML PATH('') ), 2, 8000) AS TablesWithOutNCIndexes
    FROM sys.procedures procs
    WHERE procs.name NOT LIKE 'sp_%' --skip system stored procedures
    ) InnerTab
    WHERE TablesWithOutNCIndexes IS NOT NULL
    OR TablesWithNCIndexes IS NOT NULL