sql查询数据库中有某个值的所有表

在数据库中查找含有某个值的所有表的表名，使用如下的存储过程可以做到这一点。

CREATE PROCEDURE dbo.FindString
    @string NVARCHAR(100)
AS

DECLARE @SQL NVARCHAR(4000);
    SET @SQL = N'
            DECLARE @str NVARCHAR(4000);
            SELECT
                @str = ISNULL(@str + N'' OR '' + c.name + N'' LIKE N''''%'
                                                + @string + ' %'''''',
                               c.name + N'' LIKE N''''%' + @string +'%'''''') FROM syscolumns AS c JOIN systypes AS t ON c.id=OBJECT_ID(''?'')
                AND c.xtype=t.xtype
                AND t.name IN(''varchar'',''char'',''nvarchar'',''nchar'');

            SET @str = ''SELECT TOP 1 1 FROM ? WHERE ''+@str;
            CREATE TABLE #tb(a int);
            INSERT #tb(a) EXEC(@str);
            IF EXISTS(SELECT * FROM #tb)
                PRINT ''?''
        ';
    EXEC sp_MsforeachTable @SQL;
GO

转载于:https://www.cnblogs.com/kevinGao/archive/2013/03/01/2939720.html