For example: We import a customer's information, we know that the customer's name is Zhangshan, we want to know, in our business database (Eg:northwind), which of the data tables which fields set the name value Zhangshan, through the following SQL, This is what we can do, where the SQL search has been locally modified from the Web.
One, the search data is a string type
Applicable to search Text,ntext,varchar,nvarchar,char,nchar and other types
1. Create the stored procedure: my_search_stringingiventable
Copy Code code as follows:
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 JOIN 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 [tabl E 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. Create search stored procedures: my_search_string_alltables
This stored procedure traverses all the tables of the specified database and uses the stored procedure my_search_stringingiventable created above to get the results of each table.
Copy Code code as follows:
Use [NORTHWIND]
Go
/****** object:storedprocedure [dbo]. [My_search_string_alltables] Script date:09/25/2011 15:41:58 ******/
SET ansi_nulls off
Go
SET QUOTED_IDENTIFIER OFF
Go
CREATE PROC [dbo]. [My_search_string_alltables]
(
@SearchString NVARCHAR (MAX)
)
As
BEGIN
CREATE TABLE #RESULT ([PK column] NVARCHAR (max), [column VALUE] NVARCHAR (max), [column Name] sysname,
[table SCHEMA] sysname, [table Name] sysname)
DECLARE @Table_Name sysname, @Table_Schema sysname
DECLARE curalltables CURSOR local forward_only STATIC read_only
For
SELECT TABLE_SCHEMA, table_name
From INFORMATION_SCHEMA. Tables
WHERE table_type = ' BASE TABLE '
ORDER BY TABLE_SCHEMA, table_name
OPEN Curalltables
FETCH Curalltables
Into @Table_Schema, @Table_Name
while (@ @FETCH_STATUS = 0)--loop through all tables in the database
BEGIN
INSERT #RESULT
EXECUTE my_search_stringingiventable @SearchString, @Table_Schema, @Table_Name
FETCH Curalltables
Into @Table_Schema, @Table_Name
End-While
Close Curalltables
Deallocate curalltables
--Return results
SELECT * from #RESULT ORDER BY [Table Name]
End
Using the sample
Copy Code code as follows:
Use [NORTHWIND]
Go
DECLARE @return_value int
EXEC @return_value = [dbo]. [My_search_string_alltables]
@SearchString = N ' Wantvalue '
SELECT ' return Value ' = @return_value
Go
Another version is to create a stored procedure directly to achieve the desired result, but the individual feels that the previous approach is more flexible
Copy Code code as follows:
Use [NORTHWIND]
Go
/****** object:storedprocedure [dbo]. [Zl_searchalltables] Script date:09/25/2011 15:44:10 ******/
SET ansi_nulls off
Go
SET QUOTED_IDENTIFIER OFF
Go
CREATE PROC [dbo]. [Zl_searchalltables]
(
@SearchStr nvarchar (100)
)
As
BEGIN
CREATE TABLE #Results (ColumnName nvarchar (370), Columnvalue nvarchar (3630))
SET NOCOUNT on
DECLARE @TableName nvarchar (256), @ColumnName nvarchar (128), @SearchStr2 nvarchar (110)
SET @TableName = '
SET @SearchStr2 = quotename ('% ' + @SearchStr + '% ', ')
While @TableName are not NULL
BEGIN
SET @ColumnName = '
SET @TableName =
(
SELECT MIN (QuoteName (table_schema) + '. ' + QUOTENAME (table_name))
From INFORMATION_SCHEMA. TABLES
WHERE table_type = ' BASE TABLE '
and QuoteName (Table_schema) + '. ' + QUOTENAME (table_name) > @TableName
and OBJECTPROPERTY (
OBJECT_ID (
QuoteName (Table_schema) + '. ' + QUOTENAME (table_name)
), ' ismsshipped '
) = 0
)
While (@TableName are NOT null) and (@ColumnName are NOT null)
BEGIN
SET @ColumnName =
(
SELECT MIN (QuoteName (column_name))
From INFORMATION_SCHEMA. COLUMNS
WHERE Table_schema = ParseName (@TableName, 2)
and table_name = ParseName (@TableName, 1)
and Data_type in (' char ', ' varchar ', ' nchar ', ' nvarchar ')
and QuoteName (column_name) > @ColumnName
)
IF @ColumnName is not NULL
BEGIN
INSERT into #Results
Exec
(
' SELECT ' + @TableName + '. ' + @ColumnName + ', left (' + @ColumnName + ', 3630)
From ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' like ' + @SearchStr2
)
End
End
End
SELECT ColumnName, Columnvalue from #Results
End
[Code]
Second, the search data is int type
Apply to search for smallint, tinyint, int, bigint and other types
1. Create Stored Procedures My_search_intingiventable
[Code]
Use [NORTHWIND]
Go
/****** object:storedprocedure [dbo]. [My_search_intingiventable] Script date:09/25/2011 15:45:46 ******/
SET ANSI_NULLS on
Go
SET QUOTED_IDENTIFIER ON
Go
CREATE PROCEDURE [dbo]. [My_search_intingiventable]
(@SearchValue INT,
@Table_Schema sysname,
@Table_Name sysname)
As
BEGIN
DECLARE @Columns NVARCHAR (MAX),
@Cols NVARCHAR (MAX),
@PkColumn NVARCHAR (MAX),
@SQL NVARCHAR (MAX)
--judge and create a #result table
IF object_id (' TempDB.. #Result ', ' U ') is not NULL
DROP TABLE #Result
CREATE TABLE #RESULT
(
[PK COLUMN] NVARCHAR (MAX),
[COLUMN VALUE] BIGINT,
[COLUMN Name] SYSNAME,
[TABLE SCHEMA] SYSNAME,
[TABLE Name] SYSNAME
)
--Start searching for a given table
DECLARE curalltables CURSOR local forward_only STATIC read_only
For
SELECT Table_schema,
table_name
From INFORMATION_SCHEMA. Tables
WHERE table_name = @Table_Name
OPEN Curalltables
While 1 = 1
BEGIN
FETCH Curalltables
Into @Table_Schema, @Table_Name
IF @ @FETCH_STATUS <> 0--Loop through all tables in the database
Break
PRINT CHAR + ' processing ' + QuoteName (@Table_Schema) + '. '
+ QuoteName (@Table_Name)
--Get all int columns
SET @Columns = STUFF (SELECT ', ' + QuoteName (column_name)
From INFORMATION_SCHEMA. COLUMNS
WHERE data_type like '%int '
and table_name = @Table_Name
and Table_schema = @Table_Schema
ORDER BY column_name
For
XML PATH (')
), 1, 2, "')
IF @Columns is NULL
BEGIN
PRINT ' No int columns in ' + QuoteName (@Table_Schema)
+ '. ' + QuoteName (@Table_Name)
CONTINUE
End
--Get columns for select Statement-we need to convert all columns to bigint
SET @Cols = STUFF (SELECT, cast (' + QuoteName (column_name)
+ ' as bigint ' as '
+ QuoteName (column_name)
From INFORMATION_SCHEMA. COLUMNS
WHERE data_type like '%int '
and table_name = @Table_Name
ORDER BY column_name
For
XML PATH (')
), 1, 2, "')
--Create PK column (s)
SET @PkColumn = STUFF (SELECT N ' + ' | ' + ' + ') cast ('
+ QuoteName (CU. COLUMN_NAME)
+ ' as nvarchar (max) '
From INFORMATION_SCHEMA. Table_constraints TC
INNER JOIN 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
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_Schema) + '. ' + QuoteName (@Table_Name)
+ ') src unpivot ([column Value] for [column Name] in ('
+ @Columns + ') unpvt
WHERE [Column Value] = @SearchValue '
--print @SQL--if we get errors, we'll want to print generated SQL
INSERT #RESULT
([PK COLUMN],
[COLUMN VALUE],
[COLUMN Name],
[TABLE SCHEMA],
[TABLE Name]
)
EXECUTE sp_executesql @SQL, N ' @SearchValue int ', @SearchValue
PRINT ' Found ' + CAST (@ @ROWCOUNT as VARCHAR) + ' records in '
+ QuoteName (@Table_Schema) + '. ' + QuoteName (@Table_Name)
End
Close Curalltables
Deallocate curalltables
SELECT *
From #RESULT
Order BY [TABLE SCHEMA],
[TABLE Name]
End
2, create a search stored procedure my_search_int_alltables, similar to the above, this stored procedure will call my_search_intingiventable to implement the search results for each of the data tables traversed
Copy Code code as follows:
Use [NORTHWIND]
Go
/****** object:storedprocedure [dbo]. [My_search_int_alltables] Script date:09/25/2011 15:48:29 ******/
SET ansi_nulls off
Go
SET QUOTED_IDENTIFIER OFF
Go
CREATE PROC [dbo]. [My_search_int_alltables]
(
@SearchValue INT
)
As
BEGIN
CREATE TABLE #RESULT ([PK column] NVARCHAR (max), [column VALUE] NVARCHAR (max), [column Name] sysname,
[table SCHEMA] sysname, [table Name] sysname)
DECLARE @Table_Name sysname, @Table_Schema sysname
DECLARE curalltables CURSOR local forward_only STATIC read_only
For
SELECT TABLE_SCHEMA, table_name
From INFORMATION_SCHEMA. Tables
WHERE table_type = ' BASE TABLE '
ORDER BY TABLE_SCHEMA, table_name
OPEN Curalltables
FETCH Curalltables
Into @Table_Schema, @Table_Name
while (@ @FETCH_STATUS = 0)--loop through all tables in the database
BEGIN
INSERT #RESULT
EXECUTE my_search_stringingiventable @SearchValue, @Table_Schema, @Table_Name
FETCH Curalltables
Into @Table_Schema, @Table_Name
End-While
Close Curalltables
Deallocate curalltables
--Return results
SELECT * from #RESULT ORDER BY [Table Name]
End
Using the sample
Copy Code code as follows:
Use [NORTHWIND]
Go
DECLARE @return_value int
EXEC @return_value = [dbo]. [My_search_int_alltables]
@SearchValue = 68
SELECT ' return Value ' = @return_value
Go
Note:
1, you can be based on the 1th stored procedure in the above one or two to achieve only the function of specifying certain data tables.
2, for other data types such as: date,real, etc. can be modified for reference.
3, this method for large databases will be time-consuming, so try to debug on a small database. When you need to operate on a large database, try to avoid the database using peak hours and be patient.
