SQLSERVER查询一条数据在哪张表里的哪个字段出现过_sql 查询列表判断某个字段的数据是否出现过

转载一下当做个笔记
转载的原文

创建这个存储过程

CREATE PROCEDURE [dbo].[SP_FindValueInDB]

(

    @value VARCHAR(1024)

) 

AS

BEGIN

    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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73

exec SP_FindValueInDB ’数据的值’

https://download.csdn.net/download/arno1988/7369479