Recently, due to the ERP project, we need to know which tables and columns of the background database are written in the Application Operation of the Foreground Data Import function.
Recently, due to the ERP project, we need to know which tables and columns of the background database are written in the Application Operation of the Foreground Data Import function.
For example, if we import a customer's profile, we know that the customer's name is ZhangShan. We want to know that in our business database (eg: NorthWind, in which data tables have fields with this name value set to ZhangShan, we can achieve this through the following SQL. Here, the SQL is searched from the Internet, and some modifications are made here.
1. search data is of the String type
Applicable to search for Text, NText, Varchar, Nvarchar, Char, NChar, AND OTHER TYPES
1. Create a stored procedure: My_Search_StringInGivenTable
The Code is as follows:
USE [NORTHWIND]
GO
/***** Object: StoredProcedure [dbo]. [my_search_stringingingiventable] 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 unregister
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 unpartition ([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 a search stored procedure: My_Search_String_AllTables
This stored procedure traverses all the tables in the specified database and uses the Stored Procedure My_Search_StringInGivenTable created above to obtain the search results for each table.
The Code is 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
Example
The Code is 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 directly create a stored procedure to achieve the desired results, but I personally think the previous method is more flexible.
The Code is 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]
(
@ Searchstrnvarchar (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 IS 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 is not null) AND (@ ColumnName is 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]
2. search data is Int type
Applicable to search for smallint, tinyint, int, bigint, AND OTHER TYPES
1. Create a stored procedure 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 # 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 (13) + '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 the '+ 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)'
+ 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 unregister
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 unregister ([Column Value] for [Column Name] IN ('
+ @ Columns + ') unpvt
WHERE [Column Value] = @ SearchValue'
-- Print @ SQL -- if we get errors, we may 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 (10) + '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 search results for each data table that is traversed.
The Code is 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
Example
The Code is 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 search for only some specified data tables based on the 1st stored procedures in steps 1 and 2 above.
2. Other data types such as Date and Real can be modified with reference to this.
3. This method is time-consuming for large databases, so try to debug it on small databases. When you need to operate on a large database, try to avoid using the database during peak hours and be patient.
