Get the all the tables on the server which do not have Clustered index

  • Comments posted to this topic are about the item Get the all the tables on the server which do not have Clustered index

    Regards,
    Mitesh OSwal
    +918698619998

  • Nice script.

    Works relatively quick, and could be used for troubleshooting performance issues.

    The script was initially failing on all databases with the spaces in their names (common for the SharePoint databases). Need to bracket database names in joins.

    See below corrected:

    ---Verify if any table dnt have the cluster key

    DECLARE @tbl TABLE

    (

    ID INT IDENTITY(1,1),

    DBName NVARCHAR(100),

    TableName NVARCHAR(100)

    )

    DECLARE @SQL NVARCHAR(MAX)

    SELECT @SQL = '

    SELECT ''?'',ts.Name

    FROM [?].sys.tables ts

    LEFT JOIN [?].sys.indexes si

    ON ts.object_id = si.object_id

    AND si.type = 1

    INNER JOIN [?].sys.databases d

    ON d.Name=''?'' AND d.name NOT IN ( ''tempdb'',''master'',''msdb'',''ReportServer'')

    WHERE si.index_id IS NULL

    '

    INSERT INTO @tbl

    EXECUTE sp_MSforeachdb @SQL

    select * from @tbl

    Alex Donskoy

    SQL DBA Mimai FL.

  • Thanks for posting this script. I took what you did and applied it to another SQL I got here that looked for values in stored procedures. I don't remember who posted that to give them credit, the other one looped through for each database on the server. But here is what I came up with:

    /* This will search for the value in @SearchText in stored procedures

    across all databases on the server being run. */

    DECLARE @SQL VARCHAR(8000)

    DECLARE @SearchText NVARCHAR(255)

    SET @SearchText = 'text to search for here'

    DECLARE @Results TABLE

    (

    [SERVERNAME] VARCHAR(255),

    [DBName] VARCHAR(255),

    [NAME] VARCHAR(255),

    XTYPE VARCHAR(255)

    )

    ;

    SELECT @SQL =

    'SELECT DISTINCT @@SERVERNAME, ''?'' AS [DBName], so.[name], so.xtype

    FROM ?.dbo.sysobjects so WITH(NOLOCK)

    inner join ?.dbo.syscomments sc on

    so.id = sc.id

    INNER JOIN ?.sys.databases d ON

    d.Name=''?''

    AND d.name NOT IN ( ''tempdb'',''master'',''msdb'',''ReportServer'')

    WHERE sc.text like ''%'+ @SearchText + '%'''

    INSERT INTO @Results

    EXEC sp_MSforeachdb @SQL

    SELECT *

    FROM @Results

    ORDER BY DBName, XType, [Name]

    ;

    -------------------------------------------------------------
    we travel not to escape life but for life not to escape us
    Don't fear failure, fear regret.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply