mssql常用系统级查询

zyfsky666

  
1、查询数据库中的所有表名称：

SELECT name FROM SysObjects Where XType='U' ORDER BY Name



2、查询数据库中指定表的表结构：

--快速查看表结构

SELECT CASE WHEN col.colorder = 1 THEN obj.name

                 ELSE ''

            END AS 表名,

       col.colorder AS 序号 ,

       col.name AS 列名 ,

       ISNULL(ep.[value], '') AS 列说明 ,

       t.name AS 数据类型 ,

       col.length AS 长度 ,

       ISNULL(COLUMNPROPERTY(col.id, col.name, 'Scale'), 0) AS 小数位数 ,

       CASE WHEN COLUMNPROPERTY(col.id, col.name, 'IsIdentity') = 1 THEN'1'

            ELSE ''

       END AS 标识 ,

       CASE WHEN EXISTS ( SELECT   1

                         FROM     dbo.sysindexes si

                                  INNER JOIN dbo.sysindexkeys sik ON si.id =sik.id

                                                           AND si.indid = sik.indid

                                  INNER JOIN dbo.syscolumns sc ON sc.id =sik.id

                                                           AND sc.colid = sik.colid

                                  INNER JOIN dbo.sysobjects so ON so.name =si.name

                                                           AND so.xtype = 'PK'

                         WHERE    sc.id = col.id

                                  AND sc.colid = col.colid ) THEN '1'

            ELSE ''

       END AS 主键 ,

       CASE WHEN col.isnullable = 1 THEN '1'

            ELSE ''

       END AS 允许空 ,

       ISNULL(comm.text, '') AS 默认值

FROM   dbo.syscolumns col

       LEFT  JOIN dbo.systypes t ON col.xtype = t.xusertype

       inner JOIN dbo.sysobjects obj ON col.id = obj.id

                                       AND obj.xtype = 'U'

                                       AND obj.status >= 0

       LEFT  JOIN dbo.syscomments comm ON col.cdefault = comm.id

       LEFT  JOIN sys.extended_properties ep ON col.id =ep.major_id

                                                   AND col.colid = ep.minor_id

                                                   AND ep.name = 'MS_Description'

       LEFT  JOIN sys.extended_properties epTwo ON obj.id =epTwo.major_id

                                                      AND epTwo.minor_id = 0

                                                      AND epTwo.name = 'MS_Description'

WHERE  obj.name = 'PkAutoInc'--表名

ORDER BY col.colorder ;


/*1.查询SQLSERVER中的所有数据库*/

use master

go

select * fromsysdatabases where dbid>4/*where条件用来去掉系统数据库*/

go

/*2.查询SQLSERVER中的所有数据库(存储过程)*/

exec sp_helpdb

/*3.查询指定数据库中的表*/

use 数据库名

go

select * fromsysobjects where xtype='u'/*where条件用来去掉系统表*/

go

[删除字段]

alter table tb_commonLog --表名

        drop

        column l_test,--去掉字段1

        columnl_test2, --去掉字段2

        constraintpk_name --去掉主键

go



use data;

SELECT tb2.name AS tableName,tb1.name AStriggerName FROM Sysobjects tb1

JOIN Sysobjects tb2 ON tb1.parent_obj=tb2.idWHERE tb1.type='TR'; --查触发器

SELECT name FROM  sys.all_objectsWHERE  type_desc = N'SQL_STORED_PROCEDURE' and substring(name,1,3) notin('sp_','xp_'); --查存储过程

exec sp_helptext '客户应收账表因其修改'; -- 查内容

select * from sys.procedures where substring(name,1,3) not in('sp_');--查存储过程



SELECT * FROM  sys.all_objects wheretype='FN' and name not like 'fn_%' ;--查函数
查库所有表



use data

go

select * from sys.tables


--查询数据库所有连接进程

select * from (SELECT * FROMmaster.dbo.sysprocesses WHERE dbid IN

(

  SELECT dbid FROMmaster.dbo.sysdatabases

  WHERE NAME='DATA' --指定的数据库名字

)) AA inner join

(SELECT S.SPID, S.LOGINAME, S.LOGIN_TIME,S.LAST_BATCH, C.CLIENT_NET_ADDRESS   

FROM sys.sysprocesses S,sys.dm_exec_connections C   

WHERE S.spid = C.SESSION_ID)BB on AA.spid=BB.spid

--判断游标是否存在

if exists(select * fromMASTER.dbo.syscursors where cursor_name='youbiao')

begin

deallocate youbiao

