以下代码提供查询数据库中是否存在某个值

----找到用户表的所有字段
--  SELECT  name,object_name(id) from dbo.syscolumns 
--  where OBJECTPROPERTY(id, 'IsTable') =1 and OBJECTPROPERTY(id, 'IsSystemTable') =0
-- select name,id,xtype from dbo.sysobjects where xtype='U'
/*
常用数据类型
('uniqueidentifier','tinyint','smallint','int','smalldatetime','real'
,'money','datetime','float','bit','decimal','numeric','smallmoney','bigint',
'varbinary','varchar','binary','char','nchar','nvarchar')
常用数据类型ID
(36,48,52,56,58,59,60,61,62,104,106,108,122,127,165,167,173,175,239,231)
特殊类型
(34,35,99,189,231,98)
('image','text','ntext','timestamp','sysname','sql_variant')
*/

/*
----------------------------------------------------------------------------------------------
以下代码提供查询数据库中是否存在某个值
其中：字段类型为以下中的一个('uniqueidentifier','tinyint','smallint','int','smalldatetime','real'
,'money','datetime','float','bit','decimal','numeric','smallmoney','bigint',
'varbinary','varchar','binary','char','nchar','nvarchar')
由于cast方法接受最长（nvarchar(128))，所以对某些长字段会出问题.
----------------------------------------------------------------------------------------------
*/

declare @value nvarchar(250)
declare @columnName nvarchar(255)
declare @tableName nvarchar(255)
declare @sql nvarchar(4000)
declare @findCount int
declare @printMessage nvarchar(255)

declare myCursor cursor for
SELECT  name,object_name(id) from dbo.syscolumns 
where OBJECTPROPERTY(id, 'IsTable') =1 and OBJECTPROPERTY(id, 'IsSystemTable') =0
         and xtype in (36,48,52,56,58,59,60,61,62,104,106,108,122,127,165,167,173,175,239,231)
set @value='值'  --´  --待查询的值（转换为nvarchar类型）
open myCursor
fetch next from myCursor into @columnName, @tableName

while @@fetch_status=0
begin
    set @findCount=0
set @sql=' select @Count = count(*) from '+@tableName
+' where charindex('''+@value+''',cast('+@columnName+' as nvarchar(125)))>0'
    exec sp_executesql @sql, N'@Count int output', @findCount output 
    set @printMessage ='表名'+@tableName+'-列名º'+@columnName
    if (@findCount >0)
begin
          set @sql =Replace(@sql,' @Count = count(*) ',' * ')
          print @printMessage
          exec sp_executesql @sql
end
    set @findCount =0
fetch next from myCursor into @columnName, @tableName
end
close myCursor
deallocate myCursor