由于数据库表太多,我知道其中一个表中某1列的值,如何查询出表名 比如:数据库名:test 某表的某列:name=张三 查表名?
利用系统表SysObjects,SysColumns,SysTypes,利用游标,获取数据库里面的每个表,每个字段..然后逐一循环...最终得到表..
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
最后 Exec up_GetTable即可得到所有表.不管哪个字段,只要是存在张三记录的所有表的集合