mssql查询数据库有哪些数据库

MSSQL 中可以通过查询系统视图 sys.databases 来查看数据库列表。使用 SELECT * FROM sys.databases; 语法即可。通过该视图可以查询到所有在实例中的数据库，例如默认的 master，model，msdb 和 tempdb 数据库。为了进一步研究具体的数据库信息，我们可以查询 sys.databases 视图中的各个字段，了解各个数据库的状态、创建时间、兼容性级别等相关信息。

一、MSSQL 数据库视图

在 Microsoft SQL Server（MSSQL）中，系统视图是数据库管理和维护的基础工具之一。sys.databases 视图保存了有关数据库的元数据，包括但不限于数据库名称、状态、创建时间、兼容性级别等关键信息。这使得 sys.databases 视图成为所有 DBA（数据库管理员）及开发人员查询实例中拥有的数据库的首选方式。

系统视图的重要列：

1. name – 数据库的名称
2. database_id – 数据库的唯一标识符
3. state_desc – 数据库的状态，如 ONLINE、OFFLINE、RESTORING 等
4. create_date – 数据库创建的时间
5. compatibility_level – 显示数据库的兼容性级别

具体查询语句为：

SELECT 

    name, 
    database_id, 
    state_desc, 
    create_date, 
    compatibility_level 
FROM 
    sys.databases;

这条 SQL 语句返回了实例中所有数据库的基本信息，能够快速概览当前实例中的所有数据库。

二、默认数据库详解

MSSQL 默认安装会自带四个系统数据库，分别是 master、model、msdb 和 tempdb。每个数据库都有其特定的功能和用途。

master 数据库：

master 数据库是最重要的系统数据库之一，它包含了所有系统级信息，例如登录账户、链接服务器的信息、配置设置等。保持 master 数据库的完整性对于 SQL Server 实例的稳定运行至关重要。若 master 数据库损坏，整个 SQL Server 实例将可能无法启动。因此，定期备份 master 数据库非常必要。

model 数据库：

每当新建一个数据库时，SQL Server 会使用 model 数据库作为其模板。这意味着 model 数据库中的任何对象、设置都将复制到新建数据库中。仔细配置 model 数据库有助于确保所有新建数据库的设置一致。

例如，设置默认存储的大小和文件路径：

ALTER DATABASE model MODIFY FILE (NAME = 'modeldev', FILENAME = 'C:\Data\model.mdf');

msdb 数据库：

msdb 数据库主要用于 SQL Server Agent 和其他服务的调度任务，如备份、计划任务、警报等。定期清理与优化 msdb 数据库中的历史任务数据，能够确保调度系统的高效运行。如：

EXEC sp_delete_backuphistory @oldest_date = '2023-01-01';

tempdb 数据库：

tempdb 数据库用于存储临时对象，例如临时表、临时存储过程，也用于排序、哈希操作产生的中间结果。由于 tempdb 数据库频繁被访问，它的性能对整体系统性能有重要影响。合理规划 tempdb 的大小和文件数量，能够显著提升系统的响应速度。例如：

ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev', SIZE = 1024MB, FILEGROWTH = 10MB);

三、用户数据库实践

在 MSSQL 中，用户数据库是业务系统运作所依赖的核心部分。建立专业的管理和维护策略是确保数据库系统高效和稳定的关键。以下是一些重要的操作和优化建议：

数据库的创建与初始配置：

创建数据库时，应指定文件组，分配足够的初始空间，并设置合理的增长策略。这样可以避免频繁的自增长操作，进而提升性能。例如：

CREATE DATABASE SalesDB 

ON 
( NAME = SalesDB_data, 
  FILENAME = 'C:\SQLData\SalesDB_data.mdf', 
  SIZE = 50MB, 
  MAXSIZE = 500MB, 
  FILEGROWTH = 10MB )
LOG ON 
( NAME = SalesDB_log, 
  FILENAME = 'C:\SQLData\SalesDB_log.ldf', 
  SIZE = 20MB, 
  MAXSIZE = 200MB, 
  FILEGROWTH = 5MB );

备份与还原策略：

定期备份是确保数据安全和完整的重要手段。开发标准的备份策略，结合全备份、差异备份以及事务日志备份，确保数据的多层次保护。例如：

BACKUP DATABASE SalesDB TO DISK = 'C:\Backups\SalesDB_Full.bak';

BACKUP LOG SalesDB TO DISK = 'C:\Backups\SalesDB_Log.trn';

还原数据库时，确保先还原完整备份，再应用差异备份和事务日志备份，恢复到最新的时间点。

性能优化与监控：

随着数据库的使用，可能会出现性能下降的问题。定期采集性能数据，分析和调整索引策略，优化查询，可以显著提升数据库性能。例如，创建或重建索引：

