search
HomeDatabaseMysql TutorialMS SQL 日常维护管理常用脚本(一)

MS SQL 日常维护管理常用脚本(一)

Jun 07, 2016 pm 05:45 PM
Commonly useddailymanagemaintainScript

SQL SERVER 数据库日常维护,管理,巡检过程中你可能经常需要用到一些SQL语句(亦或方法)来查看数据库服务器环境(操作系统版本, 磁盘空间,CPU,RAM信息),数据库信息(数据库版本,实例名称...),数据库对象等。 查看数据库信息 查看数据库服务器名称 方

    SQL SERVER 数据库日常维护,管理,香港服务器租用,巡检过程中你可能经常需要用到一些SQL语句(亦或方法)来查看数据库服务器环境(操作系统版本, 磁盘空间,CPU,虚拟主机,RAM信息),数据库信息(数据库版本,实例名称...),数据库对象等。

查看数据库信息

 

查看数据库服务器名称

  方法1:SQL脚本查询,可以通过下面脚本来查询。

  默认实例

默认实例查询

  命名实例

命名实例查询

 

  方法2:在数据库实例单击右键,选择“属性”——》“常规”选项里面,香港服务器,你可以看到服务器名称(划红线部分)

clip_image002

 

  方法3:都不好意思说了,你懂的。

 

查看数据库实例名称

  方法1:去服务(services.msc)里面查找SQL Server(××××)这样的服务,有多少个就就有多少数据库实例,一般默认实例是SQL Server (MSSQLSERVER)

  方法2:去SQL配置管理器的SQL Server服务配置里面找上面描述的服务。

  方法3:脚本查询,只是截取数据库服务名称的实例名(其实这个还真没必要,通过上面的脚本就可查看实例,注意默认实例)

Code Snippet

   下面脚本仅对命名实例有效,默认实例查询处理的是计算机名称

Code Snippet

 

查看数据库版本号

方法1:SQL 1:

Code Snippet

方法2:SQL 2: 看起来比较麻烦

Code Snippet

  Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)   Nov 24 2008 13:01:59

  Copyright (c) 1988-2005 Microsoft Corporation         

Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2) 

如何从 9.00.4035.00 (Intel X86) 读取一些信息呢?

  第一个数字:8 代表2000版本, 9 代表2005版本, 10 代表2008 版本

  第二个数字:50 代表R2版本, 00 代表非R2版本

  第三个数字:如上所示4035中第一个数字4代表打了补丁SP3,其规则如下所示

      1: 代表没有打补丁

      2: 代表打了SP1补丁

      3: 代表打了SP2补丁,以此类推

      035 : 代表相关的hotfix版本,我们在进行升级,备份,恢复一定要看清楚这个版本,只有在同一版本下的相关数据才能进行恢复(同版本备份文件),这点要注意。

方法3:在数据库实例上单击右键,选择“属性”——》“常规”选项里面,你可以看到服务器名称,平台,操作系统,数据库版本信息。

 

查看数据库已经打的补丁

方法1:如上所示,可以通过9.00.4035.00 (Intel X86) 来确定已经数据库已经打的最新补丁SP3。

方法2:在数据库实例上单击右键,选择“属性”——》“常规”选项里面,可以通过看到的版本信息查看补丁

方法3:在SQL配置管理器,找到相应的实例的SQL Server服务,单击右键属性.

clip_image004

 

查看实例数据库的相关信息

查看实例有哪些数据库,创建时间、排序规则.......

  方法1:SQL 查询. 其实在视图sys.databases里面你可以查看很多关于数据库的信息,例如,数据库用户访问设置,数据库的状态......

  SELECT * FROM sys.databases

  方法2: 在Mircrosoft SQL Server Management Studio管理器里面查看。

查看排序规则信息

1:查看实例排序规则

    SELECT SERVERPROPERTY(N'Collation')

2:查看数据库排序规则

    SELECT name, collation_name FROM sys.databases

查询当前数据库的磁盘使用情况

如需要查询其他数据库,则需在前面指定数据库名称

    EXEC sp_spaceused;

查看数据库启动相关参数

    EXEC sp_configure;

查看数据库启动时间

方法1:

