Home > Articles > Certification > Microsoft Certification

This chapter is from the book

Using System Tables and Views

SQL Server tracks information and maintains data about every object in the system. This information is maintained in system tables that can be queried like any other tables. Of course, it helps to know which table to query because sifting through the information in the system tables can be an arduous task. Microsoft has already gone to the effort of preparing some of the most desired information. You can see this information by querying views. You can produce still other information from recordsets of data displayed by executing system stored procedures.

If you look behind the scenes into these views and procedures, you are likely to see nothing more than the system tables being queried and the information being presented in a more readable fashion.

Still, nothing beats a little bit of know-how or, in this case, know-where. There are some useful system tables that you might want to query from time to time to double-check definitions.

Getting Information from System Tables

We have already discussed most of the actual object definitions, and if you look into the master database, you can find the storage area for these definitions. Querying the master database allows you to get information from the system. Views have replaced system tables and are prefixed with sys stored object definitions. There are many system views, but the ones described in Table 3.2 are the most commonly accessed and useful for development purposes.

Table 3.2. Common System Views/Tables

Current View/Table Name

Old View/Table Name

Description

Common System Views/Tables in Every Database (Including master):

sys.columns

syscolumns

Contains a row for every column in every table, for every view, and for each parameter in a stored procedure.

sys.indexes, sys.partitions, sys.allocation_units, and sys.dm_db_partition statssys

sysindexes

Contains a row for each index and table in the database.

sys.sql_modules

syscomments

Contains entries for each view, rule, default, trigger, constraint, and stored procedure. The text column contains the original SQL definition statements.

sys.objects

sysobjects

Contains a row for each object created within a database. In tempdb only, this table includes a row for each temporary object.

sys.types

systypes

Contains a row for each system-supplied data type and each user-defined data type.

sys.database_principals

sysusers

Contains a row for each user or role in the database.

Common System Views/Tables Additionally Found in the master Database Only:

sys.server_principals

syslogins

Contains a row for each login.

sys.messages

sysmessages

Contains a row for each system error or warning that can be displayed to the user.

sys.databases

sysdatabases

Contains a row for each database on the server.

sys.dm_exec_connections, sys.dm_exec_sessions, and sys.dm_exec_requests

sysprocesses

Holds information about processes running on the server.

sys.remote_logins

sysremotelogins

Contains a row for each remote user allowed to call remote stored procedures on the server.

sys.servers

sysservers

Contains a row for each server that the current server can access as an OLE DB data source.

In SQL Server 2005, each of the tables now maps to a system view that can be queried in place of the table. Although it is possible through some advanced configuration, the system tables should not be changed directly. You should never try to modify system tables by using DELETE, UPDATE, or INSERT statements or user-defined triggers.

In SQL Server 2000 and earlier versions, these views were system tables. For backward compatibility, they are still available. Each view, however, has a replacement system view that you should use going forward. For each of the previously mentioned system tables/views, there is a replacement, as shown in Table 3.2.

It is possible to write a database management application that uses some of the information from these tables. The information from these tables is more reliable than what can be found in other resources. Many of the columns in system tables are not documented, and you should only apply those whose supporting documentation is known, so you need to be sure to refer to the documentation. You should not write applications to directly query undocumented columns.

Instead of trying to retrieve information stored in system tables, you can create applications that access the information via system stored procedures, T-SQL statements and functions, SQL Server Management Objects (SMO), Replication Management Objects (RMO), or Database API catalog functions. These components make up a published API for obtaining system information from SQL Server.

Microsoft maintains compatibility of these alternative components from version to version. The format of the system tables depends on the internal architecture of SQL Server and may change from release to release. The supporting procedure and functions, however, still accommodate the required information. Applications that directly access the undocumented columns of system tables may have to be changed in a future release.

Information Retrieval from System Stored Procedures

Many stored procedures can provide information about the state of objects. The following are some of the most common procedures to gain this information from the database engine:

  • sp_help — Provides a list of objects if no parameters are supplied. If you supply an object name or ID, it provides information about the object.
  • sp_help objecttype or sp_help_ objectytpe — Provides information about a specified type of object. You can replace objecttype with just about any SQL Server object.
  • sp_table_validation — Provides checksum or row count for a table. If you provide the checksum or row count, the procedure will validate the table against the supplied value.
  • sp_settriggerorder — Specifies the first or last trigger to fire if multiple triggers have been defined. The order of other triggers between the first and the last cannot be set or guaranteed.
  • sp_lock — Reports information about current locks that are in place.
  • sp_configure — Displays or alters server configuration settings.
  • sp_who — Provides information about a user's current logins, sessions, and processes.
  • sp_updatestats — Updates the statistics for every table in the current database.

Keep in mind that there are somewhere in the neighborhood of 1,500 stored procedures that can be used within SQL Server. Because this chapter cannot cover every one of them, the proceeding list contains the ones you are most likely to see on the 70-431 exam and/or use on a regular basis as a database administrator.

