声明  @ Table  表（Id  int   identity ，名称 varchar （ 100 ））; 

 insert   @ Table （Name）
（选择 t.TABLE_NAME 来自 INFORMATION_SCHEMA.TABLES t 
 join  INFORMATION_SCHEMA.COLUMNS c   c.TABLE_NAME = t.TABLE_NAME 
其中 c。 COLUMN_NAME = ' 标题'）; 

声明  @ Index   int  = 0; 
 while （ 1  = 1）
开始 
 set   @ Index  + =  1 跨度>; 
声明  @ TableName   varchar  （ 100 ）=（选择名称来自  @ Table  其中 id =  @ Index ） ; 

如果  @ TableName  是  null  
 break ; 
 exec （'  select * from' +  @ TableName  + ' 其中Header =' 'CM今天辞职'''）; 
 end  

创建一个表变量，它加载表名称中包含标题列的名称。迭代表变量并执行动态sql，其中Header列包含'CM resigns today'。

试试如下



DECLARE @ Sql nVarchar （max）
DECLARE @ Col Varchar （最大）
DECLARE @ ST Varchar （max）
DECLARE @ Table Varchar （MAX）
DECLARE @ Result Varchar （MAX）

SET @Col = ' AccountNumber'
SET @ ST = ' AUSTRALI0001'

SELECT TABLE_SCHEMA + ' 。' + TABLE_NAME as TableName INTO #Temp FROM INFORMATION_SCHEMA.COLUMNS 其中 COLUMN_NAME = @ Col

DECLARE Table_Cur cursor FOR
SELECT TableName FROM #Temp

OPEN Table_Cur
FETCH NEXT FROM n> Table_Cur INTO @ Table

WHILE @@ FETCH_STATUS = 0
BEGIN
SET @ Sql = ' 选择' + @ Col + ' 从' + @ Table + ' 其中' + @ Col + ' =''' + @ ST + ' '''
exec sp_executesql @ Sql ，N ' @ x int out'， @ Result out

END

CLOSE Table_Cur
DEALLOCATE Table_Cur

Drop TABLE #Temp

嗨!!!!

使用此查询

  SELECT 标题
 FROM  sys.Tables 其中 Header =   CM今天辞职 

sir/mam
i am a fresher and i have a question to ask that is
i have 30 tables in my database suppose

Table Name : District1,District2,District3......upto 30
Columns : Distid int,Distname varchar(30),Header varchar(100),News varchar(MAX)


in 30 tables Header is common column
my requirement is to search a given sentence (say "CM resigns today") which is present in one of the 30 table and

want to know the table name where it is present?

declare  @Table table(Id int identity, Name varchar(100));

insert @Table(Name)
	(select t.TABLE_NAME from INFORMATION_SCHEMA.TABLES t
	join INFORMATION_SCHEMA.COLUMNS c on c.TABLE_NAME = t.TABLE_NAME
	where c.COLUMN_NAME = 'Header');

declare @Index int =0;
while(1 =1 )
begin
	set @Index += 1;
	declare @TableName varchar(100) = (select Name from @Table where id = @Index);

	if @TableName is null
		break;
	exec('select * from ' + @TableName + ' where Header = ''CM resigns today''');
end

create a table variable, it load table name which have column 'Header'. Iterate the table variable and execute dynamic sql where Header column contain 'CM resigns today'.

Try this as below

DECLARE @Sql nVarchar(max)
DECLARE @Col Varchar(max)
DECLARE @ST Varchar(max)
DECLARE @Table Varchar(MAX)
DECLARE @Result Varchar(MAX)

SET @Col = 'AccountNumber'
SET @ST = 'AUSTRALI0001'

SELECT TABLE_SCHEMA+'.'+TABLE_NAME as TableName INTO #Temp FROM INFORMATION_SCHEMA.COLUMNS Where COLUMN_NAME = @Col

DECLARE Table_Cur cursor FOR
SELECT TableName FROM #Temp

OPEN Table_Cur
FETCH NEXT FROM Table_Cur INTO @Table

WHILE @@FETCH_STATUS = 0
BEGIN
SET @Sql = 'Select '+@Col+' From '+@Table+' Where '+@Col+' = '''+@ST+''''
exec sp_executesql @Sql, N'@x int out', @Result out

END

CLOSE Table_Cur
DEALLOCATE Table_Cur

Drop TABLE #Temp

Hi!!!!
use this query

SELECT Header 
FROM sys.Tables where Header="CM resigns today"