Search for Table in SQL Server

In organization, where huge number of tables present in database. its very hard to search the particular table.

Here few SQL queries to search the table name by pattern.

Search For Table whose name starts with A

SELECT DISTINCT [Table] = OBJECT_NAME(OBJECT_ID)
FROM SYS.INDEXES
WHERE
OBJECTPROPERTY(OBJECT_ID,'IsUserTable') = 1
AND OBJECT_NAME(OBJECT_ID) like 'A%'

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 developers are creating all the tables with Clustered Index.

SELECT DISTINCT [Table] = OBJECT_NAME(OBJECT_ID)
FROM SYS.INDEXES
WHERE
INDEX_ID = 0 AND
OBJECTPROPERTY(OBJECT_ID,'IsUserTable') = 1
List of Non clustered Tables in SQL Server 2005
List of Non clustered Tables in SQL Server 2005

Posted

in

,

by

Tags:


Related Posts

Comments

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Discover more from Jitendra Zaa

Subscribe now to keep reading and get access to the full archive.

Continue Reading