Sqlserver根据字段查找表和根据字段值查找表的几个方法

-------------Sqlserver根据字段名称查找表(多个字段值)，例如查找字段名包含 ibatch和cbatch 的表
SELECT *FROM(
SELECT t2.name 
FROM syscolumns t1,sysobjects t2
WHERE t1.id=t2.id
and t1.NAME='ibatch'
) m
LEFT JOIN
(
SELECT t2.name 
FROM syscolumns t1,sysobjects t2
WHERE t1.id=t2.id
and t1.NAME='cbatch'
) n   ON m.name=n.name

-- 在数据库执行，Sqlserver根据字段值查找那些表包含这个值

-- 判断要创建的存储过程名是否存在 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_FindValueInDB]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) 
-- 删除存储过程 
drop procedure [dbo].[SP_FindValueInDB] 
GO 
CREATE PROCEDURE [dbo].[SP_FindValueInDB]
(
    @value VARCHAR(1024)
) 
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @sql VARCHAR(1024) 
    DECLARE @table VARCHAR(64) 
    DECLARE @column VARCHAR(64) 
    CREATE TABLE #t ( 
        tablename VARCHAR(64), 
        columnname VARCHAR(64) 
    ) 
    DECLARE TABLES CURSOR FOR 
    SELECT o.name, c.name FROM syscolumns c 
    INNER JOIN sysobjects o ON c.id = o.id 
    WHERE o.type = 'U' AND c.xtype IN (167, 175, 231, 239) 
    ORDER BY o.name, c.name 
    OPEN TABLES 
        FETCH NEXT FROM TABLES 
        INTO @table, @column 
        WHILE @@FETCH_STATUS = 0 
        BEGIN 
            SET @sql = 'IF EXISTS(SELECT NULL FROM [' + @table + '] ' 
            SET @sql = @sql + 'WHERE RTRIM(LTRIM([' + @column + '])) LIKE ''%' + @value + '%'') ' 
            SET @sql = @sql + 'INSERT INTO #t VALUES (''' + @table + ''', ''' 
            SET @sql = @sql + @column + ''')' 
            EXEC(@sql) 
            FETCH NEXT FROM TABLES 
            INTO @table, @column 
        END 
    CLOSE TABLES 
    DEALLOCATE TABLES 
    SELECT * FROM #t 
    DROP TABLE #t 
End

--  exec [SP_FindValueInDB]  '010201'    ---执行存储过程,传入要找的值 010201

--  另一个根据字段值查找表的方法

----- 根据字段值查找表
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Full_Search]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) 
-- 删除存储过程 
drop procedure [dbo].[Full_Search] 
GO
CREATE proc Full_Search(@string varchar(50)) 
as 
begin 


declare @tbname varchar(50) 
declare tbroy cursor for select name from sysobjects 
where xtype= 'u ' --第一个游标遍历所有的表 


open tbroy 
fetch next from tbroy into @tbname 
while @@fetch_status=0 
begin 


declare @colname varchar(50) 
declare colroy cursor for select name from syscolumns 
where id=object_id(@tbname) and xtype in ( 
select xtype from systypes 
where name in ( 'varchar ', 'nvarchar ', 'char ', 'nchar ') --数据类型为字符型的字段 
) --第二个游标是第一个游标的嵌套游标，遍历某个表的所有字段 


open colroy 
fetch next from colroy into @colname 
while @@fetch_status=0 
begin 


declare @sql nvarchar(1000),@j int 
select @sql= 'select @i=count(1) from ' +@tbname + ' where '+ @colname+ ' like '+ '''%'+@string+ '%''' 
exec sp_executesql @sql,N'@i int output',@i=@j output --输出满足条件表的记录数 
if @j> 0 
BEGIN
select 包含字串的表名=@tbname
--exec( 'select distinct '+@colname+' from ' +@tbname + ' where '+ @colname+ ' like '+ '''%'+@string+ '%''') 
END
fetch next from colroy into @colname 
end 


close colroy 
deallocate colroy 


fetch next from tbroy into @tbname 
end 
close tbroy 
deallocate tbroy 
end 
go


--  exec Full_Search '19344'    ---执行存储过程,传入要找的值 010201