Microsoft SQL Server 2008 SQL Fundamentals (Note one-Query metadata)

 

 Summary

1.       Catalog view:

·         sys.tables

·         sys.columns

2.       Scheam:INFORMATION_SCHEAM

·         ~.tables

·         ~.columns

3.       Stored Procedure

·         Sys.sp_tables

·         Sys.sp_help

·         Sys.sp_columns

·         Sys.sp_helpconstraint

4.       Function

·         SERVERPROPERTY

·         DATABASEPROPERTY

·         OBJECTPROPERTY

·         COLUMNPROPERTY 

CatalogView:

 

CatalogView provide very detailed information about objects in the database.

 The function SCHEMA_NAME(schema_id) is used to convert the integer scheam ID to its name.

                eg. SELECT SCHEMA_NAME(schema_id) as table_schema_name, name As table_name FROM sys.tables;

 The function TYPE_NAME(system_type_id) is used to convert the integer type ID to its name.

 

              eg. SELECT

                       name As column_name,

                       TYPE_NAME(system_type_id) AS column_type,

                       max_length,

                       collation_name,

                       is_nullable

                     FROM sys.columns

                     WHERE object_id = OBJECT_ID(N'Sales.Orders');

Information Schema Views:

 

Information schema views are a set of views that reside in a schema called INFORMATION_SCHEAM and provide metadata information in a standard manner.

For example, INFORMATION_SCHEMA.TABLES  INFORMATION_SCHEMA.COLUMNS

                Eg. SELECT TABLE_SCHEMA, TABLE_NAME

                       FROM INFORMATION_SCHEAM.TABLES

                       WHERE TABLE_TYPE = N’BASE TABLE’;

                Eg. SELECT

                           COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,

                           COLLATION_NAME, IS_NULLABLE

                          FROM INFORMATION_SCHEMA.COLUMNS

                          WHERE TABLE_SCHEMA = N’Sales’

                                 AND TABLE_NAME = N ‘Orders’;

System Stored procedures and Functions

The sp_tables stored procedure returns a list of objects(such as tables and views) that can be queried in the current database.

EXEC sys.sp_help @objectName = N’Sales.Orders’;

The sp_columns procedure returns information about columns in an object.

EXEC sys.sp_columns @table_name = N’Orders’, @table_owner  = N’Sales”;

The sp_helpconstraint procedure returns information about constraints in an object. For example the following code return information about constraints in the Orders table.

EXEC sys.sp_helpconstraint @objectName=N’Sales.Orders’;

One set of functions return information about properties of entities such as SQL Server instance, database object, column, and so on. The SERVERPROPERTY function returns the requested property of the current instance. For example, the following code returns the product level (Such as RTM, SP1, SP2, and so on) of current instance:

SELECT SERVERPROPERTY(‘ProductLevel’);

The DATABASEPROPERTYEX function returns the requested property of the given database name. For example the following code returns the collation of the TSQLFundamentals2008 database:

SELECT DATABASEPROPERTYEX(N’TSQLFundamentals2008’, ‘Collation’);

The OBJECTPROPERTY function returns the requested property of the given object name. For example, the output of the following code indicates whether the Orders table has a primary key:

SELECT  OBJECTPROPERTY(OBJECT_ID(N'Sales.Orders'), 'TableHasPrimaryKey');

Notice the nesting of the function OBJECT_ID within OBJECTPROPERTY. The OBJECTPROPERTY function expects an object ID and not a name, so the OBJECT_ID function is used to return the ID of the Orders table.

The COLUMNPROPERTY function returns the requested property of a given column. For example, the output of the following code indicates whether the shipcountry column in the Orders table is NULLable:

SELECT  COLUMNPROPERTY(OBJECT_ID(N'Sales.Orders'), N'shipcountry', 'AllowsNull');