SQL Server System Database

First, System database table (Master)

SELECT * FROM sysaltfiles master database, save database files
SELECT * FROM Syscharsets primary database character set and sort order
SELECT * FROM sysconfigures Primary database, configuration options
SELECT * FROM syscurconfigs Primary database current configuration options
SELECT * from sysdatabases databases in the primary database server
SELECT * FROM syslanguages master database language
SELECT * FROM syslogins master database, login account information
SELECT * FROM sysoledbusers Primary database, link server login information
SELECT * FROM sysprocesses master database process
SELECT * FROM sysremotelogins master database, Telnet account
SELECT * from syscolumns per database column
SELECT * from Sysconstrains per database, limit
SELECT * from Sysfilegroups per database, filegroup
SELECT * from Sysfiles per database, file
SELECT * from Sysforeignkeys per database, external keywords
SELECT * from Sysindexs per database, index
SELECT * from Sysmenbers per database role member
SELECT * from sysobjects all database objects per database
SELECT * from Syspermissions per database, permissions
SELECT * from systypes per database, user-defined data type
SELECT * from sysusers per database, user

Second, according to the system database query to some special circumstances required results

1. Get the names and types of all the fields in a table
Select A.name as fieldname,b.type_desc,b.type,t.name as TypeName from Sys.columns a
Left join sys.objects B on a.object_id=b.object_id left join Sys.types t on a.system_type_id=t.system_type_id
where B.type = ' U ' and charindex (' UDT ', t.name,0) <=0 and charindex (' sys ', t.name,0) <=0 and b.name = ' Cf_user '
Where B.type is a type, U represents a user table and V represents a view

2. Querying field columns and descriptions for a table

SELECT T.[name] As table name, C.[name] As field name, cast (ep.[ Value] as nvarchar (()) As [Field description]
From Sys.tables as T INNER JOIN Sys.columns as C on t.object_id = c.object_id
Left JOIN sys.extended_properties as EP on ep.major_id = c.object_id
and ep.minor_id = c.column_id WHERE ep.class =1 and t.name= ' tbgexpended '

3. Query which tables exist for a field

Select A.name as Columns, b.name as TableName from syscolumns a left
Join sysobjects b on a.id = b.id Where b.type = ' U ' and a.name = ' item_category ' ORDER by b.name

SQL Server System Database