導(dǎo)讀:1建站知識(shí)最近因ERP項(xiàng)目，我們需要知道前臺(tái)數(shù)據(jù)導(dǎo)入功能Application操作的導(dǎo)入字段都寫入到了后臺(tái)數(shù)據(jù)庫(kù)哪些表的哪些列網(wǎng)站建設(shè)網(wǎng)seo優(yōu)化趨勢(shì)。

比如：我們導(dǎo)入了某個(gè)客戶的資料，我們知道此客戶的姓名是ZhangShan，我們想知道，在我們的業(yè)務(wù)數(shù)據(jù)庫(kù)(eg:NorthWind)中，有哪些數(shù)據(jù)表的哪些字段設(shè)置了此姓名值ZhangShan,通過(guò)下面的SQL，我們就可以實(shí)現(xiàn)此目的，此處的SQL搜索自網(wǎng)上，在此處做了局部修改。 一、搜索數(shù)據(jù)是String類型 適用于搜索Text,NText,Varchar,Nvarchar,Char,NChar等類型 1、創(chuàng)建存儲(chǔ)過(guò)程:My_Search_StringInGivenTable

復(fù)制代碼 代碼如下:

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網(wǎng)站優(yōu)化培訓(xùn)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、創(chuàng)建搜索存儲(chǔ)過(guò)程:My_Search_String_AllTables 此存儲(chǔ)過(guò)程將遍歷指定數(shù)據(jù)庫(kù)的所有表，并利用上面創(chuàng)建的存儲(chǔ)過(guò)程My_Search_StringInGivenTable來(lái)取得每個(gè)表的搜索結(jié)果。

關(guān)鍵詞標(biāo)簽: 搜索 標(biāo)簽 數(shù)據(jù)庫(kù)