SQL Server common system tables

1. View tables and views
SELECT * from sys.objects WHERE object_id = object_id (' table name or view name ')
In SQL Server, you can generally find the results by querying the sys.objects system tables, but there are more convenient ways
As follows:
If object_id (' tb_table ') is not null
print ' exist '
Else
print ' not exist '
As above, you can use OBJECT_ID () to quickly achieve the same purpose, Tb_table is the name of the resource I will create, so I want to first determine that the current database does not exist the same resource
OBJECT_ID () can accept two parameters, the first one as shown above, represents the name of the resource, above is the name of the table, but often we want to explain what kind of resources we want to create,
This allows SQL to explicitly look for duplicate names in a type of resource, as follows:
If object_id (' tb_table ', ' u ') is not null
print ' exist '
Else
print ' not exist '

The second parameter "U" means that tb_table is a user-created table, that is: user_table initials
Query sys.objects can get the type name of various resources (type column), here are a few main examples
U-----------User-created tables, different from system tables (user_table)
S-----------system table (system_table)
V-----------Views (view)
P-----------Stored Procedure (sql_stored_procedure)
2. View the column name of the table

SELECT * from syscolumns where id=object_id (' table name ') and name= ' column name '

3. View the database

SELECT * from sys.databases where name = ' Student '

4. View Backup devices

"SELECT * from sys.backup_devices where name = ' backupdb '

(Add backup device: EXEC sp_addumpdevice ' disk ', ' backupdb ', ' D:\\backup\\backupdb.bak ')

5. View data files

Select name, Physical_name dbfile from sys.master_files where database_id=db_id (' student ')
The sysdatabases table in the master database (is the library name for all databases in SQL Server):
If you are querying all databases (user/System):
SELECT * FROM Master. sysdatabases
If you are querying a user database, use:
SELECT * FROM Master. sysdatabases where dbid > 4
If you are querying a system database, simply change the WHERE clause to dbid < 4.

6. View the SQL statements executed

SELECT Cacheobjtype,objtype,usecounts,sql
From sys.syscacheobjects where the SQL not is like '%cach% ' and SQL not like '%sys.% '

2. The sysobjects table in the corresponding database (which responds to all the objects in the corresponding database):
The use of this table is greatest, because it contains: Table (System/user), stored procedure (System/user), view, primary key, foreign key, etc.
There is no explanation here.
For example, you have a database named Test in SQL Server, if you want to query all the table objects in that database:
SELECT * FROM Test: sysobjects WHERE type = ' u ' or type = ' s '
Here the type = ' u ' is the user Table object, type = ' s ' is the system table object, how to query the user Table object individually or the system table object is needless to say.
If you want to query the stored procedures are also divided between the system and the user:
This is the query for all stored procedures in the database
SELECT * FROM Test: sysobjects where type = ' P '
If you are querying a user stored procedure then add and category <> 2.
Querying the system stored procedures is needless to say (category = 2 who does not know ...) ）
Query views are the same as query stored procedures, such as:
You are querying all the views in the database
SELECT * FROM Test: sysobjects where type = ' V '
If you are querying the user view then add and category <> 2.
The others will not say, just put their type to everyone:
Type = ' PK ' represents the primary key.
Type = ' d ' foreign key reference.
Type = ' UQ ' unique index.
The following appendix some common system tables

Name Address description
Sysaltfiles the primary database to save the database file
Syscharsets primary database character set and sort order
Sysconfigures Primary Database configuration options
Syscurconfigs Primary Database Current configuration options
sysdatabases databases in the primary database server
Syslanguages Primary Database language
syslogins Main Database login account information
Sysoledbusers Primary Database link Server login information
sysprocesses master database process
Sysremotelogins master database telnet account

syscolumns each database column
Sysconstrains per database limit
Sysfilegroups per database filegroup
Sysfiles per database file
Sysforeignkeys each database external keyword
Sysindexs per database index
Sysmenbers each database role member
Sysobjects all database objects per database
Syspermissions per database permissions
Systypes per database user-defined data type
sysusers per database user

SQL Server common system tables