end
;
--查看死锁
SELECT [session_id] ,
      [blocking_session_id] AS '正在阻塞其他会话的会话ID' ,
      DB_NAME ( [database_id] ) AS '数据库名称' ,
      [request_id] ,
      [cpu_time] ,
      [start_time] AS '开始时间' ,
      [status] AS '状态' ,
      [command] AS '命令' ,
      dest.[text] AS '语句' ,
      [reads] AS '物理读次数' ,
      [writes] AS '写次数' ,
      [logical_reads] AS '逻辑读次数' ,
      [row_count] AS '返回结果行数' ,
      [wait_type] AS '等待资源类型' ,
      [wait_time] AS '等待时间' ,
      [wait_resource] AS '等待的资源'
FROM sys.[dm_exec_requests] AS der
    CROSS APPLY sys.[dm_exec_sql_text] ( der.[sql_handle] ) AS dest
WHERE 1 = 1
ORDER BY [session_id] DESC

----------查看作业
--SELECT * FROM sysjobs_view

--SELECT * FROM sysjobservers

--SELECT * FROM sysjobsteps

SELECT Jobs.name AS '作业名称' ,
       CASE Jobs.enabled
            WHEN 1 THEN '启用'
            WHEN 0 THEN '未启用'
       END AS '是否启用',
       Jobs.description AS '描述信息',
       Jobs.date_created AS '创建日期',
       Jobs.date_modified AS '修改日期',
       Steps.command AS '作业内容',
       CASE Schedules.freq_type
            WHEN 1 THEN '只运行一次'
            WHEN 4 THEN '每天'
            WHEN 8 THEN '每周'
            WHEN 16 THEN '每月'
            WHEN 32 THEN '每月'
            WHEN 64 THEN '在代理服务启动时运行'
            WHEN 128 THEN '在服务器空闲时运行'
       END AS '频率',
       CASE Schedules.freq_type
            WHEN 1 THEN '只运行一次'
            WHEN 4 THEN '每' + CAST(Schedules.freq_interval AS VARCHAR (10)) + '天'
            WHEN 8 THEN '每周' + ( CASE freq_interval
                                      WHEN 1 THEN '星期日'
                                      WHEN 2 THEN '星期一'
                                      WHEN 4 THEN '星期二'
                                      WHEN 8 THEN '星期三'
                                      WHEN 16 THEN '星期四'
                                      WHEN 32 THEN '星期五'
                                      WHEN 64 THEN '星期六'
                                 END )
            WHEN 16 THEN '每月' + CAST(Schedules.freq_interval AS VARCHAR (10)) + '日'
            WHEN 32 THEN '每月' + ( CASE freq_interval
                                       WHEN 1 THEN ( CASE freq_relative_interval
                                                          WHEN 1 THEN '第一'
                                                          WHEN 2 THEN '第二'
                                                          WHEN 4 THEN '第三'
                                                          WHEN 8 THEN '第四'
                                                          WHEN 16 THEN '最后一个'
                                                     END ) + '个星期日'
                                       WHEN 2 THEN ( CASE freq_relative_interval
                                                          WHEN 1 THEN '第一'
                                                          WHEN 2 THEN '第二'
                                                          WHEN 4 THEN '第三'
                                                          WHEN 8 THEN '第四'
                                                          WHEN 16 THEN '最后一个'
                                                     END ) + '星期一'
                                       WHEN 3 THEN ( CASE freq_relative_interval
                                                          WHEN 1 THEN '第一'
                                                          WHEN 2 THEN '第二'
                                                          WHEN 4 THEN '第三'
                                                          WHEN 8 THEN '第四'
                                                          WHEN 16 THEN '最后一个'
                                                     END ) + '星期二'
                                       WHEN 4 THEN ( CASE freq_relative_interval
                                                          WHEN 1 THEN '第一'
                                                          WHEN 2 THEN '第二'
                                                          WHEN 4 THEN '第三'
                                                          WHEN 8 THEN '第四'
                                                          WHEN 16 THEN '最后一个'
                                                     END ) + '星期三'
                                       WHEN 5 THEN ( CASE freq_relative_interval
                                                          WHEN 1 THEN '第一'
                                                          WHEN 2 THEN '第二'
                                                          WHEN 4 THEN '第三'
                                                          WHEN 8 THEN '第四'
                                                          WHEN 16 THEN '最后一个'
                                                     END ) + '星期四'
                                       WHEN 6 THEN ( CASE freq_relative_interval
                                                          WHEN 1 THEN '第一'
                                                          WHEN 2 THEN '第二'
                                                          WHEN 4 THEN '第三'
                                                          WHEN 8 THEN '第四'
                                                          WHEN 16 THEN '最后一个'
                                                     END ) + '星期五'
                                       WHEN 7 THEN ( CASE freq_relative_interval
                                                          WHEN 1 THEN '第一'
                                                          WHEN 2 THEN '第二'
                                                          WHEN 4 THEN '第三'
                                                          WHEN 8 THEN '第四'
                                                          WHEN 16 THEN '最后一个'
                                                     END ) + '星期六'
                                       WHEN 8 THEN ( CASE freq_relative_interval
                                                          WHEN 1 THEN '第一'
                                                          WHEN 2 THEN '第二'
                                                          WHEN 4 THEN '第三'
                                                          WHEN 8 THEN '第四'
                                                          WHEN 16 THEN '最后一'
                                                     END ) + '天'
                                       WHEN 9 THEN ( CASE freq_relative_interval
                                                          WHEN 1 THEN '第一'
                                                          WHEN 2 THEN '第二'
                                                          WHEN 4 THEN '第三'
                                                          WHEN 8 THEN '第四'
                                                          WHEN 16 THEN '最后一个'
                                                     END ) + '工作日'
                                       WHEN 10 THEN ( CASE freq_relative_interval
                                                           WHEN 1 THEN '第一'
                                                           WHEN 2 THEN '第二'
                                                           WHEN 4 THEN '第三'
                                                           WHEN 8 THEN '第四'
                                                           WHEN 16 THEN '最后一个'
                                                      END ) + '休息日'
                                  END )
       END AS '间隔天数',
       CASE freq_subday_type
            WHEN 1 THEN '指定时间'
            WHEN 2 THEN '每隔' + CAST(freq_subday_interval AS VARCHAR (10)) + '秒'
            WHEN 4 THEN '每隔' + CAST(freq_subday_interval AS VARCHAR (10)) + '分钟'
            WHEN 8 THEN '每隔' + CAST(freq_subday_interval AS VARCHAR (10)) + '小时'
       END AS '间隔时间',
       DATEADD (
         HOUR ,
         ( active_start_time / 10000 ) % 100,
         DATEADD ( MINUTE, ( active_start_time / 100 ) % 100, DATEADD ( SECOND, ( active_start_time / 1 ) % 100, CAST('00:00:00' AS TIME (0))))) AS '开始执行时间',
       DATEADD (
         HOUR ,
         ( active_end_time / 10000 ) % 100,
         DATEADD ( MINUTE, ( active_end_time / 100 ) % 100, DATEADD ( SECOND, ( active_end_time / 1 ) % 100, CAST('00:00:00' AS TIME (0))))) AS '截止时间',
       CONVERT ( DATE, CAST(active_start_date AS VARCHAR (20)), 23 ) AS '开始日期',
       CONVERT ( DATE, CAST(active_end_date AS VARCHAR (20)), 23 ) AS '截止日期'
