Question

如何实现：sql查询某数据库中所有表的任意字段中包含“张三”文字的记录。

说下目的：弄到一个Web系统，然后我把其数据库复制过来，想快速了解下数据库中的情况。因为表很多，然后里面存储有用户信息，现在我想快速查一下有哪些表中存储有和“张三”相关的信息。

Answer 1

DECLARE @COLNAMELIKE NVARCHAR(100)
DECLARE @OBJNAME NVARCHAR(100)
DECLARE @COLNAME NVARCHAR(100)
DECLARE @VALUE NVARCHAR(100)
DECLARE @SQL NVARCHAR(MAX)
SET @COLNAMELIKE='%POLICYNUMBER%'
SET @VALUE='MSH-CCIC-GEP-Plan1-13530'
DECLARE CUR CURSOR FOR
select object_name(id) objName,Name as colName from syscolumns
where (name like @COLNAMELIKE)
and id in(select id from sysobjects where xtype='u')
order by objname
OPEN CUR
FETCH NEXT FROM CUR INTO @OBJNAME,@COLNAME
WHILE @@fetch_status = 0
BEGIN
SET @SQL=' SELECT * FROM + ' @OBJNAME + ' WHERE ' + @COLNAME + '=''' + @VALUE + ''' '
exec @SQL
FETCH NEXT FROM CUR INTO @OBJNAME,@COLNAME
END
CLOSE CUR
DEALLOCATE CUR