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 firstname.lastname@example.org 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.