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

开发人员和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
       =
       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;

这种解决方案的缺点是：使用游标、效率低下、复杂性高、即使在小型数据库需要大量的执行时间。另一个缺点是，它只可以用来搜索文本数据。要搜索其他数据类型，比如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
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;

结果是：

这些方法的主要缺点是对于每个对象类型搜索的变化，您都需要更改SQL。为了做到这一点您必须要熟悉系统对象结构以便可以修改SQL。搜索多个对象类型，并且添加额外的搜索条件，例如包括或排除对象名和内容，或者定义转义字符，这样在没有适当的充足时间测试的情况下会令SQL更复杂并且会出错。

如果您不是一个有经验的开发人员，您更喜欢一个经过测试和没有错误的解决方案来手工搜索SQL对象和数据，并且您对这些保存DDL信息的系统对象不熟悉和数据库对象，使用 ApexSQL Search.

ApexSQL Search 是一个 SQL搜索 SSMS和Visual Studio插件。它可以搜索数据库对象里的文本（包括对象名），存储在表里的数据和视图（甚至是加密了的视图），对之前的搜索需求只需要一键点击。

搜索表里和视图里的数据：

在 SQL Server Management Studio 或 Visual Studio的主菜单里，点击 ApexSQL Search
选择Database text search…选项：
在搜索文本框，输入您想搜索的数据值
从数据库下拉列表里，选择需要搜索的数据库
在Select objects to search 树形菜单中，选择要搜索的表和视图，或者让它们保持已勾选
选择是否要搜索视图、数值、text类型、uniqueidentifier和date列，通过选择相应的复选框，也可以选择是否精确匹配。如果需要搜索date列，指定date格式：
点击Find now 选项。grid窗格会显示所有包含了输入的值的数据库表和视图：
在 Column value列点击省略号按钮来查看查找到的对象详细内容：

为了查找对象：

在SQL Server Management Studio或Visual Studio的主菜单，找到ApexSQL菜单，点击ApexSQL Search。
选择 Database object search…选项
在搜索文本框，输入您想搜索的文本（例如：一个变量名）
从数据库下拉列表里，选择要搜索的数据库
在对象下拉列表，选择要搜索的对象类型，或者让它们保持勾选状态
通过选择复选框来决定是否搜索对象、列、索引名、对象内容、系统对象，是否精确匹配和使用的转义字符。
点击Find now 选项

网格将填充包含指定对象的数据库对象
在Database object search窗格双击对象，那么在对象资源管理器里将会高亮那个对象