真爱无限的知识驿站

GO  

/****** 对象:  StoredProcedure [dbo].[pro_get_table_construct]    脚本日期: 08/04/2012 15:27:39 ******/  

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[pro_get_table_construct]') AND type in (N'P', N'PC'))  

DROP PROCEDURE [dbo].[pro_get_table_construct]  

SET ANSI_NULLS ON  

GO  

SET QUOTED_IDENTIFIER ON  

GO  

/*****************************************************  

** DECRIPTION: 查询表的结构的存储过程  

** VERSION      AUTH          DATE          Defect No           DESC  

** --------  ------------  ------------  -----------------   ------------------------------  

** V000.0.1    pukuimin     08/03/2012                          修改                        

** --------  ------------  ------------  -----------------   -------------------------------  

*******************************************************/  

create procedure [dbo].[pro_get_table_construct](  

                    @TABLENAME NVARCHAR(200) ---表名  

)  

as  

begin  

SELECT   

表名=case when a.colorder=1 then d.name else '' end,   

表说明=case when a.colorder=1 then isnull(f.value,'') else '' end,   

字段序号=a.colorder,   

字段名=a.name,   

标识=case when COLUMNPROPERTY(a.id,a.name,'IsIdentity')=1 then '1' else '' end,  

主键=case when exists(SELECT 1 FROM sysobjects where xtype='PK' and name in(  

SELECT name FROM sysindexes WHERE indid in(  

SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid  

))) then '1' else '' end,  

类型=b.name,  

占用字节数=a.length,  

长度=COLUMNPROPERTY(a.id,a.name,'PRECISION'),  

小数位数=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),  

允许空=case when a.isnullable=1 then '1' else '' end,  

默认值=isnull(e.text,''),  

字段说明=isnull(g.[value],'')  

FROM syscolumns a  

left join systypes b on a.xtype=b.xusertype   

inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'   

left join syscomments e on a.cdefault=e.id   

left join sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id   

left join sys.extended_properties f on d.id=f.major_id and f.minor_id =0   

where d.name=@TABLENAME         --如果只查询指定表,加上此条件     

order by a.id,a.colorder  

END  

----For mssql2000  

SELECT   

表名=d.name,--case when a.colorder=1 then d.name else '' end,  

字段序号=a.colorder,  

字段名=a.name,  

标识=case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,  

主键=case when exists(SELECT 1 FROM sysobjects where xtype='PK' and name in (  

SELECT name FROM sysindexes WHERE indid in(  

   SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid  

))) then '√' else '' end,  

类型=b.name,  

占用字节数=a.length,  

长度=COLUMNPROPERTY(a.id,a.name,'PRECISION'),  

小数位数=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),  

允许空=case when a.isnullable=1 then '√'else '' end,  

默认值=isnull(e.text,''),  

字段说明=isnull(g.[value],'')  

FROM syscolumns a  

left join systypes b on a.xtype=b.xusertype  

inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'  

left join syscomments e on a.cdefault=e.id  

left join sysproperties g on a.id=g.id and a.colid=g.smallid   

order by a.id,a.colorder  

-- [pro_get_table_construct] 'stuinfo'