Code Snippet

  • 30120
  • 1
  • 查看所有数据库名称及大小

    方法1:

      EXEC sp_helpdb;

    方法2:

    Code Snippet

     

    查看所有数据库用户登录信息

      EXEC sp_helplogins;

    查看所有数据库用户所属的角色信息

      EXEC sp_helpsrvrolemember

    更改某个数据对象的用户属主

      sp_changeobjectowner [@objectname =] 'object', [@newowner =] 'owner'

    Statement
    The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
    Explain the InnoDB Buffer Pool and its importance for performance.Explain the InnoDB Buffer Pool and its importance for performance.Apr 19, 2025 am 12:24 AM

    InnoDBBufferPool reduces disk I/O by caching data and indexing pages, improving database performance. Its working principle includes: 1. Data reading: Read data from BufferPool; 2. Data writing: After modifying the data, write to BufferPool and refresh it to disk regularly; 3. Cache management: Use the LRU algorithm to manage cache pages; 4. Reading mechanism: Load adjacent data pages in advance. By sizing the BufferPool and using multiple instances, database performance can be optimized.

    MySQL vs. Other Programming Languages: A ComparisonMySQL vs. Other Programming Languages: A ComparisonApr 19, 2025 am 12:22 AM

    Compared with other programming languages, MySQL is mainly used to store and manage data, while other languages ​​such as Python, Java, and C are used for logical processing and application development. MySQL is known for its high performance, scalability and cross-platform support, suitable for data management needs, while other languages ​​have advantages in their respective fields such as data analytics, enterprise applications, and system programming.

    Learning MySQL: A Step-by-Step Guide for New UsersLearning MySQL: A Step-by-Step Guide for New UsersApr 19, 2025 am 12:19 AM

    MySQL is worth learning because it is a powerful open source database management system suitable for data storage, management and analysis. 1) MySQL is a relational database that uses SQL to operate data and is suitable for structured data management. 2) The SQL language is the key to interacting with MySQL and supports CRUD operations. 3) The working principle of MySQL includes client/server architecture, storage engine and query optimizer. 4) Basic usage includes creating databases and tables, and advanced usage involves joining tables using JOIN. 5) Common errors include syntax errors and permission issues, and debugging skills include checking syntax and using EXPLAIN commands. 6) Performance optimization involves the use of indexes, optimization of SQL statements and regular maintenance of databases.

    MySQL: Essential Skills for Beginners to MasterMySQL: Essential Skills for Beginners to MasterApr 18, 2025 am 12:24 AM

    MySQL is suitable for beginners to learn database skills. 1. Install MySQL server and client tools. 2. Understand basic SQL queries, such as SELECT. 3. Master data operations: create tables, insert, update, and delete data. 4. Learn advanced skills: subquery and window functions. 5. Debugging and optimization: Check syntax, use indexes, avoid SELECT*, and use LIMIT.

    MySQL: Structured Data and Relational DatabasesMySQL: Structured Data and Relational DatabasesApr 18, 2025 am 12:22 AM

    MySQL efficiently manages structured data through table structure and SQL query, and implements inter-table relationships through foreign keys. 1. Define the data format and type when creating a table. 2. Use foreign keys to establish relationships between tables. 3. Improve performance through indexing and query optimization. 4. Regularly backup and monitor databases to ensure data security and performance optimization.

    MySQL: Key Features and Capabilities ExplainedMySQL: Key Features and Capabilities ExplainedApr 18, 2025 am 12:17 AM

    MySQL is an open source relational database management system that is widely used in Web development. Its key features include: 1. Supports multiple storage engines, such as InnoDB and MyISAM, suitable for different scenarios; 2. Provides master-slave replication functions to facilitate load balancing and data backup; 3. Improve query efficiency through query optimization and index use.

    The Purpose of SQL: Interacting with MySQL DatabasesThe Purpose of SQL: Interacting with MySQL DatabasesApr 18, 2025 am 12:12 AM

    SQL is used to interact with MySQL database to realize data addition, deletion, modification, inspection and database design. 1) SQL performs data operations through SELECT, INSERT, UPDATE, DELETE statements; 2) Use CREATE, ALTER, DROP statements for database design and management; 3) Complex queries and data analysis are implemented through SQL to improve business decision-making efficiency.

    MySQL for Beginners: Getting Started with Database ManagementMySQL for Beginners: Getting Started with Database ManagementApr 18, 2025 am 12:10 AM

    The basic operations of MySQL include creating databases, tables, and using SQL to perform CRUD operations on data. 1. Create a database: CREATEDATABASEmy_first_db; 2. Create a table: CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY, titleVARCHAR(100)NOTNULL, authorVARCHAR(100)NOTNULL, published_yearINT); 3. Insert data: INSERTINTObooks(title, author, published_year)VA

    See all articles

    Hot AI Tools

    Undresser.AI Undress

    Undresser.AI Undress

    AI-powered app for creating realistic nude photos

    AI Clothes Remover

    AI Clothes Remover

    Online AI tool for removing clothes from photos.

    Undress AI Tool

    Undress AI Tool

    Undress images for free

    Clothoff.io

    Clothoff.io

    AI clothes remover

    Video Face Swap

    Video Face Swap

    Swap faces in any video effortlessly with our completely free AI face swap tool!

    Hot Tools

    WebStorm Mac version

    WebStorm Mac version

    Useful JavaScript development tools

    EditPlus Chinese cracked version

    EditPlus Chinese cracked version

    Small size, syntax highlighting, does not support code prompt function

    PhpStorm Mac version

    PhpStorm Mac version

    The latest (2018.2.1) professional PHP integrated development tool

    DVWA

    DVWA

    Damn Vulnerable Web App (DVWA) is a PHP/MySQL web application that is very vulnerable. Its main goals are to be an aid for security professionals to test their skills and tools in a legal environment, to help web developers better understand the process of securing web applications, and to help teachers/students teach/learn in a classroom environment Web application security. The goal of DVWA is to practice some of the most common web vulnerabilities through a simple and straightforward interface, with varying degrees of difficulty. Please note that this software

    SublimeText3 English version

    SublimeText3 English version

    Recommended: Win version, supports code prompts!