SQLServer 常用系统查询

-- 查找存储过程被哪些存储过程调用

1 SELECT DISTINCT
2        name
3 FROM sys.syscomments a,
4      sys.sysobjects b
5 WHERE a.id = b.id
6       AND b.xtype = 'p'
7       AND text LIKE '%被调用存储过程名%';

-- 查找表被哪些存储过程调用

1 SELECT DISTINCT
2        OBJECT_NAME(id)
3 FROM sys.syscomments
4 WHERE id IN(SELECT id FROM sys.sysobjects WHERE type = 'P')
5       AND text LIKE '%DCMoveStationKN%';

-- 查询执行的作业（查询存储过程在那个作业中执行）

1 SELECT *
2 FROM msdb.dbo.sysjobs JOB WITH (NOLOCK)
3     INNER JOIN msdb.dbo.sysjobsteps STP WITH (NOLOCK)
4         ON STP.job_id = JOB.job_id
5 WHERE STP.command LIKE N'%proc_Query_Report_LineLoss%';

-- 字符串截取

1 SELECT PARSENAME('1-QPB-21-0016-B.1-QPB-21-0016-B.MES_DeviceHeartbeat', 2);

-- 查询表字段说明

 1 SELECT 
 2     表名 = OBJECT_NAME(c.object_id), 
 3     字段名称 = c.name, 
 4     字段说明 = ex.value,
 5     字段类型 = t.name,
 6     字段长度 = c.max_length
 7 FROM 
 8     sys.columns c 
 9 LEFT OUTER JOIN sys.extended_properties ex ON  ex.major_id = c.object_id 
10         AND ex.minor_id = c.column_id 
11         AND ex.name = 'MS_Description' 
12 inner join sys.types t ON t.system_type_id=c.system_type_id
13 WHERE 
14     OBJECTPROPERTY(c.object_id, 'IsMsShipped')=0 
15     AND OBJECT_NAME(c.object_id) = 'WW_RMInventory'  -- 这里加上你的表名字

 1 SELECT a.name tabname,
 2        a1.name '字段',
 3        b.value '字段描述（说明）'
 4 FROM sysobjects a
 5     LEFT JOIN sys.columns a1
 6         ON a.id = a1.object_id
 7     LEFT JOIN sys.extended_properties b
 8         ON b.major_id = a.id
 9            AND b.minor_id = a1.column_id
10 WHERE ISNULL(a1.name, '') <> ''; --and a.name='JK_StoveOutInfoTemp'

-- 查询字段所在的表

1 SELECT sysobjects.name AS tablename,
2        syscolumns.name AS columnname
3 FROM syscolumns
4     JOIN sysobjects
5         ON sysobjects.id = syscolumns.id
6 WHERE syscolumns.name LIKE '%ProductEntryId%';

--在所有存储过程中查找关键词

1 SELECT b.name,
2        a.text
3 FROM dbo.syscomments a,
4      dbo.sysobjects b
5 WHERE a.id = b.id
6       AND b.xtype = 'p'
7       AND a.text LIKE '%172.21.0.22%';

--在所有触发器中查找关键词

1 SELECT b.name,
2        a.text
3 FROM dbo.syscomments a,
4      dbo.sysobjects b
5 WHERE a.id = b.id
6       AND b.xtype = 'tr'
7       AND a.text LIKE '%abc%';

--查询XML

 1 DECLARE @ParameterSQL NVARCHAR(MAX)='SELECT top 3 * FROM DeviceStatus';
 2 DECLARE @SQL NVARCHAR(MAX)
 3 DECLARE @XMLString VARCHAR(MAX)
 4 DECLARE @XML XML
 5 DECLARE @Paramlist NVARCHAR(1000)
 6 SET @Paramlist = N'@XML XML OUTPUT'
 7 SET @SQL = 'WITH PrepareTable (XMLString)'
 8 SET @SQL = @SQL + 'AS( '
 9 SET @SQL = @SQL + @ParameterSQL+ ' FOR XML RAW,TYPE,ELEMENTS'
10 SET @SQL = @SQL + ')'
11 SET @SQL = @SQL + 'SELECT @XML=[XMLString]FROM[PrepareTable]'
12 EXEC sp_executesql @SQL, @Paramlist, @XML=@XML OUTPUT
13 SET @XMLString=CAST(@XML AS VARCHAR(MAX))
14 SELECT @XML;
15 SELECT @XMLString;

--查询系统已执行的SQL语句

 1 SELECT TOP 10
 2        st.text AS sql_statement,
 3        qs.creation_time AS plan_last_compiled,
 4        qs.last_execution_time AS plan_last_executed,
 5        qs.execution_count AS plan_executed_count,
 6        qp.query_plan
 7 FROM sys.dm_exec_query_stats qs
 8     CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
 9     CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
10 ORDER BY qs.creation_time DESC;

--通过日志查看用户访问数据库情况

SELECT StartTime,
       LoginName,
       NTDomainName,
       TextData,
       DatabaseID,
       DatabaseName,
       TransactionID,
       LineNumber,
       HostName,
       ApplicationName,
       ServerName,
       EventClass,
       FileName,
       SessionLoginName
FROM sys.fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\log_21.trc', 1);

--SQL延时执行

