sql实现查询某个字段在哪个表里 及结构是什么

declare @dbname2 varchar(100) --数据库名
declare @str varchar(2000)
set @dbname2='aab'

select @str = ' SELECT 
表名=d.name,字段名=a.name,序号=a.column_id, 
标识=is_identity , 
主键=case when exists(SELECT 1 FROM '+@dbname2+ '.sys.sysobjects where xtype= ''PK '' and name in ( 
SELECT name FROM '+' sysindexes WHERE indid in( 
SELECT indid FROM '+ ' sysindexkeys WHERE id = a.object_id AND column_id=a.column_id 
))) then 1 else 0 end, 
类型=b.name, 占用字节数=a.max_length,长度=a.precision,小数位数=a.scale, 允许空=a.is_nullable, 
默认值=isnull(e.text, ''''),字段说明= '''' 
FROM '+@dbname2+ '.sys.columns a 
left join '+@dbname2+ '.sys.systypes b on a.system_type_id=b.xusertype 
inner join '+@dbname2+ '.sys.sysobjects d on a.object_id=d.id and d.xtype= ''U '' and d.name <> ''dtproperties '' 
left join '+' syscomments e on a.default_object_id=e.id 
left join '+ ' sysprotects g on a.object_id=g.id and a.column_id=g.ID 
where a.name like ''%inv%''
order by a.object_id,a.column_id '


exec(@str)

 

转载于:https://www.cnblogs.com/SoftWareIe/p/6797014.html