SQL Server System view [not periodically updated]

1.sys.objects: Objects within each user-defined schema scope that are created in the database (such as tables, views, constraints, defaults, logs, rule stored procedures, and so on, but not including DDL triggers) have one row in the table.

name

TD valign= "Top" width= "267" >

Column name	Description
Object name.
object_id	Object identification number. is unique in the database.
type	Object type. For example, U = table (user-defined type) UQ = UNIQUE constraint.
Type_desc	Description of the object type. For example, Unique_constraint, user_table
create_date	The date the object was created.
modify_date	Last use ALTE The R statement modifies the date of the object. If the object is a table or view, Modify_date also changes when you create or modify a clustered index on a table or view.

2.sys.tables: Returns a row for each user table in SQL Server (the user table in the Sys.objects table is listed separately, contains all the columns in the sys.objects, and adds several columns).

3.sys.columns: Returns a row for each column of the object that contains the column, such as a view or table.

Column Name	Description
object_id	The ID of the object to which this column belongs.
Name	The column name, which is unique within the object.
column_id	The ID of the column, which is unique within the object. Column IDs can be sorted out of order.
Is_identity	1 = column has an identity value.

4.sys.extended_properties: Returns a row for each extended property in the current database.

Column Name	Description
Class	Identifies the item class on which the attribute exists. For example, 1 = object or column.
Class_desc	The description of the class on which the extended attribute exists. For example, Object_or_column.
major_id	The ID of the item on which the extended property exists, as explained by the item class. For most items, the ID applies to the item represented by the class. If class is 0, then major_id is always 0. If class is 1, 2, or 7, then major_id is object_id.
minor_id	The secondary ID of the item on which the extended attribute exists, as explained by the item class. For most items, the ID is 0; otherwise, the ID is one of the following: if class = 1, then minor_id equals column_id If the item is a column equals 0 in case the item is an object. If class = 2, then minor_id is parameter_id. If class = 7, then minor_id is index_id.
Name	The name of the property, whose class, major_id, and minor_id are unique.
Value	The value of the extended property.

Note: The table is descriptive information for all columns or primary columns.

Usage Examples :

1. View the user table information:

Select      table name =name,    creation time =create_date,     modified time =  Modify_date  from Sys.tables-- or sys.objects where type= ' U ' 

2. View the field description information for the user table:

SelectTable name= Case  whencol.column_id=1  ThenTab.nameElseN"' End, field name=col.name, field description=IsNull(Ext.value,n"') fromsys.tables TabJoinsys.columns Col ontab.object_id=Col.object_id    Joinsys.extended_properties ext onCol.object_id=ext.major_id andcol.column_id=ext.minor_id

SQL Server System view [not periodically updated]