Microsoft SQL Server ships with some information schema views that are very helpful for getting information about the meta-data. And Microsoft suggests that you use these views instead of querying the system tables like systypes, sysreferences, sysindexez, since these tables can change from release to release. I personally prefer to have a good knowledge of the system tables and occasionally use them for database administration purposes. However, for the application, it's good if your SQL queries are based on these views so you are sure that when you migrate from one version of SQL Server to the other, you won't be breaking something. All these information_schema views exist in SQL Server 2000 and detailed information on all of them is available in BOL so I won't go into details of what each view does. Here is the list of the views:

• Information_Schema.Check_Constraints
• Information_Schema.Column_Domani_Usage
• Information_Schema.Column_Privleges
• Information_Schema.Columns
• Information_Schema.Constraint_Column_Usage
• Information_Schema.Constraint_Table_Usage
• Information_Schema.Domain_Constraints
• Information_Schema.Domains
• Information_Schema.Key_Column_Usage
• Information_Schema.Parameters
• Information_Schema.Referential_Constraints
• Information_Schema.Ruotine_Columns
• Information_Schema.Routines
• Information_Schema.Schemata
• Information_Schema.Table_Constraints
• Information_Schema.Table_Constraints
• Information_Schema.Table_Privileges
• Information_Schema.Tables
• Information_Schema.Views
• Information_Schema.View_Column_Usage
• Information_Schema.View_Table_Usage

Examples of information_schema Views

1) For getting a list of constraints, column_names, and their position in the constraint, use the following:

SELECT COLS.CONSTRAINT_NAME,COLS.COLUMN_NAME,COLS.ORDINAL_POSITION
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS COLS
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS CONS ON 
COLS.CONSTRAINT_NAME = CONS.CONSTRAINT_NAME
WHERE COLS.CONSTRAINT_CATALOG = DB_NAME()
AND COLS.TABLE_NAME = 'TASK_DTL'
AND CONS.CONSTRAINT_TYPE = 'PRIMARY KEY'
ORDER BY COLS.CONSTRAINT_NAME, COLS.ORDINAL_POSITION

2) You can replace the name of the table with whatever you want and also replace the constraint_type if you want to search for constraints other than the Primary Key.

   SELECT CONSTRAINT_NAME,
        COLUMN_NAME,
        ORDINAL_POSITION
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE CONSTRAINT_CATALOG = DB_NAME()
AND TABLE_NAME = 'X'
ORDER BY CONSTRAINT_NAME, ORDINAL_POSITION

Subsitute x with the name of the table, and you will get the name of the constraints defined on the table (Primary Key as well as the Foreign key constraints), the names of the columns involved, and the position of the columns in the constraint (for example, which column is number 1 in the case of a covered primary key).

3) For a list of tables, their columns, data-type for the columns, NULL/Not NULL criteria:

SELECT A.TABLE_NAME, B.COLUMN_NAME, B.DATA_TYPE, B.IS_NULLABLE 
FROM INFORMATION_SCHEMA.TABLES A, INFORMATION_SCHEMA.COLUMNS B
WHERE A.TABLE_NAME = B.TABLE_NAME
ORDER BY A.TABLE_NAME

However, at times you will need to use the system tables as well like if you want to get detailed information about the foreign keys and their associations, information_schema views may not suffice. For example, the following sql script reports about the foreign keys in the system and all information about them (their status, the parent and the child columns):

SELECT  (CASE
        WHEN OBJECTPROPERTY(CONSTID, 'CNSTISDISABLED') = 0 THEN 'ENABLED'
        ELSE 'DISABLED'
        END) AS STATUS, 
        OBJECT_NAME(CONSTID) AS CONSTRAINT_NAME,
        OBJECT_NAME(FKEYID) AS TABLE_NAME, 
        COL_NAME(FKEYID, FKEY) AS COLUMN_NAME,
        OBJECT_NAME(RKEYID) AS REFERENCED_TABLE_NAME,
        COL_NAME(RKEYID, RKEY) AS REFERENCED_COLUMN_NAME
FROM SYSFOREIGNKEYS
ORDER BY  TABLE_NAME, CONSTRAINT_NAME,REFERENCED_TABLE_NAME,  KEYNO
GO

So, it depends on you and your needs, but whenever possible, using information_schema views is recommended over the system tables.

Obtaining System Information

