In the past days, I have found a lot of people asking themselves, 'how do I know in which tables I can find xyz column name, and how do I know I have all the tables?' The answer to this question is often used to make database wide updates and perform a number of maintenance functions for master records and transactions that were not recorded as originally intended. The following example query attempts to solve the issue by exposing all tables were the account index (ACTINDX) column is found within the Fabrikam database (TWO).
The results are as shown below:
By checking the rowcount in the sysindexes table, we are ensuring that we focus our efforts in those tables that contain data. Very helpful, isn't it?
SQL Server 2005 and SQL Server 2008
If working with Microsoft SQL Server 2005 or above, the query can be simplified as follows:
Until next post!
Mariano Gomez, MVP
UPDATES TO THIS ARTICLE:
12/13/2008: Modified SQL query to look at system views and change article formatting.
01/08/2009: Added SQL Server 2005 and 2008 query to INFORMATION_SCHEMA