Tip

Using system tables to your advantage

As database developers we often have to work with the data tables that are not familiar to us. It's not uncommon to take over someone else's responsibilities, due to your colleague finding another job, or your company having to layoff a few employees. You can also imagine a situation when you have to compare the database tables in the production environment with their counterparts on development, quality assurance and testing servers. If any of these describes your job responsibilities, you need to learn the architecture of the SQL Server system tables and how to query them effectively. In this article I show you how to use some of the most frequently accessed system tables: sysobjects, syscolumns and sysindexes. I will show you several examples of how these tables can help you do your job.

Suppose your boss comes to you at 8AM and asks you to determine which table contains employee contact information. The report she's trying to generate references emp_last_name, emp_first_name and emp_middle_name columns. The database has over 500 tables, so you don't want to query each one of them. Instead, you can run the following query:

SELECT a.name, b.name 
FROM syscolumns a INNER JOIN sysobjects b ON
a.id = b.id
AND
b.type = 'u'
WHERE a.name LIKE '%emp_last_name%'

The sysobjects table contains one row per each database object, including user tables, system tables, constraints, views, stored procedures, etc. I specified type = 'u' in the join because I wanted to find return only the user tables. The syscolumns table, on the other hand contains a row for each table column, view column and a row for each stored procedure parameter. I further constrained the query with a WHERE clause to only bring back those tables that have a column name similar to "emp_last_name". Granted, there could be multiple tables containing such columns, but this is a good start for your investigation.

If you're comparing databases on two different servers, one of the first requirements is checking the number of rows in each table on both servers. The script below queries the sysobjects system table to get names of each user table. The script then generates a dynamic SQL statement to get the count of rows for each user table:

SET NOCOUNT ON
DECLARE @table VARCHAR(80), @sql VARCHAR(200)
DECLARE table_cursor CURSOR FOR 
SELECT name FROM sysobjects WHERE type = 'u' 
ORDER BY 1
OPEN table_cursor
FETCH NEXT FROM table_cursor INTO @table
WHILE @@FETCH_STATUS = 0
BEGIN
 SELECT 'number of rows in ' + @table
 SELECT @sql = 'SELECT COUNT(*) FROM ' + @table
 EXEC(@sql)
FETCH NEXT FROM table_Cursor INTO @table
END
CLOSE table_cursor
DEALLOCATE table_cursor

Some results (in the PUBS database):

---------------------------------------- 
number of rows in authors

            
----------- 
23

                                                                                                   
------------------------------------ 
number of rows in discounts

            
----------- 
3
?

Yet another frequent requirement is to produce a report of all indexes created on your database tables. The following script generates such report by querying sysobjects and sysindexes tables. The sysindexes table contains one row for each index and table in the database. Notice that if indid column of sysindexes has a value of 0, then that row represents the user table. The value of indid = 1 denotes a clustered index. The values between 2 and 255 (the upper limit on the total number of indexes per table) denote a non-clustered index. Since, the report generated by this script only cares about indexes, it does not return any rows with indid = 0. In addition, the report informs the user if an index is clustered or not.

Examine the inline comments for additional details:

/* Declare variables to use in this example. */
DECLARE @id INT, 
@msg VARCHAR(2000), 
@indid SMALLINT, 
@indname SYSNAME, 
@status INT,
@indkey INT, 
@name VARCHAR(30)

/* Turn off counting of the returned rows: */
SET NOCOUNT ON
-- outer loop:
DECLARE table_cursor CURSOR FOR
SELECT id, name FROM sysobjects 
WHERE type = 'u' 
ORDER BY 2

OPEN table_cursor
FETCH NEXT FROM table_cursor INTO @id, @name

WHILE @@FETCH_STATUS = 0
BEGIN 
SELECT 'Indexes for ' + @name + ' table:'

