/*------------------------------------------------------------------------------+ #| = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : | #|{>/------------------------------------------------------------------------\<}| #|: | 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=‘动软商城‘
--**********************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