查询整个数据库中某个特定值所在的表和字段的方法

CREATE PROCEDURE [dbo].[SP_FindValueInDB]
(
@valueVARCHAR(1024)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE@sqlVARCHAR(1024)
DECLARE@tableVARCHAR(64)
DECLARE@columnVARCHAR(64)

CREATETABLE #t (
tablename VARCHAR(64),
columnname VARCHAR(64)
)

DECLARE TABLES CURSOR
FOR

SELECT o.name, c.name
FROM syscolumns c
INNERJOIN sysobjects o ON c.id = o.id
WHERE o.type ='U'ANDc.xtype IN (167, 175, 231, 239)
ORDERBY o.name, c.name

OPEN TABLES

FETCHNEXTFROMTABLES
INTO@table, @column

WHILE@@FETCH_STATUS=0
BEGIN
SET@sql='IFEXISTS(SELECT NULL FROM ['+@table+'] '
SET@sql=@sql+'WHERERTRIM(LTRIM(['+@column+']))LIKE ''%'+@value+'%'') '
SET@sql=@sql+'INSERTINTO #t VALUES ('''+@table+''', '''
SET@sql=@sql+@column+''')'

EXEC(@sql)

FETCHNEXTFROMTABLES
INTO@table, @column
END

CLOSE TABLES
DEALLOCATE TABLES

SELECT*
FROM #t

DROPTABLE #t


End

 

 

 

 

 

 

 

 

EXEC [SP_FindValueInDB] ‘’