jump to navigation

Tables with no clustered indexes or no indexes at all in SQL Server databases November 18, 2013

Posted by fofo in Sql Server, Sql Server 2005, SQL Server 2008, SQL Server 2012.
Tags: ,
trackback

I have been delivering a certified course in MS SQL Server 2012 recently and I was asked a very interesting question “If there is a way to find using T-SQL tables in a database that have no clustered indexes or no indexes at all”

I have answered that this is possible and I provided with some scripts.

There a lot of tasks that we cannot perform through the GUI of SSMS.So we have to search through the object metadata to get our answers.We can query the catalog views and get all the information we want

I have installed SQL Server 2012 Enterprise edition in my machine but you can use the SQL Server 2012/2008 Express edition as well.

I am connecting to my local instance through Windows Authentication and in a new query window I type (you can copy paste)

SELECT DB_NAME(DB_ID ('AdventureWorksLT2012')) + '. ' +OBJECT_SCHEMA_NAME(t.object_ID)+ '.' +t.NAME AS
[tables without clustered index]
FROM sys.tables t
WHERE OBJECTPROPERTY(object_id, 'TableHasClustIndex')=0
AND OBJECTPROPERTY(object_id, 'IsUserTable')=1
ORDER BY [tables without clustered index]

If you execute this bit of code , you will see all the tables in the AdventureWorksLT2012 database that have not go a clustered index.

As you can see I query the sys.tables view.You can use this script to query your own database for tables without a clustered index.

If I want to find all the tables in the AdventureWorksLT2012 database or any other database for tables that have not indexes you can execute the following script.


SELECT DB_NAME(DB_ID ('AdventureWorksLT2012')) + '. ' +OBJECT_SCHEMA_NAME(t.object_ID)+ '.' + t.NAME AS
[tables_without_an_index]
FROM sys.tables t
WHERE OBJECTPROPERTY(object_id, 'TableHasIndex')=0
AND OBJECTPROPERTY(object_id, 'IsUserTable')=1
ORDER BY [tables_without_an_index]

Hope it helps!!!

Comments»

No comments yet — be the first.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: