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, , CHARINDEX('\', @@SERVERNAME)) AS SERVERNAME;

SELECT SUBSTRING(

           CONVERT(VARCHAR(), SERVERPROPERTY('servername')),

           ,

           CHARINDEX('\', CONVERT(VARCHAR(100), SERVERPROPERTY('servername')))

       ) AS ServerName;

SELECT SUBSTRING(srvname, , 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

 --【 查看排序规则信息】

 -- ：查看实例排序规则

SELECT SERVERPROPERTY(N'Collation')

 -- ：查看数据库排序规则

SELECT NAME,

       collation_name

FROM   sys.databases

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

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

EXEC sp_spaceused;

   --【查看数据库启动相关参数】

EXEC sp_configure;

    --【查看数据库启动时间】

SELECT CONVERT(VARCHAR(), LOGIN_TIME, ) AS StartDateTime

FROM   MASTER..sysprocesses

WHERE  spid = 

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

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(), 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 /  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 >= 

--查看某台机器的连接会话信息

SELECT *

FROM   sys.dm_exec_sessions

WHERE  session_id >=

       AND HOST_NAME = 'YSX'

--查看某个登录名的连接会话信息

SELECT *

FROM   sys.dm_exec_sessions

WHERE  session_id >=

       AND login_name = 'sa'

--查看活动的连接会话信息

SELECT *

FROM   sys.dm_exec_sessions WITH(NOWAIT)

WHERE  session_id >=

       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 / ,

           (

               CASE

                    WHEN er.statement_end_offset = - THEN LEN(CONVERT(NVARCHAR(MAX), qt.text))

                         *

                    ELSE er.statement_end_offset

               END - er.statement_start_offset

           ) /

       ),

       [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 >= 

--方法2

SELECT m.session_id,

       m.start_time,

       m.command,

       m.wait_type,

       m.cpu_time,

       CAST(s.text AS VARCHAR()) 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();

SET @spid = 

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') =  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 =

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';

巴特西

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

最新文章

热门文章