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 entireDatabaseTable 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. x-type 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 exist' (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', the corresponding record is returned when exec sp_findvalueindb 'admin' is input for a new query, tablename displays the table where the queried data is located, and columnname displays the queried data.