如何快速查找SQL数据库数据和对象

link管理

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

相关文章推荐

不羁的书签 · 【kubernetes】解决k8s1.28. ...· 1 周前 ·

鼻子大的小狗 · Git回滚中的reset和revert-阿里 ...· 4 天前 ·

爱搭讪的红金鱼 · 目标与效率_东软集团· 昨天 ·

痛苦的筷子 · Anaconda数据分析&人工智能教程（10 ...· 2 小时前 ·

睿智的酸菜鱼 · 【R语言】十折交叉验证-以逻辑回归为例__b ...· 2 小时前 ·

深沉的书包 · HiveQLSparQL 常用函数· 1 月前 ·

帅气的玉米 · typescript 获取当前时间 - ...· 3 月前 ·

闷骚的伤疤 · Standford ...· 4 月前 ·

要出家的大熊猫 · 新机动战记高达W：无尽的华尔兹_百度百科· 4 月前 ·

寂寞的炒粉 · 高新区科研骨干赴上海东方肝胆医院学习交流-医 ...· 4 月前 ·

开发人员和DBA很多时候都需要在数据库里查找对象或数据。如果您曾经搜索过一个数据库函数，函数里面包含了一个特定表列或者一个可变的名字，或者一个表包含了特定的数据，您会发现没有一键点击方案，就像 Ctrl+F。

由于在 SQL Server management Studio里或者在Visual Studio里没有开箱即用的解决方案，这里是一些您可以使用的选项：

在表和视图里搜索数据

使用SQL代码在所有表里搜索特定数据和数据库的所有列并不是一个最佳的解决方案。这里有各种SQL脚本来获取这些信息，它们的共同点是，它们都使用游标和系统对象。

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 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 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;

这种解决方案的缺点是：使用游标、效率低下、复杂性高、即使在小型数据库需要大量的执行时间。另一个缺点是，它只可以用来搜索文本数据。要搜索其他数据类型，比如time和datetime类型，您必须要编写新的代码。

搜索一个数据库对象名或对象定义比起搜索特定文本要容易一些。有几种方法您可以使用。但是，所有这些方法都需要查询系统对象。

以下的SQL示例用来搜索指定的文本-@StartProductID变量-在存储过程里面。当搜索的对象存在于其他的数据库对象类型- 函数、触发器、列等等，或者同时存在于多个数据库对象类型的时候，上面的SQL需要作出相应的修改

INFORMATION_SCHEMA.ROUTINES

使用SQL查询 INFORMATION_SCHEMA.ROUTINES 视图来搜索一个在所有存储过程里的特定参数。INFORMATION_SCHEMA.ROUTINES 视图包含了数据库里所有存储过程和函数的
信息。ROUTINE_DEFINITION 列包含了创建存储过程和函数的源语句。

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

不推荐使用INFORMATION_SCHEMA视图里的 ROUTINE_SCHEMA列来搜索对象schemas 。可以使用 sys.objects 目录视图代替。

sys.syscomments 视图

查询sys.syscomments视图，视图包含了一个数据库里每个存储过程、视图、规则、默认值、触发器、check和默认约束的信息。这个查询会检查在text列的特定文本，文本里会包含对象的DDL。

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

这个方法不是太推荐，因为sys.syscomments表将会在未来的SQL Server版本被移除。

sys.sql_modules 视图

查询sys.sql_modules 视图包含了一个数据库里每个模块的名字、类型和定义

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

结果跟之前那个方法显示是一样的

其他系统架构视图（sys schemaviews）

查询 sys.syscomments、sys.schemas 、sys.objects 视图。sys.schemas视图包含了每个数据库中每个架构都会显示一行。 sys.objects视图包含了一个数据库当中每个用户定义、架构范围的对象都会显示一行。注意它不包含触发器信息，所以您必须使用sys.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
     syscomments C INNER JOIN sys.objects O
     C.id
     O.object_id
                   INNER JOIN sys.schemas S
     O.schema_id
     S.schema_id
WHERE
    C.text LIKE
   + @searchString
   + '%'
 OR O.name LIKE
   + @searchString
   + '%'
ORDER BY
       Schema_name , Object_name;