导读:1建站知识最近因ERP项目，我们需要知道前台数据导入功能Application操作的导入字段都写入到了后台数据库哪些表的哪些列网站建设网seo优化趋势。

比如：我们导入了某个客户的资料，我们知道此客户的姓名是ZhangShan，我们想知道，在我们的业务数据库(eg:NorthWind)中，有哪些数据表的哪些字段设置了此姓名值ZhangShan,通过下面的SQL，我们就可以实现此目的，此处的SQL搜索自网上，在此处做了局部修改。 一、搜索数据是String类型 适用于搜索Text,NText,Varchar,Nvarchar,Char,NChar等类型 1、创建存储过程:My_Search_StringInGivenTable

复制代码 代码如下:

USE [NORTHWIND] GO /****** Object: StoredProcedure [dbo].[My_Search_StringInGivenTable] Script Date: 09/25/2011 15:37:14 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[My_Search_StringInGivenTable] (@SearchString NVARCHAR(MAX), @Table_Schema sysname, @Table_Name sysname) AS BEGIN DECLARE @Columns NVARCHAR(MAX), @Cols NVARCHAR(MAX), @PkColumn NVARCHAR(MAX) -- Get all character columns SET @Columns = STUFF((SELECT ', ' + QUOTENAME(Column_Name) FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE IN ('text','ntext','varchar','nvarchar','char','nchar') AND TABLE_NAME = @Table_Name ORDER BY COLUMN_NAME FOR XML PATH('')),1,2,'') IF @Columns IS NULL -- no character columns RETURN -1 -- Get columns for select statement - we need to convert all columns to nvarchar(max) SET @Cols = STUFF((SELECT ', cast(' + QUOTENAME(Column_Name) + ' as nvarchar(max)) as ' + QUOTENAME(Column_Name) FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE IN ('text','ntext','varchar','nvarchar','char','nchar') AND TABLE_NAME = @Table_Name ORDER BY COLUMN_NAME FOR XML PATH('')),1,2,'') SET @PkColumn = STUFF((SELECT N' + ''|'' + ' + ' cast(' + QUOTENAME(CU.COLUMN_NAME) + ' as nvarchar(max))' FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC INNER JOseo网站优化培训IN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CU ON TC.TABLE_NAME = CU.TABLE_NAME AND TC.TABLE_SCHEMA = CU.TABLE_SCHEMA AND Tc.CONSTRAINT_NAME = CU.CONSTRAINT_NAME WHERE TC.CONSTRAINT_TYPE ='PRIMARY KEY' AND TC.TABLE_SCHEMA = @Table_Schema AND TC.TABLE_NAME = @Table_Name ORDER BY CU.COLUMN_NAME FOR XML PATH('')),1,9,'') IF @PkColumn IS NULL SELECT @PkColumn = 'cast(NULL as nvarchar(max))' -- set select statement using dynamic UNPIVOT DECLARE @SQL NVARCHAR(MAX) SET @SQL = 'select *, ' + QUOTENAME(@Table_Schema,'''') + 'as [Table Schema], ' + QUOTENAME(@Table_Name,'''') + ' as [Table Name]' + ' from (select '+ @PkColumn + ' as [PK Column], ' + @Cols + ' from ' + QUOTENAME(@Table_Name) + ' )src UNPIVOT ([Column Value] for [Column Name] IN (' + @Columns + ')) unpvt WHERE [Column Value] LIKE ''%'' + @SearchString + ''%''' --print @SQL EXECUTE sp_ExecuteSQL @SQL, N'@SearchString nvarchar(max)', @SearchString END

2、创建搜索存储过程:My_Search_String_AllTables 此存储过程将遍历指定数据库的所有表，并利用上面创建的存储过程My_Search_StringInGivenTable来取得每个表的搜索结果。

关键词标签: 搜索 标签 数据库