SQL server2008 system table details sys. table at the beginning

How many tables, table names, etc. in the library

Select  from Information_schema. TABLES

Second, query table, field, type, whether primary key, length, decimal place, whether to allow empty, default value and so on detailed query

Effect:

SELECT ( CaseWhen a.colorder=1Then D.nameElse "'End asTable name,--returns an empty a.colorder if the table name is the same asfield ordinal, A.name asfield name, ( CaseWhen ColumnProperty (A.id,a.name,'isidentity')=1Then'√'Else "'End asidentification, ( CaseWhen (SELECT count (*) from sysobjects--Query primary key WHERE (nameinch(SELECT name from sysindexes WHERE (ID= a.ID) and (indidinch(SELECT indid from Sysindexkeys WHERE ( ID= a.ID) and (colidinch(SELECT colid from syscolumns WHE RE (ID= a.id) and (name =A.name))))) and (Xtype='PK')) >0Then'√' Else "'End asPrimary KEY,--Query primary key end B.name astype, A.length asthe number of bytes consumed, columnproperty (A.id,a.name,'PRECISION') aslength, IsNull (ColumnProperty (A.id,a.name,' Scale'),0) asNumber of decimal digits, ( CaseWhen a.isnullable=1Then'√'Else "'End asallow null, IsNull (E.text,"') asdefault value, IsNull (G.[value],"'As field description from syscolumns a LEFT join systypes B on A.xtype=b.xusertype INNER join sysobjects D on a.id=d.id and D.xtype='U'and d.name<>'dtproperties'Left join syscomments e on A.cdefault=E.id left Join Sys.extended_properties G on a.id=g.major_id and A.colid =g.minor_id ORDER BY A.id,a.colorder

Third, system table:

Sys.allocation_units	Each allocation unit in the database occupies a row in the table.
Sys.assembly_modules	Returns a row for each function, procedure, or trigger defined by the common language Runtime (CLR) assembly. This catalog view maps CLR stored procedures, CLR triggers, or CLR functions to its underlying implementation. Objects of type TA, AF, PC, FS, and FT have an associated assembly module. To find an association between an object and an assembly, you can join this catalog view to a different catalog view. For example, when you create a CLR stored procedure, the stored procedure is represented by a row in sys.objects, a row in Sys.procedures (inherited from Sys.objects), and a row in Sys.assembly_modules. The stored procedure itself is represented by metadata in sys.objects and Sys.procedures. A reference to the underlying CLR implementation of the procedure can be found in sys.assembly_modules.
Sys.check_constraints	Each object that is a CHECK constraint (sys.objects.type = ' C ') has a corresponding row.
Sys.columns	Returns a row for each column of an object that contains columns, such as a view or table. The following is a list of object types that contain columns. Table-Valued assembly function (FT) Inline table-Valued SQL functions (IF) Internal tables (IT) system table (S) Table-Valued SQL functions (TF) User table (U) View (V)
Sys.computed_columns	Each computed column in the sys.columns corresponds to a row.
Sys.default_constraints	Each object that is defined as the default and Sys.objects.type = D corresponds to a row in the table, which is created as part of the CREATE TABLE or ALTER table statement, not as part of the CREATE DEFAULT statement.
Sys.events	One row for each event that causes a trigger or event notification to fire. These events represent the type of event specified when creating a trigger or event notification using create TRIGGER or creation event NOTIFICATION.
Sys.event_notifications	Returns a row for each object that is notified as an event, and Sys.objects.type = EN.
Sys.extended_procedures	One row for each object that is an extended stored procedure and sys.objects.type = X. Because extended stored procedures are installed in the master database, they are only visible in the context of the database. Selecting from the Sys.extended_procedures view in any other database context returns an empty result set.
Sys.foreign_key_columns	Each column or set of columns that make up the foreign key corresponds to a row in the table.
Sys.foreign_keys	Each object that is constrained as FOREIGN KEY and Sys.object.type = F occupies a row in the table.
Sys.function_order_columns	Returns a row for each column that is part of the common language runtime (CLR) table-valued function ORDER expression.
Sys.identity_columns	Each column that is used as an identity column occupies one row in the table.
Sys.index_columns	Each column that belongs to a sys.indexes index or an unordered table (heap) corresponds to one row.
Sys.indexes	The index or heap of each table object (for example, table, view, or table-valued function) contains one row.
Sys.key_constraints	One row for each object that is a primary key or a unique constraint. Includes Sys.objects.type PK and UQ.
Sys.numbered_procedure_parameters	Each parameter with a numbered procedure corresponds to a row in the table. When you create a numbered stored procedure, the base procedure is numbered 1. The numbering of all subsequent processes is 2, 3, and so on. Sys.numbered_procedure_parameters contains the parameter definitions for all subsequent procedures (numbers greater than or equal to 2). The view does not display parameters for the base stored procedure (number = 1). A base stored procedure is similar to a stored procedure that is not numbered. Therefore, its parameters are represented in sys.parameters (Transact-SQL).
Sys.numbered_procedures	Each SQL Server stored procedure that is created as a numbered procedure occupies one row. For a base (number = 1) stored procedure, it does not display rows. The items of the base stored procedure can be found in views such as sys.objects and Sys.procedures.
Sys.objects	Each user-defined schema-scoped object created in the database corresponds to a row in that table.
Sys.parameters	Each parameter of the object that accepts the parameter corresponds to a row in the table. If the object is a scalar function, another row indicates the return value. The row will have a parameter_id value of 0.
Sys.partitions	Each partition in the database for all tables and most types of indexes corresponds to a row in the table. Special index types such as Fulltext, Spatial, and XML are not included in this view. All tables and indexes in SQL Server 2008 contain at least one partition, regardless of whether they have been explicitly partitioned.
Sys.procedures	To the same process and Sys.objects.type = P, X, RF, and a single row for each object of the PC.
Sys.service_queues	Each object in the database that acts as a service queue (Sys.objects.type = SQ) corresponds to a row in the table.
Sys.spatial_index_tessellations	Represents information about the tessellation scheme and parameters for each spatial index.
Sys.spatial_indexes	Represents the primary index information for a spatial index.
Sys.sql_dependencies	There is a row for each dependency of the referenced entity that is referenced in a Transact-SQL expression or statement that defines another reference object.
Sys.sql_expression_dependencies	Each per-name dependency on a user-defined entity in the current database has a row in this table. A dependency between two entities is created when an entity called a referenced entity is displayed by name in a persisted SQL expression of another entity called a reference entity. For example, when referencing a table in a view definition, the view that is referencing the entity will depend on the referenced entity of the table. If you delete the table, the view is not available. You can use this catalog view to report dependency information for the following entities: The entity that is bound to the schema. A non-schema-bound entity. Cross-database and cross-server entities. The entity name is reported, but the entity ID has not yet been resolved. The column-level dependencies of the entity that is bound to the schema. You can use Sys.dm_sql_referenced_entities to return column-level dependencies for objects that are not schema-bound. Server-level DDL triggers (when in the context of the master database).
Sys.sql_modules	Returns a row for the module object defined for each SQL language. The objects of type P, RF, V, TR, FN, IF, TF, and R all have an associated SQL module. In this view, the independent default value, the object of type D, also has the SQL module definition. For descriptions of these types, see type columns in the sys.objects catalog view.
Sys.stats	Each statistic for a Table object of type U, V, or TF corresponds to one row.
Sys.stats_columns	The sys.stats statistic contains a row for each column.
Sys.synonyms	In this view, Sys.objects.type = Each synonym object for SN corresponds to one row.
Sys.table_types	Displays the properties of the user-defined table type. A table type is a type from which a table variable or table-valued parameter cannot be declared. Each table type contains type_table_object_id, which is a foreign key that points to the Sys.objects catalog view. This ID can be used to query various catalog views, similar to the object_id of a regular table, in order to discover the structure of a table type, such as columns, constraints, and so on.
Sys.tables	Returns a row for each Table object, currently used only for table objects that are sys.objects.type = U.
Sys.trigger_event_types	Returns a row for each event or group of events that can fire a trigger.
Sys.trigger_events	Each event that triggers the trigger corresponds to one row.
Sys.triggers	Each trigger object of type TR or TA corresponds to one row. The DML trigger name is within the schema scope, so it can be displayed in sys.objects. The scope of the DDL trigger name depends on the parent entity and can only be displayed in this view. The Parent_class and name columns uniquely identify the triggers in the database.
Sys.views	Each View object of Ys.objects.type = V corresponds to a row in the table.

SQL server2008 system table details sys. table at the beginning