以下代碼提供查詢資料庫中是否存在某個值

----找到使用者表的所有欄位
--  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