MS SQL 日常维护管理常用脚本

--【查看数据库服务器名称】
--默认实例查询
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;

--【查看数据库版本号】
--方法1
SELECT SERVERPROPERTY('productversion')  AS ProductVersion,
       SERVERPROPERTY('productlevel')    AS ProductLevel,
       SERVERPROPERTY('edition')         AS Edition
          
--方法2
SELECT @@VERSION AS PRODUCT_VERSION;

--【查看实例数据库的相关信息】
SELECT *
FROM   sys.databases
  
  
 --【 查看排序规则信息】
 -- 1：查看实例排序规则 
SELECT SERVERPROPERTY(N'Collation')

 -- 2：查看数据库排序规则 
SELECT NAME,
       collation_name
FROM   sys.databases
    
--【查询当前数据库的磁盘使用情况】
--如需要查询其他数据库，则需在前面指定数据库名称
EXEC sp_spaceused;
   
   --【查看数据库启动相关参数】 
EXEC sp_configure;
    
    
    
    --【查看数据库启动时间】
SELECT CONVERT(VARCHAR(30), LOGIN_TIME, 120) AS StartDateTime
FROM   MASTER..sysprocesses
WHERE  spid = 1

--【查看所有数据库名称及大小】
EXEC sp_helpdb;

--【查看所有数据库用户登录信息】
EXEC sp_helplogins;
  
  --【查看所有数据库用户所属的角色信息】
EXEC sp_helpsrvrolemember
    
   --【查看链接服务器】
EXEC sp_helplinkedsrvlogin
     
     --【查看远端数据库用户登录信息】
     sp_helpremotelogin
     
     --【查看数据库下某个数据对象的大小】
     sp_spaceused @objname
     
     --查看某数据库下某个数据对象的索引信息 
     sp_helpindex @objname
     
     --【查看某数据库下某个数据对象的的约束信息】 
     sp_helpconstraint @objname
     
     --【查看表的相关信息】
     sp_help 'TABLE_NAME'
  
  
 --【查看数据库服务器各数据库日志文件的大小及利用率/状态】 
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 'dbo.sp_who_lock'


--【查看存储过程源代码：】 
--方法1：
EXEC sp_helptext 'procedureName'

--方法2：
SELECT *
FROM   SYS.SQL_MODULES
WHERE  OBJECT_ID = OBJECT_ID(N'procedureName')
    
--方法3：
SELECT s.text                      AS ProcedureText,
       s.encrypted                 AS Encrypted,
       s.number                    AS number,
       CONVERT(NCHAR(2), o.xtype)  AS xtype,
       DATALENGTH(s.text)          AS ProcedureLen
FROM   dbo.syscomments s,
       dbo.sysobjects                 o
WHERE  o.id = s.id
       AND s.id = OBJECT_ID(N'rpt_TrainingPlan')
ORDER BY
       s.number,
       s.colid
       OPTION(ROBUST PLAN)
       
 
 --【查看数据库所在机器操作系统参数】      
EXEC MASTER..xp_msver
  
  --【查看数据库服务器磁盘分区剩余空间】
EXEC MASTER.dbo.xp_fixeddrives;
   
 --【查看数据库服务器CPU/内存的大概信息】  
SELECT cpu_count                      AS [Logical CPU Count],
       hyperthread_ratio              AS [Hyperthread Ratio],
       cpu_count / hyperthread_ratio  AS [Physical CPU Count],
       physical_memory_in_bytes / 1048576 AS [Physical Memory (MB)],
       sqlserver_start_time
FROM   sys.dm_os_sys_info
       OPTION(RECOMPILE);

参考二：

http://www.cnblogs.com/Leo_wl/p/3155124.html

--【查看数据库用户信息】
SELECT *
FROM   sysusers;

--【查看最大工作线程数】 
SELECT max_workers_count
FROM   sys.dm_os_sys_info
  
--【查看当前用户进程的会话ID】
SELECT @@SPID

--【查询当前会话使用哪种协议】  
SELECT net_transport
FROM   sys.dm_exec_connections
WHERE  session_id = @@SPID;

--查看当前连接的会话信息
--进程号1--50是SQL Server系统内部用的
SELECT *
FROM   sys.dm_exec_sessions
WHERE  session_id >= 51

--查看某台机器的连接会话信息
SELECT *
FROM   sys.dm_exec_sessions
WHERE  session_id >= 51
       AND HOST_NAME = 'YSX'

--查看某个登录名的连接会话信息
SELECT *
FROM   sys.dm_exec_sessions
WHERE  session_id >= 51
       AND login_name = 'sa'

--查看活动的连接会话信息
SELECT *
FROM   sys.dm_exec_sessions WITH(NOWAIT)
WHERE  session_id >= 51
       AND STATUS = 'running'

--查找连接到服务器的用户并返回每个用户的会话数
SELECT login_name,
       COUNT(session_id) AS session_count
FROM   sys.dm_exec_sessions
GROUP BY
       login_name;

