--视图显示的字段对应表
SELECT * FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE WHERE VIEW_NAME = 'ViewCity'
--根据表名字段名查询当前字段相关信息
SELECT * FROM
(
SELECT SysColumns.name AS ColumnName,SysTypes.name as ColumnType,SysColumns.scale AS Scale,
SysColumns.is_nullable AS IsEmpty,SysColumns.is_identity AS IsIdentity,
CASE WHEN EXISTS
(
SELECT 1 FROM sys.indexes SysIndexes
JOIN sys.index_columns SysIndexColumns on (SysIndexes.OBJECT_ID = SysIndexColumns.OBJECT_ID)
WHERE SysIndexes.OBJECT_ID = SysColumns.OBJECT_ID
AND SysIndexColumns.index_column_id = SysColumns.column_id
AND SysIndexes.is_primary_key = 1
) THEN 1 ELSE 0 END AS IsPrimary,
ISNULL(SysExtendedProperties.[value], '') Annotation,
CASE WHEN SysColumns.name ='Memo' THEN SysColumns.column_id + 1000 ELSE SysColumns.column_id END OrderColumn,
SysColumns.max_length MaxLength,SysColumns.precision Precision
FROM sys.columns SysColumns
LEFT JOIN sys.types SysTypes on (SysColumns.system_type_id = SysTypes.system_type_id)
LEFT JOIN sys.extended_properties SysExtendedProperties ON SysColumns.object_id = SysExtendedProperties.major_id AND SysExtendedProperties.minor_id = SysColumns.column_id
WHERE SysColumns.OBJECT_ID = (SELECT OBJECT_ID FROM sys.tables WHERE name = 'UT_City')
AND SysTypes.name <> 'sysname'
) list WHERE ColumnName = 'CityCode' ORDER BY OrderColumn ASC
根据视图名称查询当前视图关联的所有表的所有字段
最新推荐文章于 2024-04-18 09:24:04 发布