如何搜索sql server数据库的字符串？ [英] How to search sql server database for string?

问题描述

我知道这是可能的，但不知道如何。

我需要搜索Microsoft SQL数据库以查看所有提及的特定字符串。
例如：我想搜索字符串tblEmployes的所有表，视图，函数，存储过程...。 （不包括表格中的数据）

我需要这个的原因之一，我想删除一些额外的数据表创建，但我恐怕他们是可能在程序或函数的某个地方使用。

任何帮助都是值得赞赏的。

解决方案

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

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/the-ten-most -

  CREATE PROCEDURE FindMyData_String 
 @asked-sql-server-questions-1＃2 ： 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加入Information_Schema.Tables AS T 
 ON C.Table_Name = T.Table_Name 
 AND C.TABLE_SCHEMA = T.TABLE_SCHEMA 
 WHERE Table_Type ='Base Table'
和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 +'''
）
设置@DataExists = 1 
否则
设置@DataExists = 0'
 ELSE'如果存在（选择* 
从ReplaceTableName 
其中Convert（nVarChar（4000），[ReplaceColumnName]）
 Like''％'+ @ DataToFind +'％''
）
设置@DataExists = 1 
其他
设置@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' ColumnN ame）
 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 
   
 
 
运行它只需执行此操作： 
 
 
  exec FindMyData_string'google' ，0 
  
效果非常好!!! 
 
I know it's possible, but don't know how.

I need to search Microsoft SQL database for all mentions of specific string.
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, 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.

Any help is appreciated.
解决方案
this will search every column of every table in a specific database. create the stored proc on the database that you want to search in.

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/the-ten-most-asked-sql-server-questions--1#2:
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
works amazingly well!!!

                        
这篇关于如何搜索sql server数据库的字符串？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！