--查看正在执行的SQL语句
--方法1
SELECT [Spid] = session_Id,
       ecid,
       [Database] = DB_NAME(sp.dbid),
       [User] = nt_username,
       [Status] = er.status,
       [Wait] = wait_type,
       [Individual Query] = SUBSTRING(
           qt.text,
           er.statement_start_offset / 2,
           (
               CASE 
                    WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text))
                         * 2
                    ELSE er.statement_end_offset
               END - er.statement_start_offset
           ) / 2
       ),
       [Parent Query] = qt.text,
       Program = program_name,
       Hostname,
       nt_domain,
       start_time
FROM   sys.dm_exec_requests er
       INNER JOIN sys.sysprocesses sp
            ON  er.session_id = sp.spid
       CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
WHERE  session_Id >= 51

--方法2
SELECT m.session_id,
       m.start_time,
       m.command,
       m.wait_type,
       m.cpu_time,
       CAST(s.text AS VARCHAR(1000)) AS sqlText
FROM   MASTER.sys.dm_exec_requests m WITH (NOLOCK)
       CROSS APPLY fn_get_sql(m.sql_handle) s
 
SELECT r.session_id,
       r.start_time,
       r.command,
       r.wait_type,
       r.cpu_time,
       s.text
FROM   sys.dm_exec_requests r
       CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
     
      
  --【查看SQL SERVER进程执行的语句】    
      USE MASTER 
DECLARE @spid INT;
 
DECLARE @sql_handle BINARY(20);
 
SET @spid = 56
 
SELECT @sql_handle = sql_handle
FROM   sysprocesses AS A WITH (NOLOCK)
WHERE  spid = @spid;
 
SELECT TEXT
FROM   ::fn_get_sql(@sql_handle);
    
    
--【查看会话阻塞/死锁信息】 
--方法1：查看那个引起阻塞，查看blk不为0的记录，如果存在阻塞进程，则是该阻塞进程的会话 ID。否则该列为零。  
EXEC sp_who ACTIVE
--方法2：查看那个引起阻塞，查看字段BlkBy，这个能够得到比sp_who更多的信息。

EXEC sp_who2 ACTIVE
   
   --【查看内存状态】 
   DBCC memorystatus
  

 

查找一个表的主键包含哪些列？       --可以根据需要，自行修改 where 字句中的 条件
  SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_NAME), 'ISPRIMARYKEY') = 1 AND TABLE_SCHEMA='dbo' AND TABLE_NAME='EXPRESSION'

  统计某个schema下有多少个表
  --可以根据需要，修改where字句的条件，例如 表名满足一定的pattern :table_name like ‘ACCOUNT%’

  SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='DBO'

  统计包含某个字段名的所有表
  SELECT TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME='<COLUMN_NAME>'

  统计数据量比较大的表的条数
  SELECT t.name, s.row_count from sys.tables t JOIN sys.dm_db_partition_stats s
ON t.object_id = s.object_id
AND t.type_desc = 'USER_TABLE'
AND s.index_id = 1
AND t.name ='<table_name>'

  查找依赖于某个表的数据库对象（2008版本以上）

  --当我们要把某张表废弃掉，用这个方法可以找出所有受影响的对象
  SELECT
        referencing_schema_name, referencing_entity_name, 
        referencing_class_desc, is_caller_dependent
FROM sys.dm_sql_referencing_entities ('<TableName>', 'OBJECT')

  查找某个存储过程依赖的数据库对象（2008版本以上）
  SELECT
        referenced_schema_name, referenced_entity_name, referenced_minor_name, 
        referenced_class_desc, is_caller_dependent, is_ambiguous
FROM sys.dm_sql_referenced_entities ('<StoredProcedureName>', 'OBJECT');

http://database.callback001.cn/sqlserver/62067507844201911225.html

http://database.callback001.cn/

http://www.dongcoder.com/s/1147/sql_2_server_2_2016 

PowerDesigner生成sql及HTML格式数据库文档

http://www.bbsmax.com/A/kvJ3MWPAdg/

 

sql server 数据字典的妙用

--统计某个schema下有多少个表
SELECT  *
FROM    INFORMATION_SCHEMA.TABLES
WHERE   TABLE_SCHEMA = 'DBO';

--查找一个表的主键包含哪些列
SELECT  *
FROM    INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE   OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_NAME), 'ISPRIMARYKEY') = 1
        AND TABLE_SCHEMA = 'dbo'
        AND TABLE_NAME = 'jcms_normal_template';


--统计包含某个字段名的所有表
  SELECT    TABLE_CATALOG ,
            TABLE_SCHEMA ,
            TABLE_NAME
  FROM      INFORMATION_SCHEMA.COLUMNS
  WHERE     COLUMN_NAME = 'CreateUser';


--统计数据量比较大的表的条数
    SELECT  t.name ,
            s.row_count
    FROM    sys.tables t
            JOIN sys.dm_db_partition_stats s ON t.object_id = s.object_id
                                                AND t.type_desc = 'USER_TABLE'
                                                AND s.index_id = 1
                                                AND t.name = 'Training_PlanCourserRelation';