DECLARE @Secnod INT = 5;
DECLARE @t1 VARCHAR(8);
SET @t1 = CONVERT(VARCHAR, DATEADD(ss, @Secnod, 0), 108);
WAITFOR DELAY @t1; --等待n秒
SELECT GETDATE();

--查看存储过程执行耗时

 1 SELECT TOP 100
 2        a.name AS [存储过程名称],
 3        b.total_elapsed_time / b.execution_count / 1000000 AS [单次运行时间 秒],
 4        b.total_elapsed_time AS [总运行时间 微秒],
 5        b.execution_count AS [总运行次数]
 6 FROM sys.procedures AS a
 7     INNER JOIN sys.dm_exec_procedure_stats AS b
 8         ON a.[object_id] = b.[object_id]
 9 WHERE b.database_id = DB_ID() ---当前DB
10 ORDER BY b.total_elapsed_time / b.execution_count DESC;

 1 SELECT TOP 50
 2        total_worker_time / 1000 AS [总消耗CPU 时间(ms)],
 3        execution_count [运行次数],
 4        qs.total_worker_time / qs.execution_count / 1000 AS [平均消耗CPU 时间(ms)],
 5        last_execution_time AS [最后一次执行时间],
 6        min_worker_time / 1000 AS [最小执行时间(ms)],
 7        max_worker_time / 1000 AS [最大执行时间(ms)],
 8        SUBSTRING(qt.text,
 9                     qs.statement_start_offset / 2 + 1,
10                     (CASE
11                          WHEN qs.statement_end_offset = -1 THEN
12                              DATALENGTH(qt.text)
13                          ELSE
14                              qs.statement_end_offset
15                      END - qs.statement_start_offset
16                     ) / 2 + 1
17                 ) AS [使用CPU的语法],
18        qt.text [完整语法],
19        qt.dbid,
20        dbname = DB_NAME(qt.dbid),
21        qt.objectid,
22        OBJECT_NAME(qt.objectid, qt.dbid) ObjectName
23 FROM sys.dm_exec_query_stats qs WITH (NOLOCK)
24     CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
25 WHERE execution_count > 1
26 ORDER BY (qs.total_worker_time / qs.execution_count / 1000) DESC;

--分页查询每页5条数据，第三页

1 SELECT *
2 FROM [dbo].Product
3 ORDER BY ProductId 
4 OFFSET 5 * 2 ROWS FETCH NEXT 5 ROWS ONLY;

1 SELECT *
2 FROM
3 (
4     SELECT *,
5            ROW_NUMBER() OVER (ORDER BY ProductId) ROW_ID
6     FROM [dbo].Product
7 ) t
8 WHERE t.ROW_ID
9 BETWEEN (5 * (3 - 1) + 1) AND 5 * 3;

--全表数据搜索（表数据较大，表比较多，执行耗时比较长）

 1 DECLARE @Str NVARCHAR(MAX),
 2         @tableName VARCHAR(50),
 3         @colName VARCHAR(50),
 4         @rowCount INT;
 5 
 6 SELECT a.name AS tableName,
 7        b.name AS Colname,
 8        0 AS IsFound
 9 INTO #t1
10 FROM sysobjects a
11     INNER JOIN syscolumns b
12         ON a.id = b.id
13     INNER JOIN systypes c
14         ON b.xtype = c.xtype
15 WHERE a.[type] = 'U'
16       AND c.name IN ( 'varchar', 'nvarchar', 'char', 'nchar' ); --这里是设置字段的类型，以缩小范围
17 
18 DECLARE _c1 CURSOR FOR 
19     SELECT Colname, tableName FROM #t1;
20 OPEN _c1;
21 FETCH NEXT FROM _c1
22 INTO @colName,
23      @tableName;
24 WHILE @@FETCH_STATUS = 0
25 BEGIN
26     --print @Str
27     SELECT @Str
28         = N'SELECT @rowCount=count(1) FROM [' + @tableName + N'] WHERE [' + @colName + N'] LIKE ''%TH20220630000001%'''; --这里是要查找的内容
29     EXEC sp_executesql @Str, N'@rowCount int output', @rowCount OUTPUT;
30     IF @rowCount > 0
31         UPDATE #t1
32         SET IsFound = 1
33         WHERE Colname = @colName
34               AND tableName = @tableName;
35 
36     FETCH NEXT FROM _c1
37     INTO @colName,
38          @tableName;
39 END;
40 CLOSE _c1;
41 DEALLOCATE _c1;

--查询表数据内存占用

 1 SELECT 
 2 　　 db_name() as DbName,
 3     t.NAME AS TableName,
 4     s.Name AS SchemaName,
 5     p.rows AS [表总行数],
 6     SUM(a.total_pages) * 8 AS TotalSpaceKB, 
 7     CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS [总共占用空间MB],
 8     SUM(a.used_pages) * 8 AS [使用空间KB],--包括索引大小和数据大小
 9     CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
10     (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS [未使用大小KB],
11     CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS [未使用大小MB]
12 FROM 
13     sys.tables t
14 INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
16 INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
18 INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
20 LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
22 WHERE 
23     t.NAME NOT LIKE 'dt%' 
24     AND t.is_ms_shipped = 0
25     AND i.OBJECT_ID > 0
26 GROUP BY 
27     t.Name, s.Name, p.Rows
28 ORDER BY 
29     总共占用空间MB DESC