posts - 41,  comments - 40,  trackbacks - 0

-- 我知道一个值“White”,想知道它在哪个表里的哪个字段


-- 使用Northwind测试
USE Northwind
GO

DECLARE @what varchar(800)
SET @what='White'  --要搜索的字符串

DECLARE @sql varchar(8000)

DECLARE TableCursor CURSOR LOCAL FOR
SELECT sql='IF EXISTS ( SELECT 1 FROM ['+o.name+'] WHERE ['+c.name+'] LIKE
''%'+@what+'%'' ) PRINT ''所在的表及字段:['+o.name+'].['+c.name+']'''
FROM syscolumns c JOIN sysobjects o ON c.id=o.id
WHERE o.xtype='U' AND c.status>=0 AND c.xusertype IN (175, 239, 231, 167 )

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @sql
WHILE @@FETCH_STATUS=0
  BEGIN
    EXEC( @sql )
    FETCH NEXT FROM TableCursor INTO @sql
  END

CLOSE TableCursor

-- 删除游标引用
DEALLOCATE TableCursor

 

-- 测试结果
所在的表及字段:[Orders].[ShipName]
所在的表及字段:[Customers].[CompanyName]

 

posted on 2007-08-24 05:21 NeedJava 阅读(1002) 评论(1)  编辑  收藏 所属分类: SQL

只有注册用户登录后才能发表评论。


网站导航: