主要用到 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("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;
到此这篇关于SQL Server搜索特定对象的文章就介绍到这了。希望对大家的学习有所帮助,也希望大家多多支持。
相关文章:
1. Can’t connect to MySQL server on ’localhost’ (10048)2. SQL Server系统函数介绍3. SQL Server开发智能提示插件SQL Prompt介绍4. SQL Server序列SEQUENCE用法介绍5. 轻量级数据库SQL Server Express LocalDb介绍6. SQL Server2019安装的详细步骤实战记录(亲测可用)7. SQL Server数据库备份和恢复数据库的全过程8. SQL Server备份数据库的完整步骤9. SQL Server实现查询每个分组的前N条记录10. 详解SQL Server 中的 ACID 属性