Tables with no clustered indexes or no indexes at all in SQL Server databases November 18, 2013Posted by fofo in Sql Server, Sql Server 2005, SQL Server 2008, SQL Server 2012.
Tags: catalog views, clustered index
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!!!