CREATE PROCEDURE [ dbo ] . [ SP_FindValueInDB ]
(
@value VARCHAR ( 1024 )
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
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
FORSELECT 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.nameOPEN TABLES
FETCH NEXT FROM TABLES
INTO @table , @columnWHILE @@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
ENDCLOSE TABLES
DEALLOCATE TABLESSELECT *
FROM #tDROP TABLE #t
End
例如，要查询值'BBQ CHIC SW'，结果如下：
返回三条记录，说明这个值存在于三个表中，分别为_dts_menudef, g_dts_menudef和g_recipe中，字段名分别为name1, name1, name。
非常好用。