1. 库管理

1.1 登录数据库

mssql-cli -S 100.1.1.1,1433 -U userName -P password

1.2 查看数据库列表

• 通过mssql-cli 工具查看
  \ld 查看数据库列表
  \ld xxx 查看包含xxx字符串的数据库

select name from master…sysdatabases order by name

• 通过DQL查看
  是否添加master均可
  select name from master…sysdatabases order by name
  select name from sysdatabases order by name;

• 查看sysdatabases
  select * from sysdatabases;

1.3 切换库

在mssql-cli中
use databasename

1.4 系统数据库

• master
  filename: E:\SQLDATA\MSSQL\DATA\master.mdf
  表：

+----------------+------------------+
| table_schema   | table_name       |
|----------------+------------------|
| dbo            | spt_fallback_db  |
| dbo            | spt_fallback_dev |
| dbo            | spt_fallback_usg |
| dbo            | spt_monitor      |
+----------------+------------------+

• tempdb
  filename: E:\SQLDATA\MSSQL\DATA\tempdb.mdf

+----------------+--------------+
| table_schema   | table_name   |
|----------------+--------------|
| dbo            | #A0A823EA    |
| dbo            | #BFB3FFB1    |
+----------------+--------------+

• model
  filename: E:\SQLDATA\MSSQL\DATA\model.mdf
  无权限查看里面的表

• msdb
  filename：E:\SQLDATA\MSSQL\DATA\MSDBData.mdf
  表

+----------------+-----------------------------------+
| table_schema   | table_name                        |
|----------------+-----------------------------------|
| dbo            | DTA_tuninglog                     |
| dbo            | DTA_reports_database              |
| dbo            | DTA_reports_partitionfunction     |
| dbo            | DTA_reports_partitionscheme       |
| dbo            | DTA_reports_table                 |
| dbo            | DTA_reports_tableview             |
| dbo            | DTA_reports_query                 |
| dbo            | DTA_reports_querytable            |
| dbo            | DTA_reports_querydatabase         |
| dbo            | DTA_reports_index                 |
| dbo            | DTA_reports_queryindex            |
| dbo            | DTA_reports_column                |
| dbo            | DTA_reports_indexcolumn           |
| dbo            | DTA_reports_querycolumn           |
| dbo            | sysproxies                        |
| dbo            | sysproxysubsystem                 |
| dbo            | sysproxylogin                     |
| dbo            | sysjobhistory                     |
| dbo            | dm_hadr_automatic_seeding_history |
| dbo            | sysjobs                           |
| dbo            | backupmediaset                    |
| dbo            | sysjobservers                     |
| dbo            | backupmediafamily                 |
| dbo            | sysjobactivity                    |
| dbo            | backupset                         |
| dbo            | sysjobsteps                       |
| dbo            | sysjobstepslogs                   |
| dbo            | backupfile                        |
| dbo            | sysschedules                      |
| dbo            | restorehistory                    |
| dbo            | restorefile                       |
| dbo            | restorefilegroup                  |
| dbo            | sysjobschedules                   |
| dbo            | logmarkhistory                    |
| dbo            | DTA_input                         |
| dbo            | suspect_pages                     |
| dbo            | syscategories                     |
| dbo            | DTA_progress                      |
| dbo            | sysalerts                         |
| dbo            | sysoperators                      |
| dbo            | sysnotifications                  |
| dbo            | DTA_output                        |
+----------------+-----------------------------------+

-rdscore
filename：E:\SQLDATA\MSSQL\DATA\rdscore.mdf
表：无

2.表管理

2.1 查看表

• mssql-cli中
  \lt

2.2 DQL查看表

select name from sysobjects where xtype=‘U’ order by name
select * from sysobjects where xtype=‘U’ order by name;
XType=‘U’:表示所有用户表; XType=‘S’:表示所有系统表;

2.3 获取指定表的信息

Select
a.Name as Caption, --列名
(CASE WHEN (Select count(*) FROM sysobjects Where
(name in (Select name FROM sysindexes Where (id = a.id)
AND (indid in (Select indid FROM sysindexkeys Where (id = a.id)
AND (colid in (Select colid FROM syscolumns Where (id = a.id)
AND (name = a.name)))))))
AND (xtype = 'PK' ))>0 then 1 else 0 end) as IsPrimaryKeyMember, --主键
b.name as DataType, --数据类型
a.Length as [Size], --长度
(case when a.isnullable=1 then 1 else 0 end) as AllowDBNull, --允许空
isnull(e.text,'') as DefaultValue, --默认值
isnull(g.[value],'' ) AS 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
Where d.name='Person'
orDER BY a.id,a.colorder

3. 列管理

3.1 获取指定表的列

select name from syscolumns where id=Object_Id(‘TableName’)