SQL Server 2000 introduced a new function called SERVERPROPERTY(), which can be used for getting property information about the server instance.

The following sql script can be used for finding the different options selected during the installation of SQL Server 2000:

SELECT   CONVERT(char(50), SERVERPROPERTY('COLLATION')) AS COLLATION,
         CONVERT(char(20), SERVERPROPERTY('EDITION')) AS EDITION,

        (CASE WHEN CONVERT(char(20), SERVERPROPERTY('InstanceName')) IS NULL
                THEN 'DEFAULT INSTANCE'
              ELSE CONVERT(char(20), SERVERPROPERTY('InstanceName'))
         END) AS Instance_Name,

        (CASE WHEN CONVERT(char(20), SERVERPROPERTY('ISClustered')) = 1
                THEN 'CLUSTERED'
              WHEN CONVERT(char(20), SERVERPROPERTY('ISClustered')) = 0
                THEN 'NOT CLUSTERED'
              ELSE 'INVALID INPUT/ERROR'
         END) AS FAILOVER_CLUSTERED,
 
        (CASE WHEN CONVERT(char(20), SERVERPROPERTY('ISFullTextInstalled')) = 1
                THEN 'Full Text - Installed'
              WHEN CONVERT(char(20), SERVERPROPERTY('ISFulltextInstalled')) = 0
                THEN 'Full Text - NOT Installed'
              ELSE 'INVALID INPUT/ERROR'
         END) AS FULL_TEXT_INSTALLATION,
 
        (CASE WHEN CONVERT(char(20), SERVERPROPERTY('ISIntegratedSecurityOnly')) = 1
                THEN 'Integrated Security'
              WHEN CONVERT(char(20), SERVERPROPERTY('ISIntegratedSecurityOnly')) = 0
                THEN 'SQL Server Security'
              ELSE 'INVALID INPUT/ERROR'
         END) AS SECURITY,

        (CASE WHEN CONVERT(char(20), SERVERPROPERTY('ISSingleUser')) = 1
                THEN 'Single User'
              WHEN CONVERT(char(20), SERVERPROPERTY('ISSingleUser')) = 0
                THEN 'Multi User'
              ELSE 'INVALID INPUT/ERROR'
         END) AS Single_User,

        (CASE WHEN CONVERT(char(20), SERVERPROPERTY('LicenseType')) = 'PER_SEAT'
                THEN 'Per Seat Mode'
              WHEN CONVERT(char(20), SERVERPROPERTY('LicenseType')) = 'PER_PROCESSOR'
                THEN 'Per Processor Mode'
              ELSE 'Disabled'
         END) AS License_Type,

        CONVERT(char(20), SERVERPROPERTY('MachineName')) AS Machine_Name,
        CONVERT(char(20), SERVERPROPERTY('NumLicenses')) AS Number_Of_Licenses,

        /*To identify which sqlservr.exe belongs to this instance*/
        CONVERT(char(20), SERVERPROPERTY('ProcessID')) AS Process_ID, 
        /*The version of SQL Server instance in the form: major.minor.build*/ 
        CONVERT(char(20), SERVERPROPERTY('ProductVersion')) AS Product_Version,
        /*Level of the version of SQL Server Instance*/
        CONVERT(char(20), SERVERPROPERTY('ProductLevel')) AS Product_Level,
        CONVERT(char(20), SERVERPROPERTY('ServerName')) AS Server_Name

Index Information

In order to get full index information, the columns involved the order of the columns, and the types of index, you can use this T-SLQ script.

This T-SQL code will provide information about the Indexes Table_Name, Index_Name, and the name of the columns that constitute the index.

If you want to find out which tables do not have any index and to display the number of records, you can use the following SQL:

select sysobjects.name Table_Name,
(Select rows from sysindexes
 where id = sysobjects.id
 and indid = 0) Rows
from
 sysobjects
where
 xtype = 'u'
 and objectproperty(sysobjects.id, 'TableHasIndex') = 0

--

Rahul Sharma is a senior database administrator for Manhattan Associates, Inc., and a columnist for both Swynk.com and SQLServerCentral.com. He has been working with Microsoft SQL Server since the release of SQL Server 6.5 and is currently working with both SQL Server 2000 and Oracle 9i. He is a Microsoft Certified Professional with more than six years of experience in database administration. His latest book is Microsoft SQL Server 2000.