如何快速搜索SQL数据库数据和对象

本文涉及的产品
云数据库 RDS SQL Server,基础系列 2核4GB
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
简介: 原文 如何快速搜索SQL数据库数据和对象 Frequently, developers and DBAs need to search databases for objects or data. If you’d ever searched for a database function tha...

原文 如何快速搜索SQL数据库数据和对象

Frequently, developers and DBAs need to search databases for objects or data. If you’d ever searched for a database function that contains a specific table column or a variable name, or for a table that contains specific data, you would have found out that there’s no one click solution, such as Ctrl+F

As there is no out-of-the-box solution in SQL Server management Studio, nor Visual Studio, here are a couple of options you can use:

 

Searching for data in tables and views

Using SQL to search for specific data in all tables and all columns of a database is far from an optimal solution. There are various SQL scripts with different approaches that can be used to obtain this information, what they have in common is that they all use cursors and system objects.

DECLARE
   @SearchText varchar(200),
   @Table varchar(100),
   @TableID int, @ColumnName varchar(100), @String varchar(1000); --modify the variable, specify the text to search for SET @SearchText = 'John'; DECLARE CursorSearch CURSOR FOR SELECT name, object_id FROM sys.objects WHERE type = 'U'; --list of tables in the current database. Type = 'U' = tables(user-defined) OPEN CursorSearch; FETCH NEXT FROM CursorSearch INTO @Table, @TableID; WHILE @@FETCH_STATUS = 0 BEGIN DECLARE CursorColumns CURSOR FOR SELECT name FROM sys.columns WHERE object_id = @TableID AND system_type_id IN(167, 175, 231, 239); -- the columns that can contain textual data --167 = varchar; 175 = char; 231 = nvarchar; 239 = nchar OPEN CursorColumns; FETCH NEXT FROM CursorColumns INTO @ColumnName; WHILE @@FETCH_STATUS = 0 BEGIN SET @String = 'IF EXISTS (SELECT * FROM ' + @Table + ' WHERE ' + @ColumnName + ' LIKE ''%' + @SearchText + '%'') PRINT ''' + @Table + ', ' + @ColumnName + ''''; EXECUTE (@String); FETCH NEXT FROM CursorColumns INTO @ColumnName; END; CLOSE CursorColumns; DEALLOCATE CursorColumns; FETCH NEXT FROM CursorSearch INTO @Table, @TableID; END; CLOSE CursorSearch; DEALLOCATE CursorSearch; 

 

The drawbacks of this solution are: use of cursors, which are generally inefficient, high complexity, a lot of time needed for execution, even on small databases. Another disadvantage is that it can be used to search for text data only. To search for other data types, such as time and datetime, you must write new code

Searching for objects.

Searching for a database object name or object definition is a bit easier than searching for specific text. There are several methods you can use. However, all of these methods include querying system objects.

The following SQL examples search for the specified text – the @StartProductID variable – in stored procedures. When searching for objects in other database object types – functions, triggers, columns, etc., or in multiple database object types at the same time, the SQL shown above should be modified accordingly

INFORMATION_SCHEMA.ROUTINES

Use SQL that queries the INFORMATION_SCHEMA.ROUTINES view to search for a specific parameter in all procedures. The INFORMATION_SCHEMA.ROUTINES view contains information about all stored procedures and functions in a database. The ROUTINE_DEFINITION column contains the source statements that created the function or stored procedure.

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
    FROM INFORMATION_SCHEMA.ROUTINES 
    WHERE ROUTINE_DEFINITION LIKE '%@StartproductID%' AND ROUTINE_TYPE='PROCEDURE' 

 

And the result is

It is not recommended to use INFORMATION_SCHEMA views to search for object schemas stored in the ROUTINE_SCHEMA column. Use the sys.objects catalog view instead

sys.syscomments view

Query the sys.syscomments view, which contains information about every stored procedure, view, rule, default, trigger, and CHECK and DEFAULT constraints in a database. The query checks for a specific text in the text column, which contains the object DDL

SELECT OBJECT_NAME( id )
  FROM SYSCOMMENTS
  WHERE text LIKE '%@StartProductID%' AND OBJECTPROPERTY(id , 'IsProcedure') = 1 GROUP BY OBJECT_NAME( id ); 

 

The result is

This method is not recommended because the sys.syscomments table will be removed in the future versions of SQL Server.

sys.sql_modules view

Query the sys.sql_modules view which contains the name, type and definition of every module in a database.

SELECT OBJECT_NAME( object_id )
  FROM sys.sql_modules
WHERE OBJECTPROPERTY(object_id , 'IsProcedure') = 1 AND definition LIKE '%@StartProductID%'; 

 

The results are the same as for the previous method.

Other sys schemaviews

Query sys.syscomments, sys.schemas and sys.objects views. The sys.schemas view contains a row for every database schema. The sys.objects view contains a row every user-defined, schema-scoped object in a database. Note that it doesn’t contain the triggers information, so you have to use the sys.triggers view to search for object names or object definitions in triggers.

DECLARE
 @searchString nvarchar( 50 );
SET@searchString = '@StartProductID'; SELECT DISTINCT s.name AS Schema_Name , O.name AS Object_Name , C.text AS Object_Definition FROM syscomments C INNER JOIN sys.objects O ON C.id = O.object_id INNER JOIN sys.schemas S ON O.schema_id = S.schema_id WHERE C.text LIKE '%' + @searchString + '%' OR O.name LIKE '%' + @searchString + '%' ORDER BY Schema_name , Object_name;

 

The returned results are:

 

The main disadvantage of these methods is that for every change in object types searched, you need to change SQL. To be able to do that, you have to be familiar with the system object structure so you can modify them. Searching in multiple object types, and adding additional search criteria, such as including/excluding object names and bodies, or defining the escape character, brings even more complexity to SQL, which is prone to mistakes without proper and time-consuming testing.

If you’re not an experienced developer, you prefer a tested and error-free solution to searching SQL objects and data manually, and you’re not familiar with system objects that hold DDL information about database objects, use ApexSQL Search.

ApexSQL Search is a SQL search add-in for SSMS and Visual Studio. It can search for text within database objects (including object names), data stored in tables and views (even encrypted ones), and repeat previous searches in a single click.

To search for data in tables and views:

  1. In SQL Server Management Studio or Visual Studio’s Main menu, click ApexSQL Search
  2. Select the Text search option:

  3. In the Search text field, enter the data value you want to search for
  4. From the Database drop-down menu, select the database to search in
  5. In the Select objects to search tree, select the tables and views to search in, or leave them all checked
  6. Select whether to search in views, numeric, text type, uniqueidentifier and date columns, by selecting the corresponding check boxes, and whether to search for an exact match. If searching in date columns, specify the date format:

     

    ApexSQL Search - Database text search

  7. Click the Find option. The grid will be populated with the database tables and views that contain the entered value:

    ApexSQL Search - Database text search

  8. Click the ellipsis button in the Column value to see the found object details:

     

    ApexSQL Search - Database search details

To search for objects:

  1. In SQL Server Management Studio or Visual Studio’s Main menu,from the ApexSQL menu, click ApexSQL Search.
  2. Select the Object search option:

    ApexSQL Search - Database search details

  3. In the Search text field, enter the text you want to search for (e.g. a variable name)
  4. From the Database drop-down menu, select the database to search in
  5. In the Objects drop-down list, select the object types to search in, or leave them all checked
  6. Select whether to search in object, column, index names, object bodies, system objects, by selecting the corresponding check boxes, whether to search for an exact match and which escape character to use
  7. Click the Find option:

     

    ApexSQL Search - Database object search

    The grid will be populated with the database objects that contain the specified object.

  8. Double click the object in the Object search grid, and it will be highlighted in the Object Explorer:

     

    ApexSQL Search - Database object search

SQL Server Management Studio and Visual Studio don’t provide search options for a database object name, object definition and data. SQL queries that search for these are complex, slow and require knowledge of SQL Server system objects. Use ApexSQL Search to dig through your databases and find data and objects you need.

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
目录
相关文章
|
1月前
|
数据采集 数据库 Python
有哪些方法可以验证用户输入数据的格式是否符合数据库的要求?
有哪些方法可以验证用户输入数据的格式是否符合数据库的要求?
157 75
|
3天前
|
SQL 数据建模 BI
【YashanDB 知识库】用 yasldr 配置 Bulkload 模式作单线程迁移 300G 的业务数据到分布式数据库,迁移任务频繁出错
问题描述 详细版本:YashanDB Server Enterprise Edition Release 23.2.4.100 x86_64 6db1237 影响范围: 离线数据迁移场景,影响业务数据入库。 外场将部分 NewCIS 的报表业务放到分布式数据库,验证 SQL 性能水平。 操作系统环境配置: 125G 内存 32C CPU 2T 的 HDD 磁盘 问题出现的步骤/操作: 1、部署崖山分布式数据库 1mm 1cn 3dn 单线启动 yasldr 数据迁移任务,设置 32 线程的 bulk load 模式 2、观察 yasldr.log 是否出现如下错
|
7天前
|
JSON Java 关系型数据库
Hutool创建数据源工厂动态查询不同数据库不同数据表的数据
Hutool创建数据源工厂动态查询不同数据库不同数据表的数据
23 2
|
21天前
|
SQL 存储 关系型数据库
【SQL技术】不同数据库引擎 SQL 优化方案剖析
不同数据库系统(MySQL、PostgreSQL、Doris、Hive)的SQL优化策略。存储引擎特点、SQL执行流程及常见操作(如条件查询、排序、聚合函数)的优化方法。针对各数据库,索引使用、分区裁剪、谓词下推等技术,并提供了具体的SQL示例。通用的SQL调优技巧,如避免使用`COUNT(DISTINCT)`、减少小文件问题、慎重使用`SELECT *`等。通过合理选择和应用这些优化策略,可以显著提升数据库查询性能和系统稳定性。
78 9
|
1月前
|
SQL Java 数据库连接
【潜意识Java】MyBatis中的动态SQL灵活、高效的数据库查询以及深度总结
本文详细介绍了MyBatis中的动态SQL功能,涵盖其背景、应用场景及实现方式。
112 6
|
2月前
|
SQL 存储 运维
从建模到运维:联犀如何完美融入时序数据库 TDengine 实现物联网数据流畅管理
本篇文章是“2024,我想和 TDengine 谈谈”征文活动的三等奖作品。文章从一个具体的业务场景出发,分析了企业在面对海量时序数据时的挑战,并提出了利用 TDengine 高效处理和存储数据的方法,帮助企业解决在数据采集、存储、分析等方面的痛点。通过这篇文章,作者不仅展示了自己对数据处理技术的理解,还进一步阐释了时序数据库在行业中的潜力与应用价值,为读者提供了很多实际的操作思路和技术选型的参考。
60 1
|
2月前
|
存储 Java easyexcel
招行面试:100万级别数据的Excel,如何秒级导入到数据库?
本文由40岁老架构师尼恩撰写,分享了应对招商银行Java后端面试绝命12题的经验。文章详细介绍了如何通过系统化准备,在面试中展示强大的技术实力。针对百万级数据的Excel导入难题,尼恩推荐使用阿里巴巴开源的EasyExcel框架,并结合高性能分片读取、Disruptor队列缓冲和高并发批量写入的架构方案,实现高效的数据处理。此外,文章还提供了完整的代码示例和配置说明,帮助读者快速掌握相关技能。建议读者参考《尼恩Java面试宝典PDF》进行系统化刷题,提升面试竞争力。关注公众号【技术自由圈】可获取更多技术资源和指导。
|
2月前
|
SQL Oracle 数据库
使用访问指导(SQL Access Advisor)优化数据库业务负载
本文介绍了Oracle的SQL访问指导(SQL Access Advisor)的应用场景及其使用方法。访问指导通过分析给定的工作负载,提供索引、物化视图和分区等方面的优化建议,帮助DBA提升数据库性能。具体步骤包括创建访问指导任务、创建工作负载、连接工作负载至访问指导、设置任务参数、运行访问指导、查看和应用优化建议。访问指导不仅针对单条SQL语句,还能综合考虑多条SQL语句的优化效果,为DBA提供全面的决策支持。
100 11
|
2月前
|
前端开发 JavaScript 数据库
获取数据库中字段的数据作为下拉框选项
获取数据库中字段的数据作为下拉框选项
70 5

热门文章

最新文章