查询表 所有列 基本信息 [包括表 列说明]

说明:如果表有说明,则第一行为表说明行,否则没有表说明行

--:exp=查询表<说明>及<包括无列说明的列>列相关:主键约束说明初值
--@tb<表名
ALTER PROCEDURE [dbo].[P_SltTbColsBasInf4]
	@tb nchar(32)
AS
BEGIN
declare @tmp table(odr int,col nchar(32),typ int,nam char(32),len int,nul bit,scl int,prc int,inc int,vlu sql_variant,exp sql_variant,unq int,ID bit);
insert into @tmp(odr,exp)(select p.minor_id, p.value from sys.extended_properties p where p.minor_id=0 and p.major_id=OBJECT_ID(@tb));
insert into @tmp select c.colorder as odr, c.name as col,c.xtype as typ,t.name as nam,c.length as len,c.isnullable as nul,c.scale as scl,c.prec as prc,c.colstat as inc,
v.text as vlu,p.value as exp,k.colid as unq,i.is_primary_key as ID from syscolumns c
full join sys.extended_properties p on c.id=p.major_id AND c.colid = p.minor_id
left join systypes t on c.xtype=t.xusertype 
left join syscomments v on c.cdefault=v.id 
left join sysindexkeys k on c.id=k.id and c.colid=k.colid
left join sys.index_columns ic on ic.object_id=c.id and ic.column_id=c.colid
left join sys.indexes i on i.object_id=c.id and i.index_id=ic.index_id 
where c.id=object_id(@tb) order by odr

select * from @tmp;

END

 

交流 QQ : 2412366909@qq.com

手机号码:177-7499-4428

注:本人使用过程中暂未发现造成存取不正确的问题

*以上代码集合于网上查找的相关 SQL 资料