Sql-server – Cannot Check Cluster Index Sql Server

clustered-indexindexsql server

I am just check if my table has Cluster Index or not by running this query

Table Design

CREATE TABLE [dbo].[postTB](
[postID] [bigint] IDENTITY(1,1) NOT NULL,
[slug] [nvarchar](300) NULL,
[title] [varchar](150) NOT NULL,
[postContent] [nvarchar](max) NULL,
[createDate] [datetime] NULL,
[publishDate] [datetime] NULL,
[userID] [bigint] NOT NULL,
[categoryID] [bigint] NOT NULL,
[isShow] [bit] NOT NULL,
[isApproved] [bit] NULL,
[websiteID] [int] NULL,
[miniPostContent] [nvarchar](300) NULL,
[modifedDate] [datetime] NULL,
[pageTitle] [nvarchar](300) NULL,
[pageKeyword] [nvarchar](300) NULL,
[pageDescribtion] [nvarchar](300) NULL,
[CombinedTags] [nvarchar](300) NULL,
[blogImage] [nvarchar](50) NULL,
[postBlogType] [nvarchar](50) NULL,
[postTag] [nvarchar](100) NULL,
[firstname] [nvarchar](50) NULL,
[lastname] [nvarchar](50) NULL,
[contactemail] [nvarchar](50) NULL,
[viewcount] [bigint] NULL CONSTRAINT [DF_postTB_viewcount_1]  DEFAULT ((1)),
[blogCategoryTypeID] [int] NULL CONSTRAINT [DF_postTB_blogCategoryTypeID]  DEFAULT ((0)),
CONSTRAINT [PK_postTB] PRIMARY KEY CLUSTERED 
(
[postID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
 ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

I am executing this

Execute sp_helpindex [postTB]

But the result is

The object 'postTB' does not exist in database 'cwpros' or is invalid for this operation.

Please help how to properly create cluster if not exist then check using above command?

Best Answer

Seems that either you didn't create your table on the database you are currently querying with sp_helpindex or the creation statement failed (or somehow it was dropped).

I tested the create script and it worked for me, however you should check if the table exists or not (maybe you didn't execute it, or did on another instance or database). Use this to check if the table has been created or not (assuming you are using SSMS):

USE YourDatabaseNameHere -- Put the database name where the table should be here
GO

DECLARE @tableName VARCHAR(100) = 'postTB'

SELECT SCHEMA_NAME(t.schema_id) AS SchemaName, t.* 
FROM sys.tables t 
WHERE t.name LIKE '%' + @tableName + '%' ORDER BY t.name

Result:

SchemaName  name    object_id   principal_id    schema_id   parent_object_id    type    type_desc   create_date             modify_date             is_ms_shipped ...
dbo         postTB  1890105774  NULL            1           0                   U       USER_TABLE  2018-07-24 09:08:38.463 2018-07-24 09:08:38.463 0             ...

You should see a row with your table's info. If you don't, execute the create statement on the database and run the query again. Troubleshoot if it fails.

Then either use the procedure you mentioned:

USE YourDatabaseNameHere
GO

EXEC sp_helpindex '[dbo].[postTB]' -- Make sure that the schema is placed before

Or use this query to check all indexes massively:

DECLARE @table_name_like VARCHAR(200) = 'postTB' -- partial table name here

SELECT
     SchemaName = SCHEMA_NAME(t.schema_id),
     TableName = t.name,
     IndexName = ind.name,
     IndexType = CASE ind.index_id WHEN 0 THEN 'Heap' WHEN 1 THEN 'Clustered' ELSE 'Nonclustered' END,
     Disabled = ind.is_disabled,
     ColumnOrder = ic.index_column_id,
     ColumnName = col.name,
     ColumnType = y.name,
     ColumnLength = y.max_length,
     ColumnIncluded = ic.is_included_column
FROM 
    sys.indexes ind 
    INNER JOIN sys.index_columns ic ON  ind.object_id = ic.object_id and ind.index_id = ic.index_id 
    INNER JOIN sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id 
    INNER JOIN sys.tables t ON ind.object_id = t.object_id 
    INNER JOIN sys.types y ON y.user_type_id = col.user_type_id
WHERE 
     t.is_ms_shipped = 0 AND
     t.name LIKE '%' + @table_name_like + '%'
ORDER BY
    SchemaName,
     t.name, 
     ind.name, 
     ic.index_column_id

Result:

SchemaName  TableName   IndexName   IndexType   Disabled    ColumnOrder ColumnName  ColumnType  ColumnLength    ColumnIncluded
dbo         postTB      PK_postTB   Clustered   0           1           postID      bigint      8               0
Related Question