一、注释中带某关键字的对象
主要用到 sys.tables 、sys.columns 、sys.procedures 系统对象表以及sys.extended_properties 扩展属性表
--查询列
SELECT A.name AS table_name ,
B.name AS column_name ,
C.value AS column_description
FROM sys.tables A
INNER JOIN sys.columns B ON B.object_id = A.object_id
LEFT JOIN sys.extended_properties C ON C.major_id = B.object_id
AND C.minor_id = B.column_id
WHERE CAST(C.[value] AS VARCHAR(1000)) LIKE '%年假%'; --查询表
SELECT A.name AS table_name ,
C.value AS column_description
FROM sys.tables A
INNER JOIN sys.extended_properties C ON C.major_id = A.object_id
AND C.minor_id = 0
WHERE CAST(C.[value] AS VARCHAR(1000)) LIKE '%请假%' --查询存储过程
SELECT A.name AS table_name ,
C.value AS column_description
FROM sys.procedures A
INNER JOIN sys.extended_properties C ON C.major_id = A.object_id
AND C.minor_id = 0
WHERE CAST(C.[value] AS VARCHAR(1000)) LIKE '%年假%'
二、定义语句中带某关键字的对象
主要用到 dbo.sysobjects 系统对象表以及sys.all_sql_modules 对象定义语句表
--老方式
SELECT DISTINCT b.name, b.xtype
FROM dbo.syscomments a, dbo.sysobjects b
WHERE a.id = b.id
AND b.xtype = 'p'
AND a.text LIKE '%LotMax%'
ORDER BY name; --从 2008 开始，新方式
SELECT name, type_desc
FROM sys.all_sql_modules s
INNER JOIN sys.all_objects o ON s.object_id = o.object_id
WHERE definition LIKE '%LotMax%'
ORDER BY type_desc, name;
三、查找列名
select A.name as table_name, B.name as column_name
from sys.tables A
inner join sys.columns B on B.object_id = A.object_id
where B.name like '%File%'
order by A.name, B.name;
完整的列属性：
with indexCTE
as ( select ic.column_id, ic.index_column_id, ic.object_id
from ZSOtherData.sys.indexes idx
inner join ZSOtherData.sys.index_columns ic on idx.index_id = ic.index_id and idx.object_id = ic.object_id
where idx.object_id = object_id('Mould') and idx.is_primary_key = 1 )
select colm.column_id ColumnID, cast(case when indexCTE.column_id is null then 0 else 1 end as bit) IsPrimaryKey, colm.name column_name ,object_definition(colm.default_object_id) AS column_def,
systype.name type_name, colm.is_identity is_identity, colm.is_nullable , cast(colm.max_length as int) ByteLength ,
( case when systype.name = 'nvarchar' and colm.max_length > 0 then colm.max_length / 2
when systype.name = 'nchar' and colm.max_length > 0 then colm.max_length / 2
when systype.name = 'ntext' and colm.max_length > 0 then colm.max_length / 2 else colm.max_length end ) length ,
cast(colm.precision as int) precision, cast(colm.scale as int) scale,colm.is_computed, prop.value Remark
from ZSOtherData.sys.columns colm
inner join ZSOtherData.sys.types systype on colm.system_type_id = systype.system_type_id and colm.user_type_id = systype.user_type_id
left join ZSOtherData.sys.extended_properties prop on colm.object_id = prop.major_id and colm.column_id = prop.minor_id
left join indexCTE on colm.column_id = indexCTE.column_id and colm.object_id = indexCTE.object_id
where colm.object_id = object_id('Mould')
order by colm.column_id;