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

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

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 JOINsys.schemas s ON o.schema_id=s.schema_id  JOIN sys.systypes types ON types.xusertype=c.xusertypeWHERE 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