sql里知道字段与字段值如何查找哪些表中有

[qq_34691105]

SELECT *
FROM sysobjects s
WHERE
TYPE='u'
AND
id IN(SELECT id
FROM syscolumns s
WHERE s.name='待查找字段名')

这能查出哪些表中有这个字段
我想根据字段值查出哪些表比如
vendorcode='010200001'
在哪些表中有怎么查？

[无涯大者]

这个只能取到所有表 进行循环遍历了!

[a7747369]

EXEC dbo.proc_GetTables 'EmployeeID','1'

CREATE  PROCEDURE  proc_GetTables(@columnName sysname,@value NVARCHAR(1000))
AS
BEGIN
	DECLARE @T TABLE(TableName sysname);
	DECLARE @Sql NVARCHAR(4000),@Result INT,@CurrentTableName sysname,@CurrentColumnName sysname;
	DECLARE @t_cursor CURSOR
	SET @t_cursor= CURSOR FOR 
	SELECT utable.name tableName,col.name columnName FROM SYS.objects utable 
	INNER JOIN sys.columns col ON col.object_id=utable.object_id
	WHERE utable.type='u' AND col.name=@columnName; 
	
	--打开游标
	BEGIN TRY
	OPEN @t_cursor;
	FETCH NEXT FROM @t_cursor INTO @CurrentTableName,@CurrentColumnName;
	WHILE(@@FETCH_STATUS=0)
		BEGIN
			
			SET @Result=0;
			SET @Sql=N'SELECT @c=COUNT(1) WHERE EXISTS(SELECT * FROM '+QUOTENAME(@CurrentTableName)+' WHERE '+QUOTENAME(@CurrentColumnName)+'=@v)';
		
			EXEC sp_executesql @Sql,N'@c int output,@v nvarchar(1000)',@c=@Result OUTPUT,@v=@value;
			
			IF(@Result>0)
				BEGIN
					INSERT INTO @T( TableName )
					VALUES  (@CurrentTableName)
				END
			FETCH NEXT FROM @t_cursor INTO @CurrentTableName,@CurrentColumnName;
		END
	--关闭游标	
	CLOSE @t_cursor;
	DEALLOCATE @t_cursor;
	--查询数据
	SELECT * FROM @T;
	END TRY
	BEGIN CATCH
		
		SELECT ERROR_MESSAGE() errorMsg,ERROR_LINE() errorLine
	END CATCH
END

[唐诗三百首]

用游标, 逐表用动态SQL查where vendorcode='010200001'.

[道素]

试试使用系统

Undocumene

的存储过程：

EXEC sp_MSForEachTable 'if col_length(''?'',''vendorcode'') is not null EXEC(''SELECT ''''?'''' as TableName,vendorcode  FROM ? where vendorcode=''''010200001'''''')'
---功能逐个表执行下面语句，？会被换成当前表，你乐意将下面的EXEC再加入insert into将结果输出到临时表
--col_length('?','vendorcode') is not null 判断该表是否有vendor code这个字段，如果有动态执行那个查询
 if col_length('?','vendorcode') is not null EXEC('SELECT ''?'' as TableName,vendorcode FROM ? where vendorcode=''010200001''')