查找某数据库中的列是否存在某个值

查找某数据库中的列是否存在某个值

ifobject_id('spFind_Column_In_DB') isnotnulldropproc spFind_Column_In_DB go--date:2013-5-10 --author:dyq --decs:搜索数据库中是否存在输入的值 --results：tablename,columnamecreateproc spFind_Column_In_DB ( @typeint,--类型：1为文字类型、2为数值类型 @strnvarchar(100)--需要搜索的名字 ) as--创建临时表存放结果 createtable #tbl(PK intidentityprimarykey ,tbl sysname,col sysname) declare@tblnvarchar(300),@col sysname,@sqlnvarchar(1000) if@type=1begindeclare curTable cursor fast_forward forselect'['+SCHEMA_NAME(SCHEMA_ID)+'].['+o.name+']' tableName,'['+c.name+']' columnName from sys.columns c innerjoin sys.objects o on c.object_id=o.object_idwhere o.type_desc='user_table'and user_type_id in (167,175,231,239,35,99) endelsebegindeclare curTable cursor fast_forward forselect'['+SCHEMA_NAME(SCHEMA_ID)+'].['+o.name+']' tableName,'['+c.name+']' columnName from sys.columns c innerjoin sys.objects o on c.object_id=o.object_idwhere o.type_desc='user_table'and user_type_id in (56,48,52,59,60,62,106,108,122) endopen curtable fetchnextfrom curtable into@tbl,@colwhile@@FETCH_STATUS=0beginset@sql='if exists (select * from '+@tbl+' where 'if@type=1beginset@sql+=@col+' like ''%'+@str+'%'')'endelsebeginset@sql+=@col+' in ('+@str+'))'endset@sql+=' INSERT #TBL(tbl,col) VALUES('''+@tbl+''','''+@col+''')'--print @sql exec (@sql) fetchnextfrom curtable into@tbl,@colendclose curtable deallocate curtable select*from #tbl droptable #tbl