SQL查询包含某字段的所有存储过程/所有表

 

--查询包含某字段的所有存储过程
SELECT obj.Name '存储过程名', sc.text '存储过程内容'
FROM syscomments sc
INNER JOIN sysobjects obj ON sc.Id = obj.id
WHERE SC.text LIKE '%你要查询的字段名%'

--查询包含某字段的所有表
SELECT OBJECT_NAME(id) objName, Name AS colName
FROM syscolumns
WHERE (name LIKE '%你要查询的字段名%')
AND id IN(SELECT id FROM sysobjects WHERE xtype='u')
ORDER BY objName

--查询某个存储被调用的作业
SELECT *
FROM msdb.dbo.sysjobs JOB WITH( NOLOCK)
INNER JOIN msdb. dbo.sysjobsteps STP WITH(NOLOCK)
ON STP .job_id = JOB .job_id
WHERE STP .command LIKE '%存储名%'

 

--查询数据库中占用空间最大的表
SELECT
    t.NAME AS TableName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB,
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM
    sys.tables t
INNER JOIN     
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE
    t.NAME NOT LIKE 'dt%'
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255
GROUP BY
    t.NAME, p.Rows
ORDER BY
    TotalSpaceKB DESC;

 

--查询某字段在数据库中哪些表中
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'TPRReturnTo2';

 

--查询存在字段类型为char(6)的表
SELECT DISTINCT TABLE_CATALOG,COLUMN_NAME, TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'char'
AND CHARACTER_MAXIMUM_LENGTH = 6;

 

--查询读写占用资源最高的前25个操作
SELECT TOP 25 DB_NAME( st.[dbid] ) AS [Database]
      ,cp.usecounts AS [execution_count]
      ,qs.total_worker_time AS cpu
      ,qs.total_elapsed_time AS elapsed_time
      ,qs.total_logical_reads AS logical_reads
      ,qs.total_logical_writes AS logical_writes
      ,qs.total_physical_reads AS physical_reads
      ,qs.creation_time AS [CreationTime]
      ,SUBSTRING(text,
                   CASE WHEN statement_start_offset = 0
                          OR statement_start_offset IS NULL 
                           THEN 1 
                           ELSE statement_start_offset/2 + 1 END,
                   CASE WHEN statement_end_offset = 0
                          OR statement_end_offset = -1 
                          OR statement_end_offset IS NULL 
                           then len(text) 
                           else statement_end_offset/2 end -
                     case when statement_start_offset = 0
                            or statement_start_offset is null
                             then 1 
                             else statement_start_offset/2  end + 1
                  )  as [statement]
      ,st.text AS [Complete Query Text]              
from sys.dm_exec_query_stats qs 
   join sys.dm_exec_cached_plans cp on qs.plan_handle = cp.plan_handle
   cross apply sys.dm_exec_sql_text(cp.plan_handle) st
order by qs.total_logical_writes desc;

 

 

转自：https://blog.csdn.net/qq_39591448/article/details/123221800

https://blog.csdn.net/lee576/article/details/78062311

https://www.cnblogs.com/amadeuslee/p/17952363

https://www.cnblogs.com/Boboschen/p/18053744

https://www.cnblogs.com/abclife/p/17333038.html