SQL SERVER – 2005 Find Table without Clustered Index – Find Table with no Primary Key

One of the basic Database Rule I have is that all the table must Clustered Index. Clustered Index speeds up performance of the query ran on that table. Clustered Index are usually Primary Key but not necessarily. I frequently run following query to verify that all the Jr. DBAs are creating all the tables with no Clustered Index.

USE AdventureWorks ----Replace AdventureWorks with your DBName
GO
SELECT DISTINCT [TABLE] = OBJECT_NAME(OBJECT_ID)
FROM SYS.INDEXES
WHERE INDEX_ID = 0
AND OBJECTPROPERTY(OBJECT_ID,'IsUserTable') = 1
ORDER BY [TABLE] GO

Result set for AdventureWorks:
TABLE
——————————————————-
DatabaseLog
ProductProductPhoto
(2 row(s) affected)

Related Post:
SQL SERVER – 2005 – Find Tables With Primary Key Constraint in Database
SQL SERVER – 2005 – Find Tables With Foreign Key Constraint in Database

Reference : Pinal Dave (https://blog.sqlauthority.com) , BOL

SQL Index, SQL Scripts
Previous Post
SQL SERVER – Change Default Fill Factor For Index
Next Post
SQL SERVER – 2005 Replace TEXT with VARCHAR(MAX) – Stop using TEXT, NTEXT, IMAGE Data Types

Related Posts

49 Comments. Leave new

  • CREATE TABLE [sim].[Simulations](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [SimulationDate] [datetime] NOT NULL,
    [ScenarioId] [int] NULL,
    [SimulationBudgetId] [int] NOT NULL,
    [ScenarioStrategies] [int] NOT NULL,
    CONSTRAINT [PK_Simulation] PRIMARY KEY CLUSTERED
    (
    [Id] ASC
    )

    Reply
    • Table has clustered index and that’s why not given in output. I would correct the text in blog

      CREATE DATABASE FOO
      GO
      USE FOO
      GO
      CREATE TABLE [Simulations](
      [Id] [int] IDENTITY(1,1) NOT NULL,
      [SimulationDate] [datetime] NOT NULL,
      [ScenarioId] [int] NULL,
      [SimulationBudgetId] [int] NOT NULL,
      [ScenarioStrategies] [int] NOT NULL,
      CONSTRAINT [PK_Simulation] PRIMARY KEY CLUSTERED
      (
      [Id] ASC
      ))
      GO
      SELECT DISTINCT [TABLE] = OBJECT_NAME(OBJECT_ID)
      FROM SYS.INDEXES
      WHERE INDEX_ID = 0
      AND OBJECTPROPERTY(OBJECT_ID,’IsUserTable’) = 1
      ORDER BY [TABLE]
      GO

      Reply

Leave a Reply