FROM sysjobs Jobs
     JOIN sysjobschedules JobsSchedules ON Jobs.job_id = JobsSchedules.job_id
     JOIN sysschedules Schedules ON JobsSchedules.schedule_id = Schedules.schedule_id
     JOIN sysjobsteps Steps ON Jobs.job_id = Steps.job_id
---------------------------------------------------------真秀库神操作
USE [data]
GO

/****** Object:  StoredProcedure [dbo].[dataviews]    Script Date: 09/06/2020 15:41:14 ******/
SET ANSI_NULLS ON
GO
SET NOCOUNT ON
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[dataviews]
(
@data varchar(250)=''
)

AS
BEGIN
DECLARE @sql VARCHAR(500)=''
DECLARE @com VARCHAR(250)='', @obj VARCHAR(250)='';
select @com=@data +'.dbo.syscomments a,',@obj=@data + '.dbo.sysobjects b'
SET @sql=N'use ' + @data +N';'
SET @sql= @sql +N'
select b.name AS 名称,a.text AS 内容,
( CASE b.xtype WHEN ''c'' THEN ''check约束''
WHEN ''D'' THEN ''字段默认值''
WHEN ''FN'' THEN ''标量值函数''
WHEN ''IF'' THEN ''表值函数''
WHEN ''P'' THEN ''存储过程''
WHEN ''TR'' THEN ''触发器''
WHEN ''V'' THEN ''视图''
ELSE b.xtype
END )AS 类型,
ISNULL(OBJECT_NAME(b.parent_obj),''非表项目'') AS 基表,b.crdate as 创建时间 from
'
set @sql=@sql+@com +@obj + N' where a.id=b.id ORDER BY b.xtype,b.parent_obj,b.crdate desc'
PRINT @sql
exec(@sql)

END
GO
DECLARE @tabelview TABLE( 名称 VARCHAR(250),内容 VARCHAR(max),类型 VARCHAR(250),基表 VARCHAR(250),创建时间 DATETIME)
INSERT @tabelview ( 名称, 内容, 类型, 基表, 创建时间 ) EXEC data.dbo.dataviews N'data'
SELECT * FROM @tabelview
DECLARE @cretxt VARCHAR(max)
SELECT @cretxt=内容 FROM @tabelview WHERE 类型='存储过程' AND SUBSTRING(名称,1,3)  NOT IN('sp_','xp_')
PRINT @cretxt