sql server 2016 查询表结构

查询表数据结构#

select	[表名]		 = CASE WHEN c.column_id = 1 then SCHEMA_NAME(t.schema_id)+ '.'+ t.name ELSE '' END,
		[表创建时间] = CASE WHEN c.column_id = 1 then CONVERT(varchar,t.create_date,111)    ELSE '' END,
		[表修改时间] = CASE WHEN c.column_id = 1 then CONVERT(varchar,t.modify_date,111)    ELSE '' END,
		[表说明]	 = CASE WHEN c.column_id = 1 then isnull(tdesc.value,'')			    ELSE '' END,
		[总行数]	 = CASE WHEN c.column_id = 1 then ISNULL((select top(1) CONVERT(varchar,idx.rows) 
			from sysindexes idx where idx.id = t.object_id AND idx.indid < 2 order by idx.indid desc),'')ELSE '' END,
		[字段序号]	 = c.column_id,
		[字段名]	 = c.name,
		[自增标识]	 = CASE WHEN c.is_identity  = 1 then '√'								ELSE '' END,
		[主键]	     = case when exists(
							select 1 from sys.indexes idx join sys.index_columns idxC on idx.object_id = idxC.object_id and idx.index_id = idxC.index_id
							where idx.object_id = t.object_id and idxC.column_id = c.column_id and idx.is_primary_key = 1
					   )then '√' else '' end,

		[类型]		 = tp.name,
		[占用字节数] = c.max_length,
		[长度]       = COLUMNPROPERTY(t.object_id, c.name,'PRECISION'),
		[小数位数]   = isnull(COLUMNPROPERTY(t.object_id, c.name,'Scale'),0),
		[可为空]     = CASE WHEN c.is_nullable  = 1 then '√'								ELSE '' END,
		[计算列]     = CASE WHEN c.is_computed  = 1 then '√'								ELSE '' END,
		[默认值]	 = dc.definition,	--dc.name[默认值约束]
		[字段说明]   = isnull(cdesc.[value],''),
		[TrimTrailingBlanks] = case ColumnProperty(c.object_id, c.name, 'UsesAnsiTrim')
										when 1 then '╳'
										when 0 then '√'
										else '(n/a)' end,
		[FixedLenNullInSource]	= case
								when type_name(tp.system_type_id) not in ('varbinary','varchar','binary','char')
									then '(n/a)'
								when c.is_nullable = 0 then '╳' else '√' end,
		[Collation]	 = c.collation_name
from sys.tables t
inner join sysobjects o on t.object_id = o.id and o.xtype = 'U' and o.name <> 'dtproperties'
left  join sys.extended_properties tdesc on t.object_id = tdesc.major_id and tdesc.minor_id = 0
inner join sys.columns c on t.object_id = c.object_id
inner join sys.types tp on c.user_type_id = tp.user_type_id
left  join sys.default_constraints dc on c.default_object_id = dc.object_id
left  join sys.extended_properties cdesc on t.object_id = cdesc.major_id and c.column_id = cdesc.minor_id
where t.name = 'FJ_ReelBack'
order by t.object_id, c.column_id

查询视图数据结构#

select   
	[视图名称]	= CASE WHEN c.column_id = 1 then user_name(ObjectProperty(ao.object_id, 'ownerid'))+'.'+ao.name ELSE '' END,
	[创建时间]	= CASE WHEN c.column_id = 1 then CONVERT(varchar,ao.create_date,111) ELSE '' END,
	[修改时间]	= CASE WHEN c.column_id = 1 then CONVERT(varchar,ao.modify_date,111) ELSE '' END,
	[view备注]  = CASE WHEN c.column_id = 1 then isnull(vdesc.[value],'') ELSE '' END,
	[view类型]	= CASE WHEN c.column_id = 1 then ao.type_desc ELSE '' END,
	[字段序号]  = c.column_id,
	[字段名]	= c.name,
	[类型]		= type_name(c.user_type_id),
	[计算标识]	= case when ColumnProperty(c.object_id, c.name, 'IsComputed') = 0 then '' else '√' end,
	[长度]		= convert(int, c.max_length),
	[Prec]		= case when charindex(type_name(system_type_id) + ',', N'tinyint,smallint,decimal,int,bigint,real,money,float,numeric,smallmoney,date,time,datetime2,datetimeoffset,') > 0
					   then convert(char(5),ColumnProperty(c.object_id, c.name, 'precision'))
					   else '' end,
	[Scale]		= case when charindex(type_name(system_type_id) + ',', N'tinyint,smallint,decimal,int,bigint,real,money,float,numeric,smallmoney,date,time,datetime2,datetimeoffset,') > 0
						then convert(char(5),OdbcScale(system_type_id,scale))
						else '     ' end,
	[可为空]	=  case when is_nullable = 0 then '' else '√' end,
	[TrimTrailingBlanks] = case ColumnProperty(c.object_id, c.name, 'UsesAnsiTrim')
											when 1 then '╳'
											when 0 then '√'
											else '(n/a)' end,
	[FixedLenNullInSource]	= case
							when type_name(system_type_id) not in ('varbinary','varchar','binary','char')
								then '(n/a)'
							when is_nullable = 0 then '╳' else '√' end,
	[Collation]	 = collation_name,
	[字段说明]   = isnull(cdesc.[value],'')
from sys.all_objects ao
join sys.all_columns c on ao.object_id = c.object_id
left  join sys.extended_properties vdesc on ao.object_id = vdesc.major_id and vdesc.minor_id = 0
left  join sys.extended_properties cdesc on c.object_id = cdesc.major_id and c.column_id = cdesc.minor_id
where ao.object_id = OBJECT_ID('view_WH_Matmain') --AND vdesc.name = 'MS_DiagramPane1'
ORDER BY C.column_id

查询存储过程参数#

select   
[Schema]		= CASE WHEN po.parameter_id = 1 then user_name(ObjectProperty(ao.object_id, 'ownerid')) ELSE '' end,
[PROCName]		= CASE WHEN po.parameter_id = 1 then ao.name ELSE '' end,
--[类型]			=  ao.type_desc,
[创建时间]		= CASE WHEN po.parameter_id = 1 then CONVERT(varchar,ao.create_date,111) ELSE '' end,
[参数名称]		= po.name,
[参数类型]		= type_name(user_type_id),
[Length]		= po.max_length,
[Prec]			=  case when type_name(po.system_type_id) = 'uniqueidentifier' then precision
						else OdbcPrec(po.system_type_id, po.max_length, po.precision) end,
[Scale]			= OdbcScale(po.system_type_id, po.scale),
[Param_order]	= po.parameter_id,
[Collation]		= convert(sysname, case when po.system_type_id in (35, 99, 167, 175, 231, 239)
						then ServerProperty('collation') end)
from sys.all_objects ao
left join sys.all_parameters po on ao.object_id = po.object_id
where ao.object_id = OBJECT_ID('FJ_ApiData_add')

其他方案#

sp_help 'FJ_ReelBack'
sp_help 'view_WH_Matmain'