如何查询所有表中有某个值

[fnyygt]

查询一个库中所有表，是不是有某个值
下面这个不行，我试过了
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

[二月十六]

DECLARE @cloumns VARCHAR(40);
DECLARE @tablename VARCHAR(40);
DECLARE @str VARCHAR(40);
DECLARE @counts INT;
DECLARE @sql NVARCHAR(2000);
DECLARE MyCursor CURSOR
FOR
    SELECT  a.name AS Columns ,
            b.name AS TableName
    FROM    syscolumns a ,
            sysobjects b ,
            systypes c
    WHERE   a.id = b.id
            AND b.type = 'U'
            AND a.xtype = c.xtype
            AND c.name LIKE '%char%';
SET @str = '查询的值';
OPEN MyCursor;
FETCH NEXT FROM MyCursor INTO @cloumns, @tablename;
WHILE ( @@Fetch_Status = 0 )
    BEGIN
        SET @sql = 'select  @tmp_counts=count(*) from ' + @tablename
            + ' where ' + @cloumns + ' = ''' + @str + '''';
        EXECUTE sp_executesql @sql, N'@tmp_counts int out', @counts OUT;
        IF @counts > 0
            BEGIN
                PRINT '表名为:' + @tablename + ',字段名为' + @cloumns;
            END;
        FETCH NEXT FROM MyCursor INTO @cloumns, @tablename;
    END;
CLOSE MyCursor;
DEALLOCATE MyCursor;

[道素]

你试试这个，用sp_MsforeachTable 这里没有打印出行标识，因为不确认你的结果是否有这样的数据

DECLARE @string VARCHAR(100)='11'
DECLARE @sql NVARCHAR(MAX)
SET @sql=N'DECLARE @sql NVARCHAR(MAX),@ColNames NVARCHAR(MAX),@ColValues NVARCHAR(MAX);SET @ColNames=NULL;SET @ColValues=NULL;
SELECT  @ColNames=ISNULL(@ColNames+'','','''')+QUOTENAME(c.[Name])
      ,@ColValues=ISNULL(@ColValues, ''''''''+OBJECT_NAME(c.object_id)+''''''''+'' AS [TableName]'')+'',''+ ''RTRIM(CONVERT(NVARCHAR(max),''+QUOTENAME(c.[Name])+'')) AS ''+QUOTENAME(c.[Name]) 
FROM sys.[columns] AS c where c.object_id=object_id(N''?'');
set @sql= ''SELECT * FROM (SELECT ''+ @ColValues+'' FROM ''+''?''+'' ) as t UNPIVOT(ColumnValue FOR ColumName  IN (''+@ColNames+'')) u WHERE CHARINDEX('''''+@string+N''''',ColumnValue)>0'';
print @sql;
EXEC(@sql)'
PRINT @sql
EXEC sp_MsforeachTable  @command1=@sql

TableName	ColumnValue	ColumName
X	Sep 29 2011 12:00AM	Date1
X	Sep 20 2011 12:00AM	Date2
X	Sep  1 2011 12:00AM	Date3
X	Jan  1 2011 12:00AM	Date1
X	Jan  5 2011 12:00AM	Date2