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.


I guess you came to this post by searching similar kind of issues in any of the search engine and hope that this resolved your problem. If you find this tips useful, just drop a line below and share the link to others and who knows they might find it useful too.

Stay tuned to my blog, twitter or facebook to read more articles, tutorials, news, tips & tricks on various technology fields. Also Subscribe to our Newsletter with your Email ID to keep you updated on latest posts. We will send newsletter to your registered email address. We will not share your email address to anybody as we respect privacy.

This article is related to

Sql Server,Sql Server 2005,SQL Server 2008,SQL Server 2012,catalog views,clustered index