根据已知字符串遍历整个数据库查找它所在的表和列位置以及出现的次数 .

转载 http://blog.csdn.net/beirut/article/details/8244492

 

用来分析别人设计的数据库，非常好用！！！

 

/*------------------------------------------------------------------------------+
#| = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = :  |
#|{>/------------------------------------------------------------------------\<}|
#|: | Author     :    小爱                                                      |
#|: | Description:    根据字符串遍历整个数据库查找它所在的列以及出现的次数      |
#|: | SQL Version:    适用于 SQL 2012, SQL 2008 R2, SQL 2008                    |
#|: | Copyright  :    免费使用和共享      /^(o.o)^\                             |
#|: | Create Date:    2012-11-30                                                |
#|: | Revision     :  Version: 1.1                                              |
#|{>\------------------------------------------------------------------------/<}|
#| = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = :  |
#+-----------------------------------------------------------------------------*/
/*
追加描述：
1、改脚本回去遍历每个数据库的每个架构下面的所有表的列
2、在消息选项卡里面会列出表和列以及查询语句
3、结果存储在临时表#Results。您可以查询这个表，在同一会话中进一步细化的结果，或删除GROUP BY查看详细的数据。

警告：
*因为这个脚本要遍历数据库中所有的表和列，它可能需要很长的时间来处理。
*您最初的测试可以运行在一个单一的表或列，看看你的系统负载情况。

*/
DECLARE @MaxRows INT ;
DECLARE @MinRows INT ;
DECLARE @FilterSchema NVARCHAR(255) ;
DECLARE @FilterTable NVARCHAR(255) ;
DECLARE @FilterColumn NVARCHAR(255) ;
DECLARE @Characters NVARCHAR(MAX) ;

--  过滤表的最小和最大的行数，用此来限定目标表的范围
-- a. MinRows = 0 表示搜索所有的表, 1 表示搜索包含有数据的表.
-- b. MaxRows = null 表示搜索所有的表, > 0 表示搜索的表中的总行数小于这个值.
SET @MaxRows=1000 ;
SET @MinRows=1 ;

--  下面三个变量分别用来配置架构、表和列的；这些值都使用 Like %Name% 模糊查询 
-- Names are compared using Like %Name%
SET @FilterSchema=NULL ;
SET @FilterTable=NULL ;
SET @Filtercolumn=NULL ;

--  要搜索的文本值
SET @Characters='XXXXXXXX'

--**********************Script begins****************************
--***************************************************************
SET NOCOUNT ON
DECLARE @SchemaT TABLE
        (
         RowID INT IDENTITY(1 , 1) ,
         SchemaName NVARCHAR(MAX) ,
         TableName NVARCHAR(MAX) ,
         ColumnName NVARCHAR(MAX)
        )
IF OBJECT_ID('tempdb..#Results') IS NOT NULL
   BEGIN
         DROP TABLE #Results
   END
CREATE TABLE #Results
       (
        RowID INT IDENTITY(1 , 1) ,
        RSchemaName NVARCHAR(MAX) DEFAULT '' ,
        RTableName NVARCHAR(MAX) DEFAULT '' ,
        RColumnName NVARCHAR(MAX) DEFAULT '' ,
        Value NTEXT DEFAULT ''
       )

DECLARE @LoopNo INT ,
        @TotalRows INT ,
        @Schema NVARCHAR(MAX) ,
        @Table NVARCHAR(MAX) ,
        @Column NVARCHAR(MAX) ,
        @SQL NVARCHAR(MAX) ,
        @ParamDef NVARCHAR(MAX) ,
        @DataExists BIT
DECLARE @ReturnValue NVARCHAR(MAX)
DECLARE @ParmDefinition NVARCHAR(MAX)