/* Loop through all indexes in each table.
** If indid = 0 it's the table itself, not an index
** Therefore, don't bother bringing it back
*/

 DECLARE index_cursor CURSOR FOR 
 SELECT indid, name, status 
 FROM sysindexes 
 WHERE id = @id
 AND
 indid <> 0 
 
 OPEN index_cursor
   
 FETCH NEXT FROM index_cursor INTO @indid, @indname, @status
        
    WHILE @@FETCH_STATUS = 0 
      BEGIN
   
      SET @msg = NULL
     /* Print the index name and the index number.*/
    /* If the indid = 1 then it's a clustered index
    ** provide such info in the output
    */
 IF @indid = 1
 BEGIN
      SET @msg = ' Index number '  + CONVERT(varchar, @indid)+ 
       ' is '+@indname + '. This is a clustered index on:'
 END
 ELSE
 BEGIN
     SET @msg = ' Index number '  + CONVERT(varchar, @indid)+ 
       ' is '+@indname + '. This is a non-clustered index on:' 
 END
 
      SET @indkey = 1
      
        WHILE @indkey <= 16 and INDEX_COL(@name, @indid, @indkey)
       IS NOT NULL
   
       BEGIN
       -- Print different information if @indkey <> 1.
         IF @indkey = 1
 BEGIN
 
          SET @msg = @msg + ' ' 
             + index_col(@name, @indid, @indkey) 
 END
         ELSE
          SET @msg = @msg + ', ' 
             + index_col(@name, @indid, @indkey)  
          
         SET @indkey = @indkey + 1
       END
   
       PRINT @msg          
       SET @msg = NULL
       FETCH NEXT FROM index_cursor INTO @indid, @indname, @status
   
    END
    CLOSE index_cursor
    DEALLOCATE index_cursor
FETCH NEXT FROM table_cursor INTO @id, @name
END
CLOSE table_cursor
DEALLOCATE table_cursor

If you execute this script in PUBS database, which is created during SQL Server installation, you'll get results similar to the following:

------------------------------------------------- 
Indexes for authors table:

 Index number 1 is UPKCL_auidind. This is a clustered index on: au_id
 Index number 2 is aunmind. This is a non-clustered index on: au_lname, au_fname
                                                  

------------------------------------------------- 
Indexes for employee table:

 Index number 1 is employee_ind. This is a clustered index on: lname, fname, minit
 Index number 2 is PK_emp_id. This is a non-clustered index on: emp_id
                                                  
------------------------------------------------- 
Indexes for jobs table:

 Index number 1 is PK__jobs__117F9D94. This is a clustered index on: job_id
------------------------------------------------- 
Indexes for titleauthor table:

 Index number 1 is UPKCL_taind. This is a clustered index on: au_id, title_id
 Index number 2 is auidind. This is a non-clustered index on: au_id
 Index number 3 is titleidind. This is a non-clustered index on: title_id
 Index number 4 is _WA_Sys_royaltyper_03317E3D. This is a non-clustered index on: royaltyper

Most of this (abbreviated) output should be fairly easy to decipher. However, one quick word of caution is in order. If you see an index in the output with a name similar to WA_SYS% it's a pseudo-index, generated by SQL Server automatically. So don't spend hours searching for that index in the actual table. SQL Server automatically generates and maintains pseudo-indexes on frequently accessed columns to optimize the performance. Of course, you could easily turn off reporting of such indexes by changing the second loop declaration in the above script as follows:

DECLARE index_cursor CURSOR FOR 
 SELECT indid, name, status 
 FROM sysindexes 
 WHERE id = @id
 AND
 indid <> 0 
 AND
 name NOT LIKE '_wa_sys%'

In this article I gave you a quick introduction to the most frequently queried system tables. There are numerous system tables that you might have to work with depending on your duties and assignments. Remember though, that SQL Server operates based on the information in system tables, so be careful not to brake anything. Microsoft strongly recommends not modifying these tables directly. Please be a good citizen and take this recommendation seriously.

For More Information

  • What do you think about this tip? E-mail the Editor at tdichiara@techtarget.com with your feedback.
  • The Best SQL Server Web Links: tips, tutorials, scripts, and more.
  • Have an SQL Server tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
  • Ask your technical SQL Server questions--or help out your peers by answering them--in our live discussion forums.
  • Check out our Ask the Experts feature: Our SQL, Database Design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.

This was first published in January 2002

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.