根据一个值查找表,根据表名查询出表的基础属性

--查询出一个表的基本信息

SELECT colum.NAME 列名,types.NAME 类型,colum.isnullable 是否可以为null,colum.length 长度 
FROM sys.syscolumns colum,sys.systypes types 
WHERE colum.xusertype=types.xusertype and colum.id=OBJECT_ID('表名')

--根据值查找到表
DECLARE @sqlVal nvarchar(800)  
SET @sqlVal=N'值'

DECLARE MyTable CURSOR LOCAL FOR 
	SELECT 'if exists (select 1 from ['+s.name+'].['+o.name+'] where ['+c.name+'] = '''+@sqlVal+''')
	print ''值所在的表: ['+o.name+']  值所在的字段:['+c.name+'] 字段的类型:['+types.name+']'''
	FROM sys.syscolumns c JOIN sys.objects o ON c.id=o.object_id JOIN
	sys.schemas s ON o.schema_id=s.schema_id  JOIN sys.systypes types ON types.xusertype=c.xusertype
	WHERE s.name!='sys' AND o.type='u' AND c.status>=0 AND c.xusertype IN(167,175, 239, 231)

OPEN MyTable FETCH NEXT FROM MyTable INTO @sqlVal 
 WHILE @@fetch_status=0
 begin EXEC (@sqlVal) 
 FETCH NEXT FROM MyTable INTO @sqlVal 
 END
 CLOSE MyTable
 DEALLOCATE MyTable