--****************************************************************************************************
-- 查找满足条件的所有信息到表变量@SchemaT里面，其中包括架构、表名、列名和表的大小
--****************************************************************************************************
INSERT  INTO @SchemaT (SchemaName , TableName , ColumnName)
        SELECT  Sch = t.Sch , Tbl = REPLACE(REPLACE(t.Tbl , '[' , '[[') , ']' , ']]') , Col = c.name
        FROM    (
                 SELECT s.Name AS Sch , t.name AS Tbl , t.object_id , SUM(p.rows) AS NumCount
                 FROM   sys.schemas s
                 LEFT JOIN sys.tables t
                 ON     s.schema_id = t.schema_id
                 LEFT JOIN sys.partitions p
                 ON     t.object_id = p.object_id
                 LEFT JOIN sys.allocation_units a
                 ON     p.partition_id = a.container_id
                 WHERE  p.index_id IN (0 , 1) -- 0 heap table , 1 table with clustered index
                        AND p.rows IS NOT NULL
                        AND a.type = 1  -- row-data only , not LOB
                        AND (
                             s.name LIKE '%'+@FilterSchema+'%'
                             OR @FilterSchema IS NULL
                            )
                        AND (
                             t.name LIKE '%'+@FilterTable+'%'
                             OR @FilterTable IS NULL
                            )
                 GROUP BY s.Name , t.name , t.object_id
                 HAVING (
                         SUM(p.rows) >= @MinRows
                         AND (
                              SUM(p.rows) <= @MaxRows
                              OR @MaxRows IS NULL
                             )
                        )
                ) T
        INNER JOIN sys.columns C
        ON      T.object_id = c.object_id
        INNER JOIN sys.types P
        ON      C.system_type_id = p.system_type_id
        WHERE   (
                 p.name LIKE '%char%'
                 OR p.name LIKE '%text%'
                )
                AND (
                     c.name LIKE '%'+@FilterColumn+'%'
                     OR @FilterColumn IS NULL
                    )
        ORDER BY Sch , Tbl , Col
--**********************************************************************************
-- 拼接动态语句，并执行把结果插入到临时表 #Results 里面
--**********************************************************************************
SELECT  @LoopNo=1 , @TotalRows=MAX(RowID)
FROM    @SchemaT
PRINT '总计出现次数 = '+CAST(@TotalRows AS NCHAR(5))+CHAR(13)
WHILE @LoopNo <= @TotalRows
      BEGIN
            SELECT  @Schema=SchemaName , @Table=TableName , @Column=ColumnName
            FROM    @SchemaT
            WHERE   RowID = @LoopNo 
            SET @SQL='SELECT '+QUOTENAME(@Column)+' FROM '+QUOTENAME(@Schema)+'.'+QUOTENAME(@Table)+' WHERE CHARINDEX('''+@Characters+''','+QUOTENAME(@Column)+') > 0'
--**********************************************************************************
-- 如果包含有指定的字符串，就输出查询语句和出现的此处以及表信息
--**********************************************************************************
   IF @TotalRows >0
   BEGIN
    PRINT '出现位置: '+QUOTENAME(@Schema)+'.'+QUOTENAME(@Table)+'.'+QUOTENAME(@Column)
    PRINT '出现次数: '+CAST(@LoopNo AS NCHAR(5))
    PRINT '查询语句: '+@SQL+CHAR(13)
            END
--**********************************************************************************
 
            SET @ParmDefinition='@ReturnValueOUT NVARCHAR(MAX) OUTPUT'
            INSERT  INTO #Results (Value)
                    EXECUTE sp_executesql
                        @SQL ,
                        @ParmDefinition ,
                        @ReturnValueOUT=@ReturnValue OUTPUT
            UPDATE  #Results
            SET     RTableName=@Table , RColumnName=@Column , RSchemaName=@Schema
            WHERE   RTableName = ''
            SET @LoopNo=@LoopNo+1 
      END

--**********************************************************************************
--显示结果
--**********************************************************************************

SELECT  COUNT(*) AS Occurrences , RSchemaName , RTableName , RColumnName
FROM    #Results
GROUP BY RSchemaName , RTableName , RColumnName