SQL Server数据库日常维护命令与脚本 1. 查看数据库信息
查看数据库服务器名称
-- 默认实例 SELECT @@SERVERNAME AS SERVERNAME; SELECT SERVERPROPERTY('servername') AS ServerName; SELECT srvname AS ServerName FROM sys.sysservers; SELECT SERVERPROPERTY('MachineName') AS ServerName; -- 命名实例 SELECT SUBSTRING(@@SERVERNAME, 0, CHARINDEX('\', @@SERVERNAME))AS SERVERNAME; SELECT SUBSTRING(CONVERT(VARCHAR(100),SERVERPROPERTY('servername')), 0, CHARINDEX('\',CONVERT(VARCHAR(100),SERVERPROPERTY('servername')))) AS ServerName; SELECT SUBSTRING(srvname, 0, CHARINDEX('\', srvname)) AS ServerName FROM sys.sysservers; SELECT SERVERPROPERTY('MachineName') AS ServerName;
查看数据库实例名称
SELECT @@SERVICENAME AS InstantName; SELECT ISNULL(SERVERPROPERTY('InstanceName'),'MSSQLSERVER') AS InstanceName; -- 只对命名实例有效 SELECT SUBSTRING(@@SERVERNAME,CHARINDEX('\', @@SERVERNAME)+1,100) AS InstantName; SELECT SUBSTRING(srvname, CHARINDEX('\', srvname) +1, 100) AS InstantName FROM sys.sysservers;
查看数据库版本号
SELECT SERVERPROPERTY('productversion') AS ProductVersion , SERVERPROPERTY('productlevel') AS ProductLevel , SERVERPROPERTY('edition') AS Edition; SELECT @@VERSION AS PRODUCT_VERSION;
查看数据库实例
SELECT * FROM sys.databases;
查看排序规则信息
-- 实例排序规则 SELECT SERVERPROPERTY(N'Collation'); -- 数据库排序规则 SELECT name, collation_name FROM sys.databases;
查询当前数据库的磁盘使用情况
EXEC sp_spaceused; -- 查看某个数据对象的大小 EXEC sp_spaceused @objname;
查看数据库启动的相关参数
EXEC sp_configure;
查看服务器启动时间
SELECT CONVERT(VARCHAR(30), LOGIN_TIME,120) AS StartDateTime FROM master..sysprocesses WHERE spid=1;
查看所有数据库名称与大小
-- 方法1 EXEC sp_helpdb; -- 方法2 SELECT database_id AS DataBaseId , DB_NAME(database_id) AS DataBaseName , CAST(SUM(SIZE) * 8.0 / 1024 AS DECIMAL(8, 4)) AS [Size(MB)] FROM sys.master_files GROUP BY database_id;
查看数据库的用户和进程信息
-- 查看所有 EXEC sp_who; -- 查看活动用户和进程 EXEC sp_who 'active';
查看所有数据库用户登录信息
EXEC sp_helplogins;
查看所有数据库用户所属的角色信息
EXEC sp_helpsrvrolemember;
更改某个数据对象的用户属主
注意：更改对象名的任一部分都可能破坏脚本和存储过程。把一台服务器上的数据库用户登录信息备份出来可以用add_login_to_aserver脚本。查看某数据库下,对象级用户权限sp_helprotect
EXEC sp_changeobjectowner [@objectname =] 'object', [@newowner =] 'owner'
查看链接服务器
EXEC sp_helplinkedsrvlogin;
查看远端数据库用户登录信息
EXEC sp_helpremotelogin;
查看某数据库下某个数据对象的索引信息
EXEC sp_helpindex @objname;
查看某数据库下某个数据对象的的约束信息
EXEC sp_helpconstraint @objname;
查看表的相关信息
EXEC sp_help 'TABLE_NAME';
查看数据库数据文件情况
-- 查看数据库实例各个数据库的数据文件信息 SELECT database_id AS DataBaseId, DB_NAME(database_id) AS DataBaseName, Name AS LogicalName, type_desc AS FileTypeDesc, Physical_Name AS PhysicalName, State_Desc AS StateDesc , CASE WHEN max_size = 0 THEN N'不允许增长' WHEN max_size = -1 THEN N'自动增长' ELSE LTRIM(STR(max_size * 8.0 / 1024 / 1024, 14, 2)) + 'G' END AS MaxSize , CASE WHEN is_percent_growth = 1 THEN RTRIM(CAST(Growth AS CHAR(10))) + '%' ELSE RTRIM(CAST(Growth AS CHAR(10))) + 'M' END AS Growth , Is_Read_Only AS IsReadOnly , Is_Percent_Growth AS IsPercentGrowth , CAST(size * 8.0 / 1024 / 1024 AS DECIMAL(8, 4)) AS [Size(GB)] FROM sys.master_files; -- 查看单个数据库的数据文件信息 SELECT Name AS DataBaseName, Physical_Name AS PhysicalName, type_desc AS FileTypeDesc, State_Desc AS StateDesc, (( size * 8.0 ) / 1024 / 1024 ) AS [Size(GB)], CASE WHEN max_size = 0 THEN N'不允许增长' WHEN max_size = -1 THEN N'自动增长' ELSE LTRIM(STR(max_size * 8.0 / 1024 / 1024, 14, 2)) + 'G' END AS MaxSize , CASE WHEN is_percent_growth = 1 THEN RTRIM(CAST(Growth AS CHAR(10))) + '%' ELSE RTRIM(CAST(Growth AS CHAR(10))) + 'M' END AS Growth , Is_Read_Only AS IsReadOnly , Is_Percent_Growth AS IsPercentGrowth , CAST(size * 8.0 / 1024 / 1024 AS DECIMAL(8, 4)) AS [Size(GB)] FROM sys.database_files; -- 通过数据库名称查看数据文件 SELECT fileid AS FileId, groupid AS GroupId, size AS DataBaseSize, growth AS Growth, perf AS Perf, name AS NAME, filename AS FILENAME FROM <DatabaseName>.dbo.sysfiles ;
查看数据库服务器各数据库日志文件的大小及利用率/状态
DBCC SQLPERF(LOGSPACE); 或 EXEC ('DBCC SQLPERF(LOGSPACE)');
查看当前数据库的文件状态
EXEC ('DBCC showfilestats');
查看数据库存储过程
-- 方法1 EXEC sp_stored_procedures; -- 方法2 SELECT * FROM sys.procedures; -- 方法3 SELECT * FROM sys.sysobjects WHERE xtype='P';
查看存储过程基本信息
EXEC sp_help 'sp_who';