Using Dynamic Management Views and Other System Views

SQL Server provides many dynamic management views and functions. These views and functions return server state information. The best use for these views and functions is in monitoring and determining the health of a server instance. They can be useful in diagnosing problem situations and providing information to assist in performance tuning.

All dynamic management views and functions exist in the sys schema. The naming for these views and functions follows the dm_* convention. Each database contains almost 100 different dynamic management views and functions. Because of the naming convention, you can find categories of the dynamic management views by using a query of sysobjects similar to the following:

SELECT * FROM sysobjects
    WHERE NAME LIKE 'dm_db%'
SELECT * FROM sysobjects
    WHERE NAME LIKE 'dm_db_index%'
SELECT * FROM sysobjects
    WHERE NAME LIKE 'dm_fts%'

Some of the most important categories and their common views and functions are detailed in the following list:

  • dm_db_index_operational_stats — Reports current locking and access, by partition.
  • dm_db_index_usage_stats — Specifies a count of index operations and times of last occurrences.
  • dm_db_index_physical_stats — Provides index fragmentation information.
  • dm_fts_index_population — Displays status information on the index population.
  • dm_fts_active catalogs — Reports any population activity in progress.
  • dm_fts_populations_ranges —Specifies the memory address ranges in use.
  • dm_exec_query_stats — Specifies performance statistics for cached query plans.
  • dm_exec_query_plan — Displays the XML show plan for the cached query plan.
  • dm_exec_cached_plans — Specifies the currently cached execution plans held by the server.

The first time you look, the views and the results returned by the functions look peculiar at best. However, with time and experience, the views will appear normal.

Inserting Data

You must be able to accurately get data into a system, and it needs to be organized for efficient retrieval. Although initial data loading is performed by other means as time passes, new data will no doubt need to be inserted individually or in small groups of records.

Although there are many ways to insert data into an existing table, the primary coding method is by using the INSERT statement. This statement causes the data values to be inserted into an existing table as one or more rows.

Data must meet all rules and constraints that have been defined in the table schema. The type of data being inserted must be suitable input for the data types within the table definition. Data types can themselves have control mechanisms or, with the advent of common language runtime (CLR) user-defined types (UDTs), can be complete processes for checking and manipulating data as it enters the system.

Using UDT and the CLR to Control Data Input

UDTs and the CLR component offer functionality not previously available in SQL Server. In previous releases, UDTs were only a mapping of existing data types that you could place rules and defaults onto, but little else.

The functionality of CLR UDTs is powerful but should not be overused. There is a significant amount of overhead associated with CLR use. The CLR is disabled by default in a new SQL Server installation, and to use it to its full extent, you must write the .DLL assembly in a format defined by UDT standards.

Using the CLR Within Stored Procedures

In many respects, the CLR is a wonderful thing. It opens up the opportunity to use other programming languages, such as C#, Visual C++, Visual Basic, and others, to create procedures executed within the database engine. The CLR is implemented through the use of assemblies that are referred to as managed code. Managed code is executed in the CLR environment rather than directly by the operating system.

The ability to use CLR objects within SQL Server is disabled by default upon SQL Server installation; this is partially because of the overhead involved in its use. The CLR need not be enabled in the majority of database environments. The deployment of CLR is not likely to achieve widespread use, at least initially.

The CLR will be more efficient than T-SQL in many instances. Managed code will outperform T-SQL in situations where there is use of procedural code, computation, and string manipulation. The CLR will perform better than T-SQL in any process that is computing intensive. The CLR should not be used to perform data access; T-SQL, which is specifically designed for interaction with the database engine, performs data access more efficiently than the CLR.

Not all calculations perform better in a CLR environment than with T-SQL. Managed code is moderately slower than built-in SQL Server aggregate functions, but it outperforms any cursor-based aggregation.

Inserting Individual Records

Data inserted must meet the parameters defined by the table structure. This means that NOT NULL columns must have data provided either through input or through the use of column definitions that provide for their own values. A column can obtain its input value through a DEFAULT, IDENTITY, formula, timestamp, or default object.

When inserting data, you specify the VALUES keyword to supply the data. VALUES is required unless you are using INSERT/SELECT, SELECT INTO, or EXECUTE. The following example shows the addition of a single record, using VALUES for each field:

INSERT INTO Customers
      VALUES('H99999', 'Jillier and Jergenson', 'Special',
                 'John Smith', NULL, NULL, '123 Mill Street',
                  NULL, NULL, 'US', 'Miami', 'FL', '27622',
                  '292-782-6378', NULL, NULL, NULL, 0, NULL)

Many of these fields have no value supplied, and it might be easier and neater to provide a field list with INSERT, as follows:

