SQL Server中搜索特定的對象

更新時間：2022年05月24日 08:26:02 作者：springsnow

這篇文章介紹了SQL Server搜索特定對象的方法，文中通過示例代碼介紹的非常詳細(xì)。對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值，需要的朋友可以參考下

檢索數(shù)據(jù)庫架構(gòu)信息 - ADO.NET | Microsoft 官方文檔

將系統(tǒng)表映射到系統(tǒng)視圖 (Transact-sql) - SQL Server | Microsoft 官方文檔

一、注釋中帶某關(guān)鍵字的對象（sys.extended_properties）

主要用到 sys.tables 、sys.columns 、sys.procedures 系統(tǒng)對象表以及sys.extended_properties 擴(kuò)展屬性表

--查詢列
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 '%年假%'

二、定義語句中帶某關(guān)鍵字的對象（sys.all_sql_modules ）

主要用到 dbo.sysobjects 系統(tǒng)對象表以及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('MouldTestResultDetail') 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,f.keyno as is_foreignkey, 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
     left join sysforeignkeys f on f.fkeyid=colm.object_id and f.fkey=colm.column_id
where    colm.object_id = object_id('MouldTestResultDetail')
order by colm.column_id;