Question

sql中知道数据库名和其中一个表中的数据如何得到该数据是所在表的名称

只知道一条数据，不知道列名的

Answer 1

CREATE Proc up_GetTable
As
Begin
Declare @Table NVARCHAR(30),@COL NVARCHAR(30),@Str NVARCHAR(2000)
Declare @Tab Table (TabName NVARCHAR(30))
Declare tmpCur CurSor For
Select A.NAME,B.NAME From SysObjects A INNER JOIN SYSCOLUMNS B ON A.ID=B.ID
LEFT JOIN SYSTYPES C ON B.XTYPE=C.XTYPE
WHERE C.NAME IN ('VARCHAR','NVARCHAR','CHAR','TEXT','NCHAR','NCHAR')
AND A.xtype = 'U'
Open tmpCur
Fetch Next From tmpCur Into @Table,@COL
While @@Fetch_Status=0
Begin
SET @Str=' DECLARE @TABLENAME NVARCHAR(30) ;SELECT @TABLENAME='+@COL+' FROM '+@Table +' WHERE '+@COL+'='''+'你的表的列名'''
EXEC(@Str)
IF @@ROWCOUNT>0
INSERT INTO @Tab VALUES(@Table)
Fetch Next From tmpCur Into @Table,@COL
End
Close tmpCur
DealLocate tmpCur
SELECT DISTINCT * FROM @Tab
End

追问

不知道列名，只知道表中的一点数据！能得到表名吗？

追答

Declare
tabName varchar2(30);
colName varchar2(30);
val varchar2(30);
datType varchar2(30);
vStr varchar2(300);
Begin
val := 'DALLAS';
datType := 'VARCHAR2';
for vTab in (select distinct(TABLE_NAME) as tabNa from user_tab_columns)
loop
tabName := vTab.tabNa;
for vCol in (select COLUMN_NAME as colNa from user_tab_columns where TABLE_NAME = tabName and DATA_TYPE =datType)
loop
colName := vCol.colNa;
begin
vStr := 'update '||tabName ||' set '||colName||'='||colName||' where '||colName||'='''||val||'''';
exception
when others then
null;
end;
execute immediate vStr;
if SQL%rowCount > 0 then
DBMS_OUTPUT.put_line(tabName||' '||colName||' '||val);
end if;
end loop;
end loop;
End;

追问

报错了，运行不了

追答

CREATE Proc XXXX
As
这些加上了吧？

Answer 2

这个行哦恐怕