INSERT INTO Customers
              (CUSTNMBR, CUSTNAME, CUSTCLAS,
               CNTCPRSN, ADDRESS1, COUNTRY,
               CITY, STATE, ZIP, PHONE1, INACTIVE)
     VALUES('H99999', 'Jillier and Jergenson', 'Special',
                'John Smith', '123 Mill Street', 'US',
                'Miami', 'FL', '27622', '292-782-6378', 0)

These two statements have the same result when adding a single record to the table.

Using a Query to Insert Complete Recordsets

The SELECT INTO statement can perform a data insertion and create the table for the data in a single operation. The new table is populated with the data provided by a FROM clause. The SELECT INTO statement creates a new table with a structure identical to that of the columns provided in the query. It then copies all data that meets the WHERE condition into this newly created table. It is possible to combine data from several tables or views into one table, and you can use a variety of sources. The following example creates a new table within a database that contains only two columns:

SELECT FirstName + ' ' + LastName
     AS 'Employee Name',
                Title
     INTO HRTable
     FROM Employees

The INTO clause creates a table, so it is important that the table does not exist when you're using the command. If you want to add data to an existing table, you must perform an INSERT INTO operation. You can use a SELECT statement within the INSERT statement to add values to a table from one or more other tables or views. Using a SELECT subquery is another mechanism that enables more than one row to be inserted at one time. This type of INSERT statement is often used to insert data into a separate table from some other table or data source. In this manner, the data can be copied or just separated for handling of exceptions or specialty tasks.

For example, imagine that you would like to copy all your current employees into a customer table to enable them to make purchases and, of course, allow for an employee discount. The query to perform this operation might look similar to the following:

INSERT INTO Customers
       SELECT EmployeeID, 'TOMORA Systems',
              'Employee', FirstName + ' ' + LastName,
              'N/A', 'INTERNAL', Address, NULL, NULL,
              Country, City, Region, PostalCode,
               HomePhone, NULL, NULL, NULL, 0, NULL
          FROM Employees

The SELECT list of the subquery must match the column list of the INSERT statement. If no column list is specified, the SELECT list must match the columns in the table or view being inserted into, as in the example. Note that NULL has been provided for several fields as a placeholder for columns in which there is no data.

You can use the INSERT SELECT statement to insert data from any viable source. This includes SQL Server tables and views, as well as sources outside SQL Server. Often, the operation is used in more involved procedures to move data to and from temporary tables or table variables.

Temporary tables exist only during the duration of the procedure, so they need to be loaded during the process and offloaded before the procedure ends. A temporary table is defined using the # prefix for local temporary tables that are accessible only to the immediate scope/batch or ## for global temporary tables that are accessible outside the current batch. These are used less often because SQL Server now has a table data type that can be used for this purpose.

Disabling Functionality During Data Insertion

At times, you might want to disable indexes, triggers, constraints, and other objects to improve performance and prevent errors from occurring while loading data. This is particularly useful when loading large amounts of data in a bulk or batch format.

Disabling Indexes

Disabling an index puts the index to sleep and prevents the system from accessing it until it is enabled again. The index definition remains in the system catalog. To see the status of an index, you can query the is_disabled column in the sys.indexes catalog view. The DISABLE INDEX feature is new to SQL Server 2005 and is therefore very likely to be on the exam.

It really makes sense to disable only nonclustered indexes. Disabling a clustered index prevents access to the data. The data remains in the B-tree and must be dropped or rebuilt to correct the situation. Disabling an index on a view physically deletes the data associated with the index.

If a table is in a transactional replication publication, you cannot disable any indexes that are associated with primary key columns. These indexes are required by replication. To disable an index, you must first drop the table from the publication.

You use the ALTER INDEX REBUILD statement or the CREATE INDEX WITH DROP_EXISTING statement to enable an index. You cannot rebuild a disabled clustered index when the ONLINE option is set to ON. For more information, see the information on DISABLE INDEX in SQL Server Books Online.

Disabling Trigger Firing

Disabling a trigger does not drop the trigger. The definition of the trigger still exists as an object in the current database. A disabled trigger does not fire when any T-SQL statements on which it was programmed are executeds. This applies to both DDL and DML triggers.

To disable a trigger, you simply use the DISABLE TRIGGER statement. To turn it back on again, you use ENABLE TRIGGER.

Disabling Constraint Checking

You can use ALTER TABLE with the NOCHECK CONSTRAINT clause to disable a constraint. While a constraint is disabled, the system allows for an insertion of data that would typically violate the constraint. You might want this in rare situations, but it would corrupt most systems.

Using ALTER TABLE with CHECK CONSTRAINT turns the constraint back on again.

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

Overview


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information


To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.

Surveys

Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.

Newsletters

If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information


Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.

Security


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

Children


This site is not directed to children under the age of 13.

Marketing


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information


If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.

Choice/Opt-out


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information


Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents


California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure


Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.

Links


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact


Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice


We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020