CREATE INDEX IX_Sales ON SalesDB.Sales (SaleDate);

REBUILD INDEX IX_Sales;

安全与权限管理：

对于用户数据库，必须严格控制访问权限。合理配置角色和权限，确保只授予用户必要的访问权限，防止数据泄露与未授权操作。例如，创建用户并授予读权限：

CREATE USER SalesReader FOR LOGIN SalesReaderLogin;

ALTER ROLE db_datareader ADD MEMBER SalesReader;

四、数据库维护与监控

数据库维护和监控是确保 MSSQL 数据库系统稳定运行的重要环节。使用自动化的工具和脚本，能够减少手动工作量，提升效率。以下是一些实战建议：

定期检查与更新统计信息：

统计信息对于查询优化器选择最佳执行计划至关重要。定期更新统计信息可以确保查询性能。例如：

UPDATE STATISTICS SalesDB.Sales WITH FULLSCAN;

碎片整理与索引重建：

数据插入、更新和删除操作会导致索引碎片化，影响查询性能。定期进行索引重组或重建，可以保持数据库的高效运行。例如：

ALTER INDEX ALL ON SalesDB.Sales REORGANIZE;

ALTER INDEX ALL ON SalesDB.Sales REBUILD;

数据库的监控和预警：

使用 SQL Server 自带的 Management Studio，或者结合第三方监控工具，如 SolarWinds、Redgate SQL Monitor 等，可以对数据库进行全方位的监控。发现潜在问题时，即时告警，快速响应以确保系统稳定。

备份的自动化与验证：

使用 SQL Server Agent 进行定期备份任务的调度，确保数据库备份的及时性和完整性。并且，定期对备份文件进行还原测试，验证备份的有效性。例如，创建备份作业：

EXEC sp_add_job @job_name = 'DatabaseBackupJob';

EXEC sp_add_jobstep @job_name = 'DatabaseBackupJob', 
    @step_name = 'BackupDatabase', 
    @subsystem = 'TSQL', 
    @command = 'BACKUP DATABASE SalesDB TO DISK = ''C:\Backups\SalesDB_Full.bak''';
EXEC sp_add_schedule @job_name = 'DatabaseBackupJob', 
    @name = 'DailyBackup', 
    @freq_type = 1, 
    @freq_interval = 1, 
    @active_start_time = 200000;
EXEC sp_attach_schedule @job_name = 'DatabaseBackupJob', 
    @schedule_name = 'DailyBackup';
EXEC sp_add_jobserver @job_name = 'DatabaseBackupJob', 
    @server_name = @@servername;

五、云计算与数据库集成

随着云计算的普及，越来越多的企业将数据库工作负载迁移到云平台上。MSSQL 在 Azure SQL Database 和 AWS RDS 等平台上均有广泛应用。掌握云平台上的操作和优化策略，对于现代数据库管理者是至关重要的。

部署与迁移：

使用 Database Migration Service (DMS) 工具，可以方便地将本地 MSSQL 数据库迁移到 Azure 或 AWS 平台上。部署过程中要关注网络带宽、安全配置等因素，确保数据的安全无误。

云平台上的优化与成本控制：

在云平台上运行数据库需要优化资源使用。定期监控数据库性能，调整计算资源配额，减少不必要的开支。例如，调整 Azure SQL Database 的服务层级：

ALTER DATABASE SalesDB MODIFY ( SERVICE_OBJECTIVE = 'S3' );

安全管理与合规：

云端数据库的安全和合规也是重点关注对象。使用云平台提供的加密服务、身份验证和访问控制，确保数据的机密性和完整性。配置网络安全组(NSG)，限制数据库的访问范围。

总之，在 MSSQL 查询数据库、理解各个数据库的功能与优化策略、以及在日常数据库管理中采取最佳实践，能够确保数据库系统的高效、稳定和安全运行。无论是本地环境还是云平台，熟练掌握这些技巧都是成为一名优秀 DBA 的基础。

相关问答FAQs：

1. 如何在MSSQL中查询数据库？

要查询MSSQL中的数据库，可以使用以下T-SQL语句：

SELECT Name
FROM sys.databases;

这将返回系统中所有数据库的名称。

2. MSSQL数据库查询返回的信息有哪些？

MSSQL数据库查询返回的信息包括数据库的名称、数据文件和日志文件的路径、数据库的状态信息以及其他相关属性。通过查询sys.databases视图，您可以获取所有这些信息。

3. 如何列出MSSQL中所有的用户可见的数据库？

要列出所有用户可见的数据库，可以使用以下T-SQL语句：

SELECT Name
FROM sys.databases
WHERE database_id > 4; 

这将排除系统数据库，并返回用户可见的所有数据库名称。

除了以上信息外，您还可以获取有关数据库大小、创建时间、状态等更多详细信息。希望这些回答能帮到您！