Find out which column of the table contains a field in the sqlserver Database

Welcome to the Windows community forum and interact with 3 million technical staff. Sometimes we want to know which table and which field the value is from the database through a value and search for it online, find a better method and implement it through a stored procedure. You only need to input a value you want to search for to query the table and words of the value.

Welcome to the Windows community forum and interact with 3 million technical staff> sometimes we want to know which table and field the value comes from the database through a value and search for it online, find a better method and implement it through a stored procedure. You only need to input a value you want to search for to query the table and words of the value.

Welcome to the Windows community forum and interact with 3 million technicians>

Sometimes we want to know which table and field the value comes from in the database through a value, and search for it online to find a better method, which is implemented through a stored procedure. You only need to input a value to query the table and field name of the value.

The premise is to store the stored procedure in the queried database.

Create procedure [dbo]. [SP_FindValueInDB]

(

@ Value VARCHAR (1024)

)

AS

BEGIN

-- Set nocount on added to prevent extra result sets from

-- Interfering with SELECT statements.

Set nocount on;

DECLARE @ SQL VARCHAR (1024)

DECLARE @ table VARCHAR (64)

DECLARE @ column VARCHAR (64)

Create table # t (

Tablename VARCHAR (64 ),

Columnname VARCHAR (64)

)

DECLARE TABLES CURSOR

FOR

SELECT o. name, c. name

FROM syscolumns c

Inner join sysobjects o ON c. id = o. id

WHERE o. type = 'U' AND c. xtype IN (167,175,231,239)

Order by o. name, c. name

OPEN TABLES

FETCH NEXT FROM TABLES

INTO @ table, @ column

WHILE @ FETCH_STATUS = 0

BEGIN

SET @ SQL = 'if EXISTS (SELECT NULL FROM ['+ @ table +']'

SET @ SQL = @ SQL + 'where RTRIM (LTRIM (['+ @ column +']) LIKE ''% '+ @ value +' % '')'

SET @ SQL = @ SQL + 'insert INTO # t VALUES (''' + @ table + ''','''

SET @ SQL = @ SQL + @ column + ''')'

EXEC (@ SQL)

FETCH NEXT FROM TABLES

INTO @ table, @ column

END

CLOSE TABLES

DEALLOCATE TABLES

SELECT *

FROM # t

Drop table # t

End