<legend id='xdxn0'><style id='xdxn0'><dir id='xdxn0'><q id='xdxn0'></q></dir></style></legend>

    <tfoot id='xdxn0'></tfoot>

    <small id='xdxn0'></small><noframes id='xdxn0'>

      <bdo id='xdxn0'></bdo><ul id='xdxn0'></ul>

        <i id='xdxn0'><tr id='xdxn0'><dt id='xdxn0'><q id='xdxn0'><span id='xdxn0'><b id='xdxn0'><form id='xdxn0'><ins id='xdxn0'></ins><ul id='xdxn0'></ul><sub id='xdxn0'></sub></form><legend id='xdxn0'></legend><bdo id='xdxn0'><pre id='xdxn0'><center id='xdxn0'></center></pre></bdo></b><th id='xdxn0'></th></span></q></dt></tr></i><div id='xdxn0'><tfoot id='xdxn0'></tfoot><dl id='xdxn0'><fieldset id='xdxn0'></fieldset></dl></div>
      1. 如何在 SQL Server 数据库中搜索字符串?

        时间:2023-09-18
        <i id='VXu2I'><tr id='VXu2I'><dt id='VXu2I'><q id='VXu2I'><span id='VXu2I'><b id='VXu2I'><form id='VXu2I'><ins id='VXu2I'></ins><ul id='VXu2I'></ul><sub id='VXu2I'></sub></form><legend id='VXu2I'></legend><bdo id='VXu2I'><pre id='VXu2I'><center id='VXu2I'></center></pre></bdo></b><th id='VXu2I'></th></span></q></dt></tr></i><div id='VXu2I'><tfoot id='VXu2I'></tfoot><dl id='VXu2I'><fieldset id='VXu2I'></fieldset></dl></div>
        <legend id='VXu2I'><style id='VXu2I'><dir id='VXu2I'><q id='VXu2I'></q></dir></style></legend>

        <tfoot id='VXu2I'></tfoot>
            • <small id='VXu2I'></small><noframes id='VXu2I'>

                  <tbody id='VXu2I'></tbody>
                  <bdo id='VXu2I'></bdo><ul id='VXu2I'></ul>
                  本文介绍了如何在 SQL Server 数据库中搜索字符串?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

                  问题描述

                  我知道这是可能的,但我不知道怎么做.

                  I know it's possible, but I don't know how.

                  我需要在 SQL Server 数据库中搜索所有提及特定字符串的内容.

                  I need to search an SQL Server database for all mentions of a specific string.

                  例如:我想搜索所有表、视图、函数、存储过程...以查找字符串tblEmployes"(不是表中的数据).

                  For example: I would like to search all tables, views, functions, stored procedures, ... for string "tblEmployes" (not data within the tables).

                  我需要这个的原因之一是我想删除一些创建的额外数据表,但我担心它们可能在过程或函数的某处使用.

                  One of the reasons I need this is I would like to remove some extra data tables that are created, but I am afraid that they are maybe used somewhere in procedures or functions.

                  推荐答案

                  这将搜索特定数据库中每个表的每一列.在要搜索的数据库上创建存储过程.

                  This will search every column of every table in a specific database. Create the stored procedure on the database that you want to search in.

                  十个最常见的 SQL Server 问题及其答案:

                  CREATE PROCEDURE FindMyData_String
                      @DataToFind NVARCHAR(4000),
                      @ExactMatch BIT = 0
                  AS
                  SET NOCOUNT ON
                  
                  DECLARE @Temp TABLE(RowId INT IDENTITY(1,1), SchemaName sysname, TableName sysname, ColumnName SysName, DataType VARCHAR(100), DataFound BIT)
                  
                      INSERT  INTO @Temp(TableName,SchemaName, ColumnName, DataType)
                      SELECT  C.Table_Name,C.TABLE_SCHEMA, C.Column_Name, C.Data_Type
                      FROM    Information_Schema.Columns AS C
                              INNER Join Information_Schema.Tables AS T
                                  ON C.Table_Name = T.Table_Name
                          AND C.TABLE_SCHEMA = T.TABLE_SCHEMA
                      WHERE   Table_Type = 'Base Table'
                              And Data_Type In ('ntext','text','nvarchar','nchar','varchar','char')
                  
                  
                  DECLARE @i INT
                  DECLARE @MAX INT
                  DECLARE @TableName sysname
                  DECLARE @ColumnName sysname
                  DECLARE @SchemaName sysname
                  DECLARE @SQL NVARCHAR(4000)
                  DECLARE @PARAMETERS NVARCHAR(4000)
                  DECLARE @DataExists BIT
                  DECLARE @SQLTemplate NVARCHAR(4000)
                  
                  SELECT  @SQLTemplate = CASE WHEN @ExactMatch = 1
                                              THEN 'If Exists(Select *
                                                            From   ReplaceTableName
                                                            Where  Convert(nVarChar(4000), [ReplaceColumnName])
                                                                         = ''' + @DataToFind + '''
                                                            )
                                                       Set @DataExists = 1
                                                   Else
                                                       Set @DataExists = 0'
                                              ELSE 'If Exists(Select *
                                                            From   ReplaceTableName
                                                            Where  Convert(nVarChar(4000), [ReplaceColumnName])
                                                                         Like ''%' + @DataToFind + '%''
                                                            )
                                                       Set @DataExists = 1
                                                   Else
                                                       Set @DataExists = 0'
                                              END,
                          @PARAMETERS = '@DataExists Bit OUTPUT',
                          @i = 1
                  
                  SELECT @i = 1, @MAX = MAX(RowId)
                  FROM   @Temp
                  
                  WHILE @i <= @MAX
                      BEGIN
                          SELECT  @SQL = REPLACE(REPLACE(@SQLTemplate, 'ReplaceTableName', QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName)), 'ReplaceColumnName', ColumnName)
                          FROM    @Temp
                          WHERE   RowId = @i
                  
                  
                          PRINT @SQL
                          EXEC SP_EXECUTESQL @SQL, @PARAMETERS, @DataExists = @DataExists OUTPUT
                  
                          IF @DataExists =1
                              UPDATE @Temp SET DataFound = 1 WHERE RowId = @i
                  
                          SET @i = @i + 1
                      END
                  
                  SELECT  SchemaName,TableName, ColumnName
                  FROM    @Temp
                  WHERE   DataFound = 1
                  GO
                  

                  要运行它,只需执行以下操作:

                  To run it, just do this:

                  exec FindMyData_string 'google', 0
                  

                  效果非常好!!!

                  这篇关于如何在 SQL Server 数据库中搜索字符串?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

                  上一篇:sql server 函数中的 newid() 下一篇:如何将自定义属性添加到 SQL 连接字符串?

                  相关文章

                  <i id='9tUsl'><tr id='9tUsl'><dt id='9tUsl'><q id='9tUsl'><span id='9tUsl'><b id='9tUsl'><form id='9tUsl'><ins id='9tUsl'></ins><ul id='9tUsl'></ul><sub id='9tUsl'></sub></form><legend id='9tUsl'></legend><bdo id='9tUsl'><pre id='9tUsl'><center id='9tUsl'></center></pre></bdo></b><th id='9tUsl'></th></span></q></dt></tr></i><div id='9tUsl'><tfoot id='9tUsl'></tfoot><dl id='9tUsl'><fieldset id='9tUsl'></fieldset></dl></div>

                    <tfoot id='9tUsl'></tfoot>
                    <legend id='9tUsl'><style id='9tUsl'><dir id='9tUsl'><q id='9tUsl'></q></dir></style></legend>

                    <small id='9tUsl'></small><noframes id='9tUsl'>

                      <bdo id='9tUsl'></bdo><ul id='9tUsl'></ul>