SQL Server怎样对特定对象搜索，SQL语句是什么

发布时间：2023-05-08 14:16:28 所属栏目：MsSql教程来源：

导读：很多朋友都对“SQL Server如何对特定对象搜索，SQL语句是什么”的内容比较感兴趣，对此小编整理了相关的知识分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获，那么感兴趣的朋友就继续往下看吧！

很多朋友都对“SQL Server如何对特定对象搜索，SQL语句是什么”的内容比较感兴趣，对此小编整理了相关的知识分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获，那么感兴趣的朋友就继续往下看吧！

检索数据库架构信息 - ADO.NET | Microsoft 官方文档

将系统表映射到系统视图 (Transact-sql) - SQL Server | Microsoft 官方文档

一、注释中带某关键字的对象（sys.extended_properties）

主要用到 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 '%年假%'

二、定义语句中带某关键字的对象（sys.all_sql_modules ）

主要用到 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('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;

（编辑：聊城站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!