View the table where the field in the database is located or the table and field where a value is located

View the table and field of the field or value in the database ************************* * the table in which the field is located, selecttab. nametable_name, col. namecolumn_namefromsysobjectstableftjoinsyscolumnscolontab.idcol.idandtab.xtypeUwherecol

View the table and field of the field or value in the database ************************* * select tab of the table in which the field is located. name table_name, col. name column_name from sysobjects tab left join syscolumns col on tab. id = col. id and tab. xtype = U where col

View the table where the field in the database is located or the table and field where a value is located

*

Select tab. name table_name, col. name column_name

From sysobjects tab

Left join syscolumns col on tab. id = col. id and tab. xtype = 'U'

Where col. name like '% fkfz0000003 %' order by 1, 2

************************************* Query the entire database table and field where a specific value is located

By performing a stored procedure, you only need to input a value you want to search for 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

For example, to query 'admin', create a query Input

EXEC SP_FindValueInDB 'admin'

Corresponding records are returned. Tablename displays the table where the queried data is located, and Columnname displays the queried data.