如何获取表名 [英] how to get the table name
问题描述
sir / mam
i更新鲜,我有一个问题要问
i在我的数据库中有30个表格假设
表名:District1,District2,District3 ......最多30
列:Distid int,Distname varchar(30),Header varchar(100),News varchar (MAX)
30桌上的
标题是常用栏
我的要求是搜索给定的句子(比如今天CM辞职),它存在于30个表中的一个表格中,而b / b
想要知道它所在的表名吗?
声明 @ 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'。
试试如下
< pre lang =sql> DECLARE @ Sql nVarchar (max)
DECLARE @ Col Varchar (最大)
DECLARE @ ST Varchar (max)
DECLARE @ Table Varchar (MAX)
DECLARE @ Result Varchar (MAX)
SET @Col = 'AccountNumber' Table_Cur INTO @ Table
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
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"
这篇关于如何获取表名的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!