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;

 

 

 

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

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

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