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

方法1：

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

 方法2：

01	CREATE PROC sp_ValueSearch

02	@value sql_variant,  --要搜索的数据

03	@precision bit=1     --1=仅根据sql_variant中的数据类型查找对应类型的数据列.<>1,查询兼容的所有列,字符数据使用like匹配

04

AS

05	SET NOCOUNT ON

06	IF @value IS NULL RETURN

07

08

--数据类型处理

09	SELECT xtype INTO #t FROM systypes

10	WHERE name=SQL_VARIANT_PROPERTY(@value,N'BaseType')

11

12	--扩展数据类型及查询处理语句

13	DECLARE @sql nvarchar(4000),@sql1 nvarchar(4000)

14	IF @precision=1

15	SET @sql=CASE SQL_VARIANT_PROPERTY(@value,N'BaseType')

16	WHEN N'text' THEN N' LIKE N''%''+CAST(@value as varchar(8000))+''%'''

17	WHEN N'ntext' THEN N' LIKE ''%''+CAST(@value as nvarchar(4000))+''%'''

18	ELSE N'=@value' END

19

ELSE

20

BEGIN

21	SET @sql=CAST(SQL_VARIANT_PROPERTY(@value,N'BaseType') as sysname)

22	IF @sql LIKE N'%char' or @sql LIKE N'%text'

23

BEGIN

24	INSERT #t SELECT xtype FROM systypes

25	WHERE name LIKE N'%char' or name LIKE N'%text'

26	SELECT @sql=N' LIKE N''%''+CAST(@value as '

27

+CASE

28	WHEN LEFT(@sql,1)=N'n' THEN ' nvarchar(4000)'

29	ELSE 'varchar(8000)' END

30	+N')+N''%'''

31

END

32	ELSE IF @sql LIKE N'%datetime'

33

BEGIN

34	INSERT #t SELECT xtype FROM systypes

35	WHERE name LIKE N'%datetime'

36	SET @sql=N'=@value'

37

END

38	ELSE IF @sql LIKE N'%int'

39	OR @sql LIKE N'%money'

40	OR @sql IN(N'real',N'float',N'decimal',N'numeric')

41

BEGIN

42	INSERT #t SELECT xtype FROM systypes

43	WHERE name LIKE N'%int'

44	OR name LIKE N'%money'

45	OR name IN(N'real',N'float',N'decimal')

46	SET @sql=N'=@value'

47

END

48

ELSE

49	SET @sql=N'=@value'

50

END

51	--保存结果的临时表

52	CREATE TABLE #(TableName sysname,FieldName sysname,Type sysname,SQL nvarchar(4000))

53

54	DECLARE tb CURSOR LOCAL

55

FOR

56	SELECT N'SELECT * FROM '

57	+QUOTENAME(USER_NAME(o.uid))

58	+N'.'+QUOTENAME(o.name)

59	+N' WHERE '+QUOTENAME(c.name)

60

+@sql,

61	N'INSERT # VALUES(N'+QUOTENAME(o.name,N'''')

62	+N',N'+QUOTENAME(c.name,N'''')

63	+N',N'+QUOTENAME(QUOTENAME(t.name)+CASE

64	WHEN t.name IN (N'decimal',N'numeric')

65	THEN N'('+CAST(c.prec as varchar)+N','+CAST(c.scale as varchar)+N')'

66	WHEN t.name=N'float'

67	OR t.name like N'%char'

68	OR t.name like N'%binary'

69	THEN N'('+CAST(c.prec as varchar)+N')'

70	ELSE N'' END,N'''')

71	+N',@sql)'

72	FROM sysobjects o,syscolumns c,systypes t,#t tt

73	WHERE o.id=c.id

74	AND c.xusertype=t.xusertype

75	AND t.xtype=tt.xtype

76	AND OBJECTPROPERTY(o.id,N'IsUserTable')=1

77

78

OPEN tb

79	FETCH tb INTO @sql,@sql1

80	WHILE @@FETCH_STATUS=0

81

BEGIN

82	SET @sql1=N'IF EXISTS('+@sql+N') '+@sql1

83	EXEC sp_executesql @sql1,N'@value sql_variant,@sql nvarchar(4000)',@value,@sql

84	FETCH tb INTO @sql,@sql1

85

END

86

CLOSE tb

87	DEALLOCATE tb

88